Naveen's Weblog

Bridge to future

Posts Tagged ‘MSSQL’

Find row count of all tables in given database

Posted by codingsense on February 4, 2010

Hi,
Last week I wanted to know which tables in my database are having records and it was quite big database and would take a lot of time opening all tables and check. So I planned to write a query and find out. And here is what I wrote.

Select rows, OBJECT_NAME(id)
from sysindexes where id in
(select Object_ID(TABLE_NAME) from INFORMATION_SCHEMA.TABLES)
and indid < 2 and rows > 0

And this fetched me the exact result i wanted. There are lot of metadata tables in MSSQL which can be handy.
For more information on metadata information in MSSQL click here

Happy Learning
Codingsense 🙂

Posted in MSSQL | Tagged: , | Leave a Comment »

MSSQL Output in XML using For XML

Posted by codingsense on December 31, 2008


As we know nowadays XML is extensively used for any transfer between media so we should know how to get output of any user data in XML form. In MSSQL 2005 life has been made easier with For XML Keyword. In this section we will see how to get different types of XML output in MSSQL.

Open Management studio, and as i explain each keyword, execute the queries and check the output for better understanding.

First let us create 2 tables which will be refferred in the below section

Employee Table :

CREATE TABLE [dbo].[Employee](
	[ID] [int] NOT NULL Primary Key,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[DOB] [datetime] NULL)
GO

EmployeeSalary Table:

CREATE TABLE [dbo].[EmpSalary](
	[ID] int NULL,	
	[SalariedMonth] varchar(4) NULL
)

Insert Data in the tables:

Insert into Employee select 1,'First1','Last1','08/04/1983'
Insert into EmployeeSalary select 1,'Apr'
Insert into EmployeeSalary select 1,'May'
Insert into EmployeeSalary select 1,'Jun'

Insert into Employee select 2,'First2','Last2','08/04/1984'
Insert into EmployeeSalary select 2,'Apr'
Insert into EmployeeSalary select 2,'Jun'

Insert into Employee select 3,'First3','Last3',null
Insert into EmployeeSalary select 3,'May'
Insert into EmployeeSalary select 3,'Jun'

Raw: – Gives as the element name for each row

select FirstName,LastName from Employee for xml raw

Auto: – Gives Table Name as the element name for each Row

select FirstName,LastName from Employee for xml AUTO

Elements:
Without Elements keyword a row with all the columns will be converted in one XML node element.
With Elements Keyword a row will be converted into Parent Node and each column will be converted into a child node.

select FirstName,LastName from Employee for xml AUTO , elements
XSINIL: While selecting columns if the column value is null then it will not be displayed in the XML. If we want to display the column even it has Null Values, then we have to use XSINIL. By using this, if the column has values then the respective value will be displayed or xsi:nil="true" will be displayed for the NULL values.
select FirstName,LastName, DOB from Employee for xml path, ELEMENTS XSINIL

'@':
@ keyword has to be used in 'As Clause' of the column if we want the Column to be considered as an Tag attribute and display in the parent tag.

select ID as '@ID',FirstName,LastName from Employee for xml path, ELEMENTS 

Root:
Root creates a root node with the name specified in the parameter
For eg:
root('Client') create .... on both ends of the XML generated

select * from Employee for xml path, ELEMENTS XSINIL, root('Client')

Joining 2 Tables:
Now if you want both the tables to be joined and the output has to be shown in a single XML file then there are many methods i will show you the two output which i have come accross.
Consider that for each employee we need to show the months in which he has earned the salary, then within the Employee node we will insert one more salary paid node and display the months.

1) Here a salary Tag will be displayed under each Employee tag and will contain the list of months in separate child nodes in which the employee has been paid the salary.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'Month' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

2) Here we use data() method to merge all the child entries into one row in the salary tag. This will give only one element named 'salary' under employee tag.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'data()' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

As you see in above example there is no much difference in both the methods except the 'AS clause' in subquery, but the output is pretty different.

Data():-
If for each parent record if there exists multiple children then we can use data() to display the parent and all children together in a single tag.
The 2nd example in previous section can be used to know about the Data() method.

There are lot many things to know, with the above things you can just begin smoothly.

More Information:
http://technet.microsoft.com/en-us/library/ms345137(SQL.90).aspx#forxml2k5_topic8

Happy Learning 🙂

Posted in MSSQL | Tagged: , | Leave a Comment »

MDF and LDF Files

Posted by codingsense on December 30, 2008

MDF file and LDF file will be created automatically by MSSQL server when we create a new database in MSSQL. The files will be located at Installed Path -> MSSQL.1 -> MSSQL -> Data.
Whenever you create a Database ‘X’ in MSSQL, in the data folder 2 files will be created namely, X.mdf and X_log.ldf.

Difference between MDF and LDF file

MDF file is the Primary database file used by MSSQL in which user data will be present.

For each MDF file there will be a LDF file associated, this is the Transaction log file used by MSSQL to store the user transactions. This file will be used by MSSQL to recover the Primary Database file if it is corrupted.

Is there a need of LDF file?

Some of the users remove the LDF file since it grows very heavily after any transaction in MDF file. But it’s always suggested that to keep the LDF file with MDF file so that in recovery time the user will not repent. For the size problem you can use Shrink Database and Files to decrease the file size.

Happy Learning 🙂

Posted in MSSQL | Tagged: , | Leave a Comment »

Delete duplicate entry from table

Posted by codingsense on December 26, 2008

Hi friends,

While working on a project i found that my tables were loading with duplicate entries and i wanted to remove the duplicate entries. I googled for the solution but found none. I think there is no option given in MSSQL for this issue. So i made my own logic to remove the duplicate entries and it worked fine. I have even tested this with 3 million records and it take just few seconds.

I create a procedure for this issue just have a look.

Create PROCEDURE RemoveDuplicateDomain
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--Create Temp Table for holding the duplicate values
	Create table #TempTable ( DomainName varchar(200));

	--Insert all duplicate values from into the temptable
	insert into #TempTable(DomainName) select EmailID from EmailA Group by EmailID having count(emailid) > 1;

	--Delete the selected duplicate entry from the main table
	Delete from EmailA where EmailID in (select DomainName from #TempTable);

	--Insert back the single entry into the main table from temp table
	Insert into EmailA(emailid) Select DomainName from #TempTable;

	--Drop the temp table
	drop table #TempTable ;
END
GO

If you know any better idea please post a comment

Happy Learning 🙂

Posted in MSSQL | Tagged: | Leave a Comment »

Search metadata

Posted by codingsense on December 23, 2008


Many times we may want to see the information of our MSSQL objects. Here are some list of searches i have come across and found useful to share with you all.

  • SYSOBJECTS:

    The below query gives the list of keys associated with a table

    
    select name, xtype from sysobjects where parent_obj = object_id(N'[dbo].TableName')

    You can further filter it out by adding where clause to get only Primary Key( Xtype = ‘PK’) and foreign key (Xtype = ‘F’) and so on

    XType List :
    C: Check constraint
    D: Default constraint
    F: Foreign Key constraint
    L: Log
    P: Stored procedure
    PK: Primary Key constraint
    RF: Replication Filter stored procedure
    S: System table
    TR: Trigger
    U: User table
    UQ: Unique constraint
    V: View
    X: Extended stored procedure

  • INFORMATION_SCHEMA:

    To List the Tables in the database with thier properites use

    
    select * from INFORMATION_SCHEMA.TABLES

    To see the list of stored procedures in the database with thier properites use

    
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES

    Information schema to find foreign keys associated with a table
    More Information_Schema

  • SYSINDEXES:

    Many people use count(*) to get the number of rows in a table, for this operation the MSSQL server recounts the rows thus leading to slower process. The table properties will have its rowcount at any point of time. The best way is to access from sysindexes as follows

    
    select rows from SYSINDEXES WHERE ID = OBJECT_ID('TableName') and indid < 2

Happy Learning 🙂

Posted in MSSQL | Tagged: , , | Leave a Comment »

List tables linked to a parent table

Posted by codingsense on December 23, 2008

Many times we require to know which child tables are related to the parent table. The following query gives us the tables are related to the parent table with foreign keys.


select TABLE_NAME from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME IN(
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE UNIQUE_CONSTRAINT_NAME IN (
select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name ='ParentTableName'))

Just replace the string ParentTableName with the table name for which you want to search the related child tables.

Happy Learning 😉

Posted in MSSQL | Tagged: | Leave a Comment »

Guidelines, Tips and Tricks MSSQL

Posted by codingsense on December 23, 2008

Here are some of the Tips and Tricks to imporve the performance of MSSQL.

  • Minimize the use of nulls.Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values.
  • Use TRUNCATE TABLE statement instead of DELETE clause if you want to delete all rows from a table
  • Do not use reserved words for naming database objects, as that can lead to some unpredictable situations
  • Avoid using the new bigint data type unless you really need its additional storage capacity. The bigint data type uses 8 bytes of memory verses 4 bytes for the int data type
  • Don’t use “sp_“ as your prefix for stored procedures – it is a reserved prefix in MS SQL server!
  • The Union All statement is much faster than Union, because Union All statement does not look for duplicate rows, and Union statement does look for duplicate rows, whether or not they exist.
  • Always put the Declare statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
  • Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
  • To avoid trips from application to SQL server, we should retrieve multiple resultset from single Stored procedure instead of using output param.
  • If stored procedure always returns single row result set, then consider returning the result set using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than result set returned by SELECT statements.
  • Avoid (*), Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns.
  • Use SET NOCOUNT ON statement in your stored procedures to reduce network traffic.
  • Call stored procedure using its fully qualified name.The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.
  • Do not query/manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure
  • Except or Not Exist clause can be used in place of Left Join or Not In for better performance.

If any more additional tips or guidelines then please post a comment.

Happy Learning 🙂

Posted in MSSQL | Tagged: , , | Leave a Comment »

Bulk Insert and Update MSSQL

Posted by codingsense on December 23, 2008

Bulk insert is a good feature that inserts data into a table from any file at great speed. But this option is only applicable for insertion there is no option for updation. If we have a primary key in our table then many times we face the problem “Cannot insert duplicate key”. I faced the same problem while using bulk insert so made a manual way to handle updates during bulk insert.

I will explain you the whole process which i followed with a sample

Create a CSV file:
Consider a CSV file containing email, firstname, lastname and DateOfBirth of clients as below and save the file as Test.csv in C:\


naveen@gmail.com,Naveen,Prabhu,08/04/1983
aatish@yahoo.com,Aatish,Sethi,10/08/1984
gautham@hotmail.com,Gautham,Nayak,10/08/1984
manas@rediff.com,Manas,Patnaik,10/08/1984
raghu@India.com,Raghu,Kini,10/08/1984

Create a table TestCSV:
For the above CSV file we need to create a table with the same fields, here email will be a unique key so the table can be designed as,

Create table TestCSV
	(
		Email Nvarchar(100),
		FirstName varchar(100),
		LastName varchar(100),
		DOB datetime,
		Constraint PK_TestCSV Primary Key(Email)
	);

Create a stored procedure for Insertion and Updation:

Create PROCEDURE InsertClientInfo( 
@CSVPath NVARCHAR(200))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Create a Temporary table to input the data from CSV file    
Create table #LookUpTable
	(
		Email Nvarchar(100),
		FirstName varchar(100),
		LastName varchar(100),
		DOB datetime,
	);

	--Declare a Variable to hold the command for Bulk insert    
Declare @UpdateQuery NVArchar(200)
	Set @UpdateQuery = 'bulk insert #LookUpTable
	from ''' + @CSVPath + ''' with(
	fieldterminator = '','',
	rowterminator =''\n'')';

	--Execute the Bulk Insert command
	Exec SP_ExecuteSQL @UpdateQuery

	--Check if Email from is already in the database and delete those from TestCSV
	Delete from TestCSV where Email in (Select distinct Email from #LookUpTable);

	--Insert the Updated or New entries in TestCSV table
	Insert into TestCSV select Distinct * from #LookUpTable;

	--Drop the temporary table
	Drop table #LookUpTable;
END

Run the Stored Procedure:

InsertClientInfo 'C:\Test.csv'

This will insert the new records and update the old ones from the file Test.CSV into TestCSV table

Allow Duplicates
If your table does not have a primary key and can accept duplicates then the stored procedure will be simple and will look like

Create PROCEDURE InsertClientInfo( 
@CSVPath NVARCHAR(200))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Set @UpdateQuery = 'bulk insert TestCSV
	from ''' + @CSVPath + ''' with(
	fieldterminator = '','',
	rowterminator =''\n'')';

	--Execute the Bulk Insert command
	Exec SP_ExecuteSQL @UpdateQuery
END

Hope you are clear with the article, If you have any other good solution for this issue, Kindly help.

Happy Learning 🙂

Posted in MSSQL | Tagged: | 14 Comments »

Asynchronous call to MSSQL using ADO.Net

Posted by codingsense on December 22, 2008

Hi friends,

I was working on a small project that had a lot of computations happening in stored procedures.

In beginning all worked fine, when there was increase in data the stored procedure would take more time to complete the process and the application would raise command timeout, once i increased the command timeout of the connection it worked well but when again the data increased it failed i had to increase the timeout much more.

I thought it would not be feasible to increase the command timout to max, so was searching for some alternate and found a nice concept called “Asynchronous execution of the Transact-SQL” .

This worked very fine and i was able to overcome the following issues

  • No need to worry about command timeout
  • Other tasks can be done till MSSQL finishes executing the stored procedure

Here is the code i used for Asynchronous execution

string ConnectionString = @"Data Source=NAVEEN\SQLEXPRESS;Initial Catalog=Email;Integrated Security=True;Max Pool Size=5000;Connect Timeout=120; Asynchronous Processing=true";
using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
{
sqlConn.Open();
SqlCommand command = new SqlCommand("ShiftNewEmails", sqlConn);
command.CommandType = CommandType.StoredProcedure;
IAsyncResult asyncResult = command.BeginExecuteNonQuery();
while (!asyncResult.IsCompleted)
{
}

MessageBox.Show(string.Format("Shifted {0} Email id's ", command.EndExecuteNonQuery(asyncResult)));
}

The key points to focus in the above code are

  • Asynchronous Processing=true -> in the connection string without this you cannot use Asynchronous execution.
  • command.BeginExecuteNonQuery() -> Starts the execution of the stored procedure in asyn mode.
  • asyncResult.IsCompleted -> Will be false till the mssql executes the command, once MSSQL finishes the value will be true.
  • command.EndExecuteNonQuery(asyncResult)-> Gets the result of the execution from MSSQL in the asyncResult parameter and completes the task.

I did the following task while MSSQL is executing the command, this gave the user an idea that the process is working fine.

int FixedString = lblProcessing.Text.Length;
int Dots = 0;
while (!asyncResult.IsCompleted)
{
if (Dots > 10)
Dots = 0;
lblProcessing.Text = "Processing details please wait". PadRight(FixedString + Dots,'.');
Dots++;
Application.DoEvents();
Thread.Sleep(100);
}

So you can use this feature to make your application more user friendly and avoid program form showing “NOT RESPONDING”.

Happy Learning 🙂

Posted in ADO.Net | Tagged: , | 2 Comments »

Shrink Database and Files in MSSQL

Posted by codingsense on December 22, 2008

To increase your MSSQL database performance use Shrink database and files.

Each MDF file will have its LDF (LOG) file with it. Any transaction made to the database will lead to increase of size in MDF as well as LDF files. Over a period of time you will notice that eventhough your database has less data, the file size of both MDF and LDF will be large which leads to slower processing of queries and lead to other maintainance problems.

So MSSQL has given an option to remove unused spaces.

To shrink the database and files through Management studio you can follow the below steps

  • Open Object explorer
  • Right click on the database that needs to be shrinked
  • Tasks
  • Shirnk Database
  • Shrink Files

The following are the commands to shrink Database and Files through T-SQL.

Shrink Database :

dbcc ShrinkDatabase(DatabaseName)

The database name is the name that is seen in the object explorer of Management studio.

Shrink Files :

dbcc ShrinkFile(Logical Name)

The logical name of the database can be seen using the following query
Select name from sysfiles

Here 2 rows will be listed first one will represent the logical name of MDF file and other is for LDF file. Execute the DBCC query twice with both the names to shrink both MDF and LDF files, like

dbcc ShrinkFile(DB)

dbcc ShrinkFile(DB_LOG)

In my case i was working on a Email Project and the database would increase rapidly, so i make a stored procedure and gave user the option to shrink the database. In the button click event i called the stored procedure which was

Create PROCEDURE [dbo].[ShrinkEmail]
AS
BEGIN
 DBCC ShrinkDatabase(Emails)
 DBCC ShrinkFile(Emails)
 DBCC ShrinkFile(Emails_LOG)
END

This reduced my LOG file from size 7GB to 504KB and MDF file changed from 2.8 GB to 2.3 GB. 🙂

Caution: There are many discussions going over the net whether the MSSQL performance increases or decrease by shrinking the databases and files. So please do refer the following links and take appropriate decision.

http://technet.microsoft.com/en-us/library/ms189493.aspx
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

Happy Learning 🙂

Posted in MSSQL | Tagged: , | 8 Comments »