SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for June, 2010

Finding Nth highest number in SQL Server

Posted by Prashant on June 29, 2010


Many times you face this question while working with real time data and most interviews. In this post we will go through different ways to find the records with Nth highest number. Use this script to populate some sample data to test the results.

CREATE TABLE PLAYER
(
PLAYERID INT IDENTITY(100,2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
DOB DATETIME,
COUNTRY VARCHAR(3),
PRIZEMONEY MONEY NOT NULL
)
GO

INSERT INTO PLAYER(NAME,DOB,COUNTRY,PRIZEMONEY) VALUES
('Andre Agassi','4/29/1970','USA',$31152975),  ('Rafael Nadal','6/3/1986','ESP',$27224163), ('Boris Becker','11/22/1967','GER',$25080956),
('Yevgeny Kafelnikov','2/18/1974','RUS',$23883797), ('Ivan Lendl','3/7/1960','USA',$21262417),  ('Stefan Edberg','1/19/1966','SWE',$20630941),
('Goran Ivanisevic','9/13/1971','CRO',$19876579),  ('Michael Chang','2/22/1972','USA',$19145632), ('Lleyton Hewitt','2/24/1981','AUS',$18312036),
('Andy Roddick','8/30/1982','USA',$17109084), ('Novak Djokovic','5/22/1987','SRB',$15984098), ('Gustavo Kuerten','9/10/1976','BRA',$14807000),
('Jonas Bjorkman','3/23/1972','SWE',$14600323),  ('Marat Safin','1/27/1980','RUS',$14373291), ('Jim Courier','8/17/1970','USA',$14034132),
('Carlos Moya','8/27/1976','ESP',$13382822), ('Nikolay Davydenko','6/2/1981','RUS',$1339499),  ('Michael Stich','10/18/1968','GER',$129528),
('Juan Carlos Ferrero','2/12/1980','ESP',$12588898),  ('John McEnroe','2/16/1959','USA',$12552132), ('Thomas Muster','10/2/1967','AUT',$12225910),
('Tim Henman','9/6/1974','GBR',$11635542), ('Sergio Bruguera','1/16/1971','ESP',$11632199),  ('Patrick Rafter','12/28/1972','AUS',$11127058),
('Thomas Enqvist','3/13/1974','SWE',$10461641),  ('Petr Korda','1/23/1968','CZE',$10448900), ('Alex Corretja','4/11/1974','ESP',$10411354),
('Todd Woodbridge','4/2/1971','AUS',$10095245), ('Richard Krajicek','12/6/1971','NED',$10077425), ('Roger Federer','8/8/1981','SUI',$53362068),
('Pete Sampras','8/12/1971','USA',$43280489)
GO

Here is 4 different ways to find the same result.

--Define Level of N
DECLARE @N INT = 3

--Method-1: Without using any functions
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT DISTINCT PRIZEMONEY FROM PLAYER p1 WHERE (@N-1)=
(SELECT COUNT(DISTINCT p2.PRIZEMONEY) FROM PLAYER p2 WHERE p2.PRIZEMONEY > p1.PRIZEMONEY ))

--Method-2: Using TOP
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT TOP 1 PRIZEMONEY FROM PLAYER
WHERE PRIZEMONEY in (SELECT DISTINCT TOP (@N) PRIZEMONEY FROM PLAYER ORDER BY PRIZEMONEY DESC)
ORDER BY PRIZEMONEY)

--Method-3: Using MIN() function
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT MIN([t].[amt]) FROM
(SELECT DISTINCT TOP (@N) PRIZEMONEY AS [amt] FROM PLAYER ORDER BY PRIZEMONEY DESC) AS [t])

--Method-4: Using Dense_Rank()
;WITH cte
AS (
SELECT NAME,DOB,COUNTRY,PRIZEMONEY,  DENSE_RANK() OVER (ORDER BY PRIZEMONEY DESC) AS [rnk]
FROM PLAYER t
)
SELECT NAME,DOB,COUNTRY,PRIZEMONEY
FROM cte
WHERE [rnk] = @N

Also let me know your suggestions on this.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , , , | Leave a Comment »

CHECK constraint with User Defined Function in SQL Server

Posted by Prashant on June 25, 2010


This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

/*
This function will take Date Of Birth as input parameter,
and returns Age in Years to the caller
*/
CREATE FUNCTION [dbo].[fnGetAge](@DateOfBirth DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Age SMALLINT
SET @Age =(DATEDIFF(YY, @DateOfBirth, GETDATE())-
(CASE
WHEN GETDATE() >= DATEADD(YY, DATEDIFF(YY, @DateOfBirth, GETDATE()), @DateOfBirth) THEN 0
ELSE 1
END))
RETURN @Age
END;
GO

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

--Create Customer table
CREATE TABLE Customers
(
CustID INT IDENTITY(1,1) NOT NULL,
CustName VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NOT NULL,
Email VARCHAR(100),
CONSTRAINT pkCustomers PRIMARY KEY(CustID),
--Calculate & check if age of customer is atleast 18 Years
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfBirth) >= 18)
)
GO

--Populate table with some sample data.
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('ABC','19810726','abc@cust.info'),
('XYZ','19840510','xyz@cust.info'),
('MNO','19720417','mno@cust.info')
GO
--Result Message
--(3 row(s) affected)

Now try to insert a record where calculated age is less than 18 years and see what happens.

--Try to insert a record where Age less than 18 Years(as per provided Date of Birth)
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('TEST','20010315','test@cust.info')
GO

/*
--Error Message
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chkCheckAge".
The conflict occurred in database "SQLJourney", table "dbo.Customers", column 'DateOfBirth'.
The statement has been terminated.
*/

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record to the table.

Note:

CHECK constraint evaluates the provided expression while UPDATE operation as well.

You cannot DROP the function till the table (which refers to that function) exists in the database.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , , | 4 Comments »

Generate Report with Alternate Order of an Attribute (say Gender) in SQL Server

Posted by Prashant on June 23, 2010


Some times we may need to generate a report which display employees/customers in alternate order of any particular attribute.This post describes for employees with alternate order of Gender i.e. first a Male employee then a Female employee and so on or vice versa.

In SQL Server this is just a simpler way. There could be may ways to do this. Here is my set based approach with ROW_NUMBER() function.

DECLARE @EMPLOYEE TABLE
(
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(1) NOT NULL
)

--Populate sample data
INSERT INTO @EMPLOYEE(NAME,GENDER) VALUES
('Anup','M'), ('Sheetal','F')
,('Sonam','F'), ('Payal','F')
,('Parul','F'), ('Peter','M')
,('Rahul','M'), ('Roxy','F')
,('Preeti','F'), ('Manav','M')

SELECT * FROM @EMPLOYEE

--Result
;WITH CTE(SEQ,ENAME,GENDER)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER), NAME, GENDER FROM @EMPLOYEE
)
SELECT ENAME,GENDER FROM CTE
ORDER BY SEQ, GENDER DESC
GO

Also put your valuable suggestions on this.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , , | Leave a Comment »

SQL Server Reporting Tool – SqlAnswersQuery

Posted by Prashant on June 21, 2010


Here is a FREE tool SqlAnswersQuery you can use for SQL Server reporting purposes.  In fact this tool is also compatible with other databases like Microsoft Access, Oracle, MySQL and DB2

You can download it from http://www.sqlanswers.com/Software/SAQ/Default.aspx.

I found it interesting. Let me know your experience with this tool.

Posted in General, SQL Server, SQL Server Tools | Tagged: , , , , , , | 2 Comments »

My Windows is 32-bit or 64-bit Operating System?

Posted by Prashant on June 17, 2010


While installing a application to my machine, I come up to check my Windows bit count. So here is the quick post on how to find the Windows Bit Count.

The machine I was working was with Windows XP operating system and the steps are:

Go to Start >> Programs >> Accessories >> System Tools >> System Information.

(OR)

Start >> Run >> type “winmsd.exe” >> OK

Now under “System Summery” find an item named “System type”. If its value is x86-based then the CPU is 32-bit or if it’s value is x64-based then CPU is 64-bit (the image below is of a 32-bit machine).

For more information on this, visit http://support.microsoft.com/kb/827218.

Posted in General, Operating System | Tagged: , , | Leave a Comment »

Insert Excel Data into a SQL Server table using OPENROWSET

Posted by Prashant on June 16, 2010


There are many ways to load excel data into SQL Server database, using DTS, BULK import, SSIS and many others. In this post I will go with OPENROWSET. This is very useful when we need ad-hoc connection to an OLE DB source.

Here below is the image of the excel file taken for demonstrate.

Here is the script to import the file into SQL Server database using OPENROWSET.

SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Import\ExcelDataImport.xlsx; HDR=YES'
,'SELECT * FROM [Sheet1$]') AS exl
GO

Now see the data imported into our table.

SELECT * FROM #myExcelData
GO

Here I have taken Excel 2007 as source and a SQL Server temp table as destination. Let me know if this is useful to you.

Posted in Interview Questions, SQL Server, SSIS | Tagged: , , , , , , | 4 Comments »

Fixing Error Message 1807 could not obtain exclusive lock on database ‘model’ in SQL Server

Posted by Prashant on June 15, 2010


Some days back I got this question from one of my friend, when he was trying to create a New Database, he is getting the below error message.

Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The Situation:

He was trying to create table on Model database first then he tried to create a New Database. As Model database is used as a template for a database being created, SQL Server tries to obtain an Exclusive Lock on Model database. So if one session is using model database SQL Server can’t obtain exclusive lock on model.

If you are using SQL Server Management Studio try these steps:

Step1: Open a New Query (say SQL Query1.sql) and select “model” from database dropdown box.

Step2: Now in another Query (say SQL Query2.sql) run the below query:

select * from sys.dm_tran_locks

Observation: You will find result like below.

(Click on the image to enlarge)

This indicates Shared Lock has been Granted on resource_database_id = 3 (database_id: 3 is for model)

Step3: Now try to create database either from Object Explorer of SQL Server Management Studio or by CREATE DATABASE statement.

Step4: This time run query in step-3 and observe the result.

(Click on the image to enlarge)

This indicates an Exclusive lock on model database but has not been granted yet (it’s in WAIT state).

Solution:

Make sure to disconnect all the sessions which uses model database then CREATE DATABASE statement later. If this problem still remains, restart management studio and try again.

This is tested in SQL Server 2008, also leave your suggestions.

Posted in SQL Server | Tagged: , , , , | 4 Comments »

UNIQUE Key Constraint with Multiple NULL values in SQL Server

Posted by Prashant on June 12, 2010


This was a really interesting situation when we development team members were discussing over this topic. It was a situation where it was not possible to allow multiple NULL values into a column with UNIQUE Key defined in a table. Here I will give the problem first then we will go with the alternate solution for this.

Problem:

In SQL Server it is not allowed to insert multiple NULL values into a UNIQUE Key column. Below is the situation:

--Create employee table
CREATE TABLE EMPLOYEE
(EMPLOYEEID INT NOT NULL
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED
,FNAME VARCHAR(100)
,LNAME VARCHAR(100)
,UNIQUEID INT UNIQUE)
GO
--Insert data values (used row constructor)
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME, UNIQUEID)VALUES
(1,'RAHUL','MISHRA','112342115'),
(2,'SHEEL','KURANI','1725421455'),
(3,'SHEETAL','BAJAJ','1423721455'),
(4,'KAUSHIK','NARANG','1123721955'),
(5,'ADRIAN','THOULISS','1452342145')
GO
--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(6,'SAUGAT','KIOHLI')
--See below the result
SELECT EMPLOYEEID, FNAME, LNAME, UNIQUEID FROM EMPLOYEE
GO

When I try to enter another record with NULL value to UNIQUEID column, it does not allow me to do that and throws an error.

--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU')

--ERROR MESSAGE
--Msg 2627, Level 14, State 1, Line 2</pre>
--Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__04FF5B33023D5A04'.
--Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
--The statement has been terminated.
Solution:
Here is the alternate solution for the above situation. Instead of define an UNIQUE Key to UNIQUEID column I will create another COMPUTED Column with UNIQUE Key. See how it is done:
--ALTERNATE SOLUTION
--Drop the UNIQUE key
ALTER TABLE EMPLOYEE
DROP CONSTRAINT UQ__EMPLOYEE__04FF5B33023D5A04

--Add a computed column with UNIQUE key
ALTER TABLE EMPLOYEE
ADD ALTUNIQUEID AS CASE WHEN UNIQUEID IS NULL THEN EMPLOYEEID ELSE UNIQUEID END
GO
ALTER TABLE EMPLOYEE
ADD CONSTRAINT UKey_QNIQUEID UNIQUE (ALTUNIQUEID)
GO

--Now insert multiple records with NULL to UNIQUEID column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU'),(8,'TARUN','KISHORE')
GO
--See below the result
SELECT * FROM EMPLOYEE
GO

Your valuable comments are most appreciated.

Posted in Interview Questions, SQL Server | Tagged: , , , , , | 2 Comments »

Insert or Copy Data into Table

Posted by Prashant on June 11, 2010


This post is for a common situation most of us face while working with a database, which is copy/insert to a table.  We can use INSERT & SELECT to copy data into a database table in three ways.

 

  • INSERT Statement (Direct insert data values into the table):

Using INSERT statement we can add one data row at a time to destination table.

--Example: 1
CREATE TABLE Table01
(Column1 INT PRIMARY KEY, Column2 varchar(10))
GO

INSERT INTO Table01(Column1,Column2) VALUES(101,'A')
INSERT INTO Table01(Column1,Column2) VALUES(102,'B')
INSERT INTO Table01(Column1,Column2) VALUES(103,'C')
INSERT INTO Table01(Column1) VALUES(104)
GO

SELECT Column1,Column2 FROM Table01
GO
  • INSERT Statement with a SELECT Sub-Query:

If the destination table (we want to populate) is already created in the database, then using INSERT statement with a SELECT sub-query, we can copy data rows from one table to another.  The number of columns listed in INSERT statement must be same as the columns listed in SELECT statement and their data types must match.

--Example: 2
CREATE TABLE Table02
(Column1 INT IDENTITY, Column2 VARCHAR(10),
Column3 VARCHAR(8) DEFAULT('Welcome1'))
GO

INSERT INTO Table02(Column2)
SELECT Column2 FROM Table01
GO

SELECT Column1, Column2,Column3 FROM Table02
GO
  • SELECT with INTO Statement:

If the destination table (we want to populate) is not available in the database, then using SELECT INTO statement also it will create the table on the fly and copy data to it from the source table.

--Example: 3
SELECT Column1, Column2, Column3
INTO NewTable
FROM Table02
WHERE Column2 IS NOT NULL
GO

SELECT * FROM NewTable
GO

Please let me know your valuable comments.

Posted in General, Interview Questions, SQL Server | Tagged: , , , , , | Leave a Comment »

Condition based Update in SQL Server

Posted by Prashant on June 9, 2010


In SQL Server conditional update can be done using CASE.

--Update employee monthly commission based on their rating
UPDATE Employees
SET Comm =
CASE
WHEN (Rating between 2 and 3)THEN (Salary * 0.1)
WHEN (Rating between 3.1 and 4)THEN (Salary * 0.15)
WHEN (Rating between 4.1 and 5)THEN (Salary * 0.25)
ELSE 0.0
END

This example is just to demonstrate a scenario. Here it examines the employee rating to determine what should be the updated Commission. The CASE expression uses range of employee rating to update the commission amount.

Posted in General, Interview Questions | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: