Find third highest salary from Employee table

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 INTIDENTITY(1,1)
,FirstName varchar(10)
,LastName varchar(10)
,JoiningDate datetime defaultgetdate()
,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 fromtblEmp
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 distincttop 3 salary fromtblEmp order bySalary
) 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Understanding basic tools of SQL Server 2008/2005

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Generate SP or function definition in SQL Server 2005/2008

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

TimeStamp datatype in SQL Server

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:
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Save sub report as from main crystal report

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Deadlocks in SQL Server 2008

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find Schema Name, row size in bytes and Total Number of rows in table for all tables in SQL Server 2008

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Restart SQL Server and SQL Server Agent from Command Prompt.

Many times I need to restart SQL Server service and SQL Server Agent service in my testing server.. Generally people used to go at Control Panel->Administrative Tools->Services, find SQL Server service or/and SQL Server agent service and start, restart or stop it. This is general practice but I would like to do this from command prompt itself.

Syntax is:

Net Stop mssql$YourSQLServerInstanceName
Net Start mssql$YourSQLServerInstanceName
Above command is used to start and stop SQL Server Service.  Now, here is the command for SQL Server agent.
Net Stop sqlagent$YourSQLServerInstanceName
Net Start sqlagent $YourSQLServerInstanceName
My instance name of SQL Server 2008 is “SQL2k8” so my command would be
Net Stop mssql$SQL2K8
Net Start mssql$SQL2K8
Net Stop sqlagent$SQL2K8
Net Start sqlagent $SQL2K8
Indeed I have one batch file which do this stuff for me. Just go to command prompt and run .BAT file is really easy than going to Control Panel->Administrative Tools->Services, find services and restart it.
Look at the screen below for more details.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah