SET ROWCOUNT will not be supported in future version of SQL Server

Things are changing very rapidly in SQL Server future versions. Some of the features which were once treated as standard solutions to some typical scenarios of TSQL are now scheduled to be deprecated in future versions of SQL Server. One of these feature was the SET ROWCOUNT statement. This statement is also scheduled for being deprecated in future versions of SQL Server. The only way to limit your results will be by using the TOP keyword.

Similarly some other SET options will no longer be supported like

SET ANSI_NULLS
SET ANSI_PADDING
SET CONCAT_NULL_YIELDS_NULL

I will suggest you to study the complete list of deprecated database features here
and make sure you are not relying too much on these features, if you want to make sure that your database is compatible with future versions of SQL Server.

Finding tables which have Identity column

Following is a quick trick to find all the tables which have identity column.

USE Adventureworks;
GO

SELECT SCHEMA_NAME(schema_id) AS [Schema],name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 1
ORDER BY [Schema], [Table];
GO

Please note that if you want to find all those tables which do not have identity column then just change the where clause as follows:

WHERE OBJECTPROPERTY(object_id,‘TableHasIdentity’) = 0

Execute stored proecdure every time with the start of SQL Server

Remember good old days of DOS when you were able to execute any file by adding it autoexec.bat file? What if you need to perform some processing every time your SQL Server starts? The answer is sp_procoption By using this system stored procedure you can schedule a stored procedure to be executed every time your SQL Server get restarted.
Please keep in mind that the stored procedure:

• must be in master database
• must not have INPUT or OUTPUT parameters.

To use sp_procoption, you must be a member of sysadmin role.
Following is an example to schedule a simple stored procedure which increment a counter to give you an idea how many times your SQL Serever has been restarted.

Use master
Go
–Create a test table in master database
Create table testTable (startupCount int)

–Create a stored procedure to schedule at startup
Create proc usp_IncrementStartupCount
as
Begin

If exists (Select top 1 * from testTable)
Begin
Update testTable Set startupCount=startupCount+1;
End
Else
Begin
Insert into testTable values (1);
End
End

–Schedule the stored procedure for startup
exec sp_procoption ‘usp_IncrementStartupCount’,’startup’,‘on’

———-STOP—————
—RESTART the SQL SERVER —
———-STOP—————

–After restarting, execute the following query
Select * from testTable

Difference between INNER JOIN and INTERSECT

Suppose you have two tables and you want the distinct rows which are common in both the tables what will you do to get them? Most of us will answer that they will use INNER JOIN to get the common rows of two tables, right? Wrong!!!!!!
This is a big misconception that INNER JOIN will always return all the common rows between two tables. In reality, INNER JOIN treats two NULLS as two different values rather than a same value so if you are joining a row based on a NULLable column there is a chance that if both tables have NULLs in that column then INNER JOIN will ignore those rows because

Two NULLs are not same in TSQL

To correctly retrieve all common rows between two tables, SQL Server 2005 has introduced the INTERSECT keyword. INTERSECT treats two NULLs as a same value and it returns all rows which are common in both the tables.
Run the following example and you will see the result yourself:

–Create two table variables for testing
Declare @m_table1 table (id int, firstName varchar(50))
Declare @m_table2 table (id int, firstName varchar(50))

–Fill the tables with NULLs
Insert into @m_table1 values (1,NULL)
Insert into @m_table2 values (1,NULL)

—Getting all common rows by Inner Join
Select t1.*
from @m_table1 t1
inner join @m_table2 t2
On
t1.id=t2.id and t1.firstName=t2.firstName

—Getting all common rows by INTERSECT
Select * from @m_table1
INTERSECT
Select * from @m_table2

Avoid accidental Truncate command on table

Sometimes, it is very critical to make sure that no body truncate the table accidentally. Since Truncate table command is not logged therefore, it is difficult to recover the data without using specialized tools.
Here is a quick tip to avoid accidental Truncate command. Just create a dummy foreign key which refers to your critical table and that is it. Truncate table command will not be executed if there is any foreign key refers to the primary table even if there is no data in secondary table. Following is a sample code to do this:

Use tempdb
Go

—Create Primary table
Create table test1 (UserId int not null, UserName varchar(50))

–Add Primary key
ALTER TABLE dbo.test1 ADD CONSTRAINT
PK_test1 PRIMARY KEY CLUSTERED
(
UserId
)

–Create Secondary table
Create table test2 (id int)

–Add Foreign key
ALTER TABLE test2 ADD CONSTRAINT
FK_dummy FOREIGN KEY
(
Id
) REFERENCES test1
(
UserId
)
–Now try to truncate the primary table
truncate table test1

SQL server will display the following the error:

Msg 4712, Level 16, State 1, Line 3
Cannot truncate table ‘test1′ because it is being referenced by a FOREIGN KEY constraint.

Finding client application name by TSQL

A friend of mine asked me is there any way to find the name of the application or client which is connected to the SQL Server by using just TSQL? I gave him the answer and thought that it will be useful to other readers of my blog. It is very simple to find the client application’s name. Following is the sample code:

Select APP_NAME() as currentApplication

Optimized solution of Paging by using Count(*) OVER() functionality

Paging through a dataset is always a requirement of any data oriented application. There are several solutions to implement paging. Some of them are client based and some are server based.
Every paging solution needs:

1. Records of only selected page
2. Total no. of records to calculate total of pages available

Whatever solution you implement, you will end up doing at least two queries, first to find total no. of records and secondly to find the records of your selected page.

In oneof my previous posts, I showed you the way of implementing paging by using Row_Number() function of SQL Server 2005. Today, I am showing you an excellent trick to get total of records and list of selected records in just one query. Following is the sample code to implement this logic. This code demonstrates the way you can implement paging very effectively without hitting the server twice. Note down the use of Count(*) OVER() functionality.

Use AdventureWorks
Go

–Change these two variable to parameters of your
–actual stored procedure

Declare @PageNumber int
Declare @PageSize int

–Assume we need page 6 i.e. records from 51-60
Select @PageNumber=6
Select @PageSize=10

–Select only those records which
–fit into a single page
Select Top(@PageSize) * from
(
Select
RowID=ROW_NUMBER() OVER (ORDER BY Name),
ProductID,Name,ProductNumber,ListPrice,
TotalRows=Count(*) OVER() –Count all records
from Production.Product
) A
Where A.RowId > ((@PageNumber-1)*@PageSize)

Calculate Product of the numeric values by TSQL

TSQL Provides you mathematical functions like SUM(), AVG() etc. but it does not provide you a way to calculate Product of a list of values. Most of us know from our high school mathematics that Multiplication can be converted into a series of addition. If you use remember basic logarithms then you can easily calculate the product of the values. To refresh your concepts of logarithms please visit wikipedia here
Now, following is a sample code to demonstrate how you can calculate the product of the values by TSQL

Please be aware that since logarithms involves decimal numbers therefore, the result will not be 100% accurate but it will serve the purpose in most of the cases.

–Declare a sample table
Declare @m_TestTable table (sampleValue int)

–Fill the sample table with 10 values
Declare @m_value int
Select @m_value=1
While (@m_value < 10)
Begin
Insert into @m_TestTable values(@m_value)
Select @m_value=@m_value+1
End

–Calculate the product
Select Power(10,Sum(Log10(sampleValue))) from @m_TestTable

Retrieve SQL Server Service account name from registry through TSQL

Here is a quick and easy way to get the account name under which current SQL Server service is running. This script will also teach you how you can use an undocumented extended stored procedure to read windows registry through TSQL. I got this code from Microsoft newsgroup and I have checked it on SQL Server 2005. All credit goes to the actual writer of the code.

–Declare a variable to hold the value
DECLARE @serviceaccount varchar(100)

—Retrieve the Service account from registry
EXECUTE master.dbo.xp_instance_regread
N‘HKEY_LOCAL_MACHINE’,
N‘SYSTEM\CurrentControlSet\Services\MSSQLSERVER’,
N‘ObjectName’,
@ServiceAccount OUTPUT,
N‘no_output’

—Display the Service Account
SELECT @Serviceaccount

Generating 1 million rows in less than a second

If you are a SQL Query developer like me than you must came across the situation where you need an auxiliary table of numbers which contain just one column and rows like 1,2,3…….n
There are so many uses of this table like generating dummy data by cross joining this table to another table etc.
Following is a very quick and efficient way of generating 1 million rows in less than a second by using Common Table Expressions or CTE and Recursion of SQL Server. This logic is actually proposed in the book called SQL Server 2005 TSQL Querying by Microsoft press.

Following is the code:

–Declare a variable to hold the
–count of rows to be generated
Declare @p_NumberOfRows Bigint

–We need 1 million rows
Select @p_NumberOfRows=100000;

With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C
From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n
From Expand
)
Select n from Nums Where n<=@p_NumberOfRows
–Remove Maximum Recursion level constraint
OPTION (MaxRecursion 0);