I had discussion regarding few of the TSQL techniques with our .NET developer team and in-between one of the new .NET developer girl, who have just joined the team, told me that she has been asked in few of the interviews regarding this question “Find third highest salary from Employee table” and she had provided solution with big query and calculation.

After listening this, it comes to my mind that we don’t need any big query with calculations, neither we need Rank or row_number etc., it could be achieved with very short and simple query and don’t need any version specific functions like Row_Number, Rank, Dense_rank.

Let us see how we can achieve this:

create table tblEmp

(

ID INT IDENTITY(1,1)

,FirstName varchar(10)

,LastName varchar(10)

,JoiningDate datetime default getdate()

,Salary numeric(10,2)

)

GO

INSERT INTO tblEmp (FirstName,LastName,Salary)

SELECT 'Rushik','Shah',21000 UNION ALL

SELECT 'Prapa','Acharya',21000 UNION ALL

SELECT 'Kalpan','Bhalsod',35000 UNION ALL

SELECT 'Ashish','Patel',18000 UNION ALL

SELECT 'Hetal','Shah',18000

GO

SELECT * FROM tblEmp

GO

--solution given by new .NET developer

declare @maxsal float

set @maxsal = (select  max(salary) from tblEmp

where salary not in (select max(salary) from tblEmp))

select distinct salary from tblEmp

where (salary != @maxsal) and (salary != (select max(salary) from tblEmp))

GO

--this could be easily achieved by Dense_Rank function

Select Salary FROM(

SELECT distinct Salary,dense_rank() over (order by salary desc) as rn FROM tblEmp

) as t where rn=3

GO

--even easy then windows partioning function

--like Dense_Rank

--especially this is not a SQL Server version specific query

SELECT top 1 Salary FROM

(

      select distinct top 3 salary from tblEmp order by Salary

) as t

GO

You can compare all three different queries as performance point of view via execution plan.

Have fun!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 

In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.

“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.

Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.

--Compatability View

select * from sysdatabases

--Catalog View

select * from sys.databases

Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:

--return information about authenticated session in SQL Server

select * from sys.dm_exec_sessions

--returns info about currently locked object.

select * from sys.dm_tran_locks

--this is DMF and retun I/O stat for MDF and LDF file.

select * from sys.dm_io_virtual_file_stats(DB_ID(N'master'), 2);

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.

Look at the TSQL below:

select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id

--P for Stored Procedure

--FN for scalar function

--IF for Inlined table function

--TF for Table function

--TR for trigger

where so.type in ('P','FN','IF','TF','TR')

--if you want defination of specific function or SP, include below condition too

--and so.name='YourSPorFunctionName'

Order by so.name,sc.colid

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Generally It is good practice to disable SA login in SQL Server and use SysAdmin account which is created by you to secure you SQL Server from few threats. Well, this is not a scope of this article to explain why you should disable it but I would like to share one quick tip when you face above error while enabling you SA account back.

If you see error like:

ERROR FIX: Alter failed for Login sa - Cannot set a credential for principal 'sa'. (Microsoft SQL Server, Error: 15535)

While enabling your SA account, you have to enable one check box “Map to credential” from “General” table. Once you are done with that, go to “Status” table and make your user “Enable” with radio button under “Status” tab.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Well TimeStamp datatype is one of the old datatype in SQL Server and going to deprecated in newer version so it is not advisable to use it. The main intention to write this article is, recently I have see few people discussing TimeStamp datatype with misconception in one of the forum.

I wonder people got this datatype wrong by understanding it as a real data or time datatype. Actually TimeStamp datatype has nothing to do with Date or time in SQL Server.

As per MSDN, TimeStamp is

“timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.”

Let us see one small example which proves above statement.

create table TimeStampTesting

(

Name varchar(10),

TS TimeStamp

)

Insert Into TimeStampTesting(Name)

Select 'Ritesh' union all

Select 'Rajan' union all

Select 'Bihag'

GO

--since we are making order by on TS

--Bihag should be first as that record was inserted last

Select * from TimeStampTesting order by TS desc

Go

Update TimeStampTesting set Name='Rajan S.' where Name='Rajan'

GO

--if you observe, this time Bihag wouldn't first

--but Rajan S. would be the first as it updated last

--so TS is a binary unique number which updates itself automatically

--for new upate and/or insert

Select * from TimeStampTesting order by TS desc

Go

BTW, now a day, you should use RowVersion datatype rather than TimeStamp as I told you above too that TimeStamp will be deprecated and RowVersion is synonyms for TimeStamp. For more information, look at the below URL:

http://msdn.microsoft.com/en-us/library/ms177566.aspx

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Recently I was working in one of the very big windows application which has been developed with the following tools, framework and software.

1.)    Visual Studio 2008

2.)    .Net Framework 3.5

3.)    C#

4.)    SQL Server 2005

5.)    Crystal Report 11

There are lots of crystal reports used in that application. While working on that application (product) I needed to create on very big crystal report. Fortunately or unfortunately the same kind of report was a part of (as a sub report) one main report. If I can extract that sub report from main reports, I could probably save my so many hours.

Basically we used to create a simple report and import that report as a part of sub report in other main reports. Unfortunately main report was deleted by somebody so the only way was to extract report from main report as that report was working fine under main report.

I have opened that main report which has a link to sub report, I right click on that sub report link to get option “Save Sub report as” in visual studio but no luck.  Look at the image below.

Actually visual studio doesn’t have that option so I have seen many programmers start looking at sub report and create new report based on that which kills few hours if report is big.

So what is the solution? If you are having full licensed version of crystal report than open crystal report viewer, open same report in that and you will get an option to “Save sub report as” look at image below.

Isn’t it easy and time saving stuff?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

There are few different kinds of lock available in SQL Server but the most resource consuming especially CPU power is DeadLock. It is better to know about deadlock and try to reduce as much as possible. First of all let us understand what is deadlock and how deadlock happens?

Deadlock happens when two or more processes waiting for the resource and wait for other process to finish and eventually neither of them ever does it at that time SQL Server select one process and abort other process.

Let us see one small example to create dead lock.

--create one two tables for demo and insert dummy records.

create table DL1

(

id int,

name varchar(10)

)

go

create table DL2

(

id int,

name varchar(10)

)

go

insert into DL1

select 1,'ritesh' union all

select 2,'rajan'

go

insert into DL2

select 1,'vipul' union all

select 2,'darshan'

go

--open other query window, will consider this window as A1

begin tran

update dl1 set name='rit' where id=1

go

--open other query window, will consider this window as A2

begin tran

update dl2 set name='vi' where id=2

go

--again come back to window A1

update dl2 set name='d' where id=2

go

Now this statement will create lock and query got hold but it is not a dead lock so far.

--now comeback to A2 window and execute following command

update dl1 set name='raj' where id=1

go

Now, this statement creates dead lock and you will be greeted with the error message something like below and your

Msg 1205, Level 13, State 45, Line 2

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As soon as you execute COMMIT or ROLLBACK, your lock gets release. So, this is how deadlock generates so while designing the database, keep relatively normalize. Keep your transaction as short as possible and try to use TRY….CATCH block so if there is any error in your TRY block, it transfer the focuses to CATCH block and you can ROLLBACK transaction from there.

BTW, if you want to find tables which are being locked, have a look at my article here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

System catalog and views provided inbuilt with SQL Server, are really wonderful, very useful and handy to get some inside information immediately. Today I want to show you use of following different system catalog and views.

SysObjects: used to show you list of all objects in database
SysColumns: used to show you list of all columns of all tables
Information_Schema.Columns:  also going to show you the list of columns for all tables, some information are easy to access other than SysColumns but note that SysColumns is powerful than this one
Sys.Partitions: Generally tables and indexes are in at least one partition in SQL Server 2008 so I use it as a handy tool to get total number of rows in each table.

Now, here I present one very small code snippet which will show you Schema Name, Table Name, Total Maximuz size of row in table and total number of rows exits in table. I have used above introduced system catalog to get these information, You may have different combination to get this kind of information.

select

      isc.TABLE_SCHEMA as SchemaName,

      so.name as TableName,

      SUM(sc.length) AS RowSizeInBytes,

      sp.rows as TotRowsInTable

from

      sysobjects so join

      syscolumns sc on so.name = OBJECT_NAME(sc.id) join

      INFORMATION_SCHEMA.COLUMNS isc on sc.name=isc.COLUMN_NAME and so.name=isc.TABLE_NAME join

      sys.partitions sp on OBJECT_NAME(sp.object_id)=so.name

where

      so.type = 'U'

GROUP BY

      so.name, isc.TABLE_SCHEMA,sp.rows;

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

One of my recent projects was dealing in many databases in one SQL Server instance. I required one procedure which can create one Windows authenticated login and associated user in all databases.  After creating users in databases, I needed to assign datareader and datawriter roles to those users. There is not inbuilt functionality in any of the SQL Server version so thought to create one stored procedure for my need. I have used it in my project and now want to share with all of you.

CREATE PROC CreateWindowsLoginAndUser(@FullLoginName sysname, @ActionName sysname)

as

begin

declare @DataBaseName sysname

declare @SQL varchar(255)

declare @Host sysname

declare @Login sysname

set @Host = LEFT(@FullLoginName,  charindex('\',@FullLoginName)-1)

set @Login = Right(@FullLoginName,   LEN(@FullLoginName)-charindex('\',@FullLoginName))

set @SQL = ''

if (@ActionName = 'CREATE')

        set @SQL = 'USE [master] CREATE LOGIN ['+@FullLoginName+'] from windows'

else

        set @SQL = 'USE [master] DROP LOGIN ['+@FullLoginName+']'

exec (@SQL)

declare dbname cursor for

select name from sysdatabases

where name not in ('master','model','msdb','tempdb') and

DATABASEPROPERTYEX(name,'IsInStandBy') = 0 and

DATABASEPROPERTYEX(name,'Status') not in ('OFFLINE','RESTORING','RECOVERING','SUSPECT','EMERGENCY') and

DATABASEPROPERTYEX(name,'Updateability') not in ('READ_ONLY') and

DATABASEPROPERTYEX(name,'UserAccess') = 'MULTI_USER'

order by name

open dbname

fetch next from dbname into @DataBaseName

while (@@fetch_status <> -1)

begin

        if (@@fetch_status <> -2)

        begin

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] CREATE USER '+@Login+' FOR LOGIN ['+@FullLoginName+']'

        else

                set @SQL = 'USE ['+@DataBaseName+'] DROP USER '+@Login+''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datareader'','''+@Login+''''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datawriter'','''+@Login+''''

        exec (@SQL)

        end

fetch next from dbname into @DataBaseName

end

close dbname

deallocate dbname

end

go

Once you are done with creating script, you can execute it by following commands.

--to create user, use following TSQL

exec CreateWindowsLoginAndUser 'DomainName\WindowsLoginName','CREATE'

GO

--to drop user, use following TSQL

exec CreateWindowsLoginAndUser ' DomainName\WindowsLoginName','DROP'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com