TOP 1 WITH TIES

Ten Ways To Lose Your DBA Job

2007-04-13T17:36:00.000+05:30

Its always good to know how you can find your way out. Steve Jones has this interesting article in SQLServerCentral.com

Two Minute SQL Server Stumpers Vol. 3

2007-04-02T23:01:00.000+05:30

Third volume of Two Minute SQL Server Stumpers from SQLServerCentral is available for download from here. Thanks to RedGate

How you can help in the search for Jim Gray

2007-02-04T22:53:00.000+05:30

http://blogs.msdn.com/dtjones/archive/2007/02/03/how-you-can-help-in-the-search-for-jim-gray.aspx

Which database is more secure? Oracle vs. Microsoft

2006-11-23T13:52:00.000+05:30

Today, SQL Server MVP Frank Kalis pointed to this interesting comparison by NGSSoftware . As per the study, the reported number of security flaws in Microsoft SQL server is near to zero for the period 0f 2005-2006, whereas the Oracle figures are going higher.

Here is the link.

Which database is more secure?Oracle vs. Microsoft

Update: There is another study emphasizing the fact at the Enterprize Strategy Group website.
Also see this comparison from Microsoft.

SQL Server 2005 November CTP

2006-11-08T23:57:00.000+05:30

SQL Server 2005 November CTP is available for download at
http://www.microsoft.com/sql/ctp.mspx#E2

T-SQL Drawing

2006-08-04T14:50:00.000+05:30

Here is a humble attempt to draw a landscape using T-SQL !

NOTE: Before running this query, you should set the dispaly mode to text and Results font to a fixed width Font (Mine is Courier new) in Query Analyzer

SET NOCOUNT ON

DECLARE @ TABLE(id int IDENTITY(1,1), pic CHAR(120))

INSERT INTO @
SELECT REPLICATE(CHAR(126),120)
FROM (SELECT 1 AS N UNION ALL SELECT 2) A,
(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) B,
(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) C

--Tree 1
UPDATE @
SET pic = STUFF(pic, (50 - (-3+ id)) , ((-3+ id) * 2), REPLICATE(CHAR(47),(-3+ id))+ REPLICATE(CHAR(92),(-3+ id)))
WHERE id BETWEEN 4 AND 26

UPDATE @
SET Pic = STUFF(Pic,49, 2, CHAR(43)+CHAR(43))
WHERE id >=6

UPDATE @
SET Pic = STUFF(Pic,48, 4, REPLICATE(CHAR(124),4))
WHERE id >=26

--Tree 2
UPDATE @
SET Pic = STUFF(Pic, 100 - ((id - 6)*3), 6 + (((id -6) * 6)), REPLICATE(CHAR(37), 6 + (((id -6) * 6))))
WHERE ID BETWEEN 6 AND 10

UPDATE @
SET Pic = STUFF(Pic, 89 + ((id -11)*2), 28 - ((id -11)*4), REPLICATE(CHAR(37), 28 - ((id-11)*4)))
WHERE ID BETWEEN 11 AND 14

UPDATE @ SET Pic = STUFF(Pic, 102,2,CHAR(64)+CHAR(64))
WHERE Id >= 14 AND ID <= 25

--Add a branch
UPDATE @
SET Pic = STUFF(Pic, 90,12,REPLICATE(CHAR(61),12))
WHERE Id = 19

UPDATE @
SET Pic = STUFF(Pic, CASE WHEN id % 2 = 0 THEN 88 ELSE 87 END, 3, REPLICATE(CHAR(37), 3))
WHERE ID IN(18,19,20)

--Add a Bird too :)
UPDATE @
SET Pic = STUFF(Pic, 94, 3 , CONVERT(VARCHAR, 0x2F5E5C))
WHERE ID = 20

UPDATE @
SET Pic = STUFF(Pic, 94, 3 , CHAR(43) + CHAR(32) + CHAR(43))
WHERE ID = 19

UPDATE @
SET Pic = STUFF(Pic, 91, 9 , CONVERT(VARCHAR, 0x205C5C2022202F2F20))
WHERE ID = 18

UPDATE @
SET Pic = STUFF(Pic, 91, 9 , CONVERT(VARCHAR, 0x205F7B2A762A7D5F20))
WHERE ID = 17

UPDATE @
SET Pic = STUFF(Pic, 92, 7 , CONVERT(VARCHAR, 0x2020205F202020))
WHERE ID = 16

--Add a house in the hills
UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x7C5F5F5F5F5F5F7C5F7C5F7C))WHERE ID = 11

UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x7C5B5D205B5D207C207C207C))
WHERE ID = 10

UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x2F2F2F2F2F2F2F2F5F5F5F5C))
WHERE ID = 9

UPDATE @
SET Pic = STUFF(Pic, 67, 11 , CONVERT(VARCHAR, 0x2F2F2F2F2F2F2F2F635C))
WHERE ID = 8

UPDATE @
SET Pic = STUFF(Pic, 68, 8 , CONVERT(VARCHAR, 0x5F5F5F5F5F5F5F50))
WHERE ID = 7

--Add fruits to the first tree in random locations.
UPDATE @
SET Pic = STUFF(Pic, ((50 - (id - 4))+ (CAST(RAND() * (id -4) AS INT))), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, 50 + CAST(RAND() * (id -4) AS INT), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, ((50 - (id - 4))+ (CAST(RAND() * (id -4) AS INT))), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, 50 + CAST(RAND() * (id -4) AS INT), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

--Sun
UPDATE @
SET pic = STUFF(Pic,10,6, REPLICATE(CHAR(32),6))
WHERE id IN(2,5)

UPDATE @ SET pic = STUFF(Pic,9,8, REPLICATE(CHAR(32),8))
WHERE id IN(3,4)

--Add the superCool SQL Guy
UPDATE @ SET Pic = STUFF(Pic, 10,7, CONVERT(VARCHAR, 0x285F5F5B5F5F29))
WHERE ID = 28

UPDATE @
SET Pic = STUFF(Pic, 12,5, CONVERT(VARCHAR, 0x5F2F5F5F2F))
WHERE ID = 27

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x207C2F20202F))
WHERE ID = 26

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x207C5C20205C))
WHERE ID = 25

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x5F5F5F285F2F))
WHERE ID = 24

UPDATE @
SET Pic = STUFF(Pic, 16,3, CONVERT(VARCHAR, 0x2F5F2F))
WHERE ID = 23

UPDATE @
SET Pic = STUFF(Pic, 16,3, CONVERT(VARCHAR, 0x5C205C))
WHERE ID = 22

UPDATE @
SET Pic = STUFF(Pic, 10,8, CONVERT(VARCHAR, 0x2F20205C2F20205C))
WHERE ID = 21

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x5F5C206F2F5F))
WHERE ID = 20

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x5C287E6F6F20))
WHERE ID = 19

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x2F20205F5F29))
WHERE ID = 18

UPDATE @
SET Pic = STUFF(Pic, 12,4, REPLICATE(CHAR(95),4))
WHERE ID = 17

UPDATE @
SET Pic = STUFF(Pic, 9,3,CONVERT(VARCHAR, 0x2F202F))
WHERE ID = 22

UPDATE @
SET Pic = STUFF(Pic, 9+ (id -23),2, CONVERT(VARCHAR, 0x5C20))
WHERE ID IN(23,24)

UPDATE @
SET Pic = STUFF(Pic, 11,1, CHAR(124))
WHERE ID BETWEEN 23 AND 27

UPDATE @
SET Pic = STUFF(Pic, 12,4, CASE WHEN id % 2 = 1 THEN CONVERT(VARCHAR, 0x20475559) ELSE CONVERT(VARCHAR, 0x2053514C) END)
WHERE ID IN(22,23)

--Some Bush
UPDATE @
SET Pic = REPLICATE(CHAR(92)+CHAR(124)+ CHAR(47),40)
WHERE id = 31

--River (for Veer :)
UPDATE @
SET Pic = STUFF(pic, (78 + ((31 - id)*5)), (40 -((31 - id)*5)+2), LEFT(CHAR(63) + REPLICATE(CONVERT(VARCHAR, 0x2D205F207E5E7E2D205F7E207E20),4), (40 -((31 - id)*5)+2) ))
WHERE ID BETWEEN 23 AND 31

--And a couple of Fish too
UPDATE @
SET Pic = STUFF(Pic,CASE WHEN ID < 30 THEN 108 ELSE 95 END , 7, CONVERT(VARCHAR, 0x3E3C2828282A3E))
WHERE ID IN(27,30)

UPDATE @
SET Pic = STUFF(Pic,105 , 5, CONVERT(VARCHAR, 0x3C275F3E3C))
WHERE ID IN(28,31)

--Border
UPDATE @
SET Pic = REPLICATE(CHAR(95),120)
WHERE ID IN(1,32)

UPDATE @
SET Pic = STUFF(STUFF(Pic,1,1,CHAR(124)),120,1,CHAR(124))
WHERE id > 1

--Signature
UPDATE @
SET Pic = STUFF(Pic, 112,6, CONVERT(VARCHAR,0x526F6A697074))
WHERE ID = 31

SELECT Pic [ ] FROM @ ORDER By Id

The future of SQL Server

2006-04-11T15:47:00.000+05:30

Paul Flessner, Senior VP of Server Applications at Microsoft, shares his vision about the future of SQL Server in his latest letter here.

How do you get current Job Name?

2006-04-06T16:47:00.000+05:30

Today a colleague asked me this question.
How do you get current Job Name from a job step?

Well, I did'nt know the answer :(

It should be simple. If we want to know the current procedure name from inside the procedure, we can use something like OBJECT_NAME(@@PROCID)
Do we have something like @@JOBID? Well, NO.

I turned to google and learned about SQL Agent Tokens. That was news to me.

So SQL Agent Tokens are placeholders that are automatically replaced by the subsystem during runtime.
In SQL Server 2000, you can refer to an SQL Agent token like [TOKENNAME]. Token names are case sensitive.

Here is an example.

SELECT output_file_name
FROM msdb..sysjobsteps
WHERE job_id = [JOBID]
AND step_id = [STEPID]

SQL Server Agent in SQL Server 2005 has changed the syntax used in tokens from "[X]" to "$(X)" where X is the token name

Here are some relevant links.

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

http://sqldev.net/sqlagent/SQLAgentStepTokens.htm

Responsibilities of a Development DBA

2006-03-30T14:50:00.000+05:30

Recently, I was asked to define the responsibilities of a Development DBA.

The following is the list of items that came to my mind. Feel free to add items you feel appropriate in the comments section.

1. Database design and implementation

Design and implement the database. Normalize and denormalize the database as necessary. This includes the creation of tables, views , common functions and procedures

2. Design, Implement and monitor back end jobs

Define standard notification for all back end jobs. Take corrective action when a job fails. Schedule the job to execute on non-peek hours. Verify job level and step level status on a daily basis.

3. Do database tuning and performance monitoring

Monitor the performance of individual programs and based on the performance results tune/reorganize the tables and indexes.

4. Optimize badly performing queries and procedures

Optimize badly performing programs by modifying/rewriting the programs in statement level.

5. Do application tuning and performance monitoring

Assess the performance of the applications and the load and impact on the database caused by applications. Provide suggestions to concerned developers where database access mechanisms can be optimized.

6. Perform troubleshooting on DB related issues

Help developers to troubleshoot database related issues. Provide trace files containing database access details for a specific database/program/time interval to the developers when required.

Make the developers aware about the known issues/bugs in the software and the limitations of the version/service pack in use.

7. Participate in design and architecture of new CRs.

Participate/review the design and architecture of the CRs to ensure that the design/architecture is developed with scalability and performance considerations.

8. Code Review

Review procedures/functions. Evaluate compliance with the defined standards and best practices. Use available tools best practices compliance.

9. Training

Conduct training on database related topics. Introduce new versions of SQL server. Distribute study materials. Assist the developers to start working with the new version.

10. Extend support to developers for writing complex programs

Help the developers write complex queries in an optimized manner. Share common patterns/tips and tricks used in the industry to solve similar problems.

11. Setup and maintain standards and best practices.

Define and enforce organization wide standards and best practices. Update the standards and practices based on the feedback and review experience.

12. Recommend Architectural changes.

Recommend changes to the current design/architecture which can give a database wide performance boost.

13. Monitor database activities

Monitor the activities on the servers and take corrective action when required. Look for issues like connection creeping, blocks, deadlocks, slow response etc..

14. Perform database health checking

Perform regular health checking activities on the database to ensure integrity, stability and robustness of the database. Check fragmentation levels and statistics concurrency. Perform re-indexing and update statistics when required.

15. Establish and maintain backup policies

Verify the backup process and occasionally test the backup. This includes the database backup and script backup.

16. Define and Enforce security policies

Define the minimum security credentials each role/user requires and enforce the defined security policy.

17. Capacity Planning

Monitor the space allocation and free space on the development and QA environments. Shrink the database files on a regular basis to recover free space. Configure/Recommend auto grow settings for each database based on its growth rate.

18. Disaster Recovery Planning.

Document and test a DRP plan for the development and QA environments. This should be in sync with the DR plan for the production environment.

19. Installation and configuration of SQL Server Databases.

Setup SQL server environments for development, QA and performance testing. Configure the servers and databases based on the requirement.

20. Evaluate, test and apply service packs

Test the service packs released for SQL server before applying them in a production environment to make sure they don't break the existing programs and the service pack is bug free.

21. Evaluate, test and upgrade to new versions

Evaluate the new versions of SQL server. Perform performance/scalability/availability/programmability comparisons with the existing version. Use the new versions to run in-house projects for a comfortable time period and recommend for upgrading to the new version if the results are positive.

22. Evaluate, test and recommend third party components.

Evaluate the components available in market that can boost the performance, secure the server, ensure high availability, prevent data loss, perform fast backups etc.., and if essential, recommend using them.

23. Coordinating and interfacing with developers, support persons, outside vendors, end users, other department team members, and service providers to resolve issues.

Interact with other departments and teams to resolve DB related issues. If required get assistance from the product support team.

BUG: Combination of GROUP BY with HAVING Clause and LEFT OUTER JOIN with Derived Table With LEFT Function Produces Incorrect Result

2006-02-25T13:20:00.000+05:30

Applies To : SQL Server 2000 RTM to SP4

This bug is a variant of KB 308458 which is fixed in SP2.

Symptoms

If all of the following conditions are true for a query, the query will return extra rows.

• You include GROUP BY and HAVING clauses.
• You use a LEFT OUTER JOIN.
• You incorporate derived tables.
• You use an aggregate.
• You use the LEFT function.

Cause
The optimizer inappropriately processes a cross join.

Steps to Reproduce

Execute the following query in Query Analyzer

Use Pubs
GO

SELECT T.pub_id, T.type, SUM(T.price) AS BasePrice
FROM Titles T
LEFT JOIN
(Select 1) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
--HAVING SUM(T.price) > 0

You will get the following results

pub_id	type	BasePrice
1389	business	19.9900
0877	mod_cook	2.9900
0877	UNDECIDED	NULL

Now try executing the above query by uncommenting the the HAVING caluse to exclude the NULL value.

Use Pubs
GO

SELECT T.pub_id, T.type, SUM(T.price) AS BasePrice
FROM Titles T
LEFT JOIN
(Select 1) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price) > 0

you will get the following incorrect result.

pub_id	type	BasePrice
1389	business	19.9900
0877	mod_cook	19.9900
1389	business	2.9900
0877	mod_cook	2.9900
1389	business	NULL
0877	mod_cook	NULL

If you look at the execution plan, you can see that the query performs an incorrect cross join.

|--Nested Loops(Inner Join)
..... |--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1018]=0) then NULL else [Expr1019]))
..... |....|--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id]) DEFINE:([Expr1018]=COUNT_BIG([T].[price]), [Expr1019]=SUM([T].[price])))
......|........|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
......|..............|--Clustered Index Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]), WHERE:(substring([T].[title], 1, 3)='The'))
......|--Table Spool
...........|--Filter(WHERE:([Expr1002]>0.00))
................|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then NULL else [Expr1021]))
.....................|--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id]) DEFINE:([Expr1020]=COUNT_BIG([T].[price]), [Expr1021]=SUM([T].[price])))
..........................|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
...............................|--Clustered Index Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]), WHERE:(substring([T].[title], 1, 3)='The'))

Workaround
You can workaround the problem by just replacing the LEFT function with SUBSTRING function.

SELECT T.pub_id, T.type, SUM(T.price) AS BasePrice
FROM Titles T
LEFT JOIN
(Select 1) X (pub_id)
ON X.pub_id = T.pub_id
WHERE SUBSTRING(T.title,1,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price) > 0

Note
This bug is fixed in SQL server 2005

Named Constraints on Temporary tables

2006-02-22T20:26:00.000+05:30

Last week I was investigating a peculiar issue reported by our testing team. They are receving an error like the following occassionally.

Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'PK_Temp' in the database.

It seems that the error occurs only when multiple users tries to access the same page. A concurrency issue.

I looked at the code of the procedure being called from the page. I found that a code segment like the following is causing the problem.

CREATE TABLE #Temp(id int NOT NULL)
ALTER TABLE #Temp ADD CONSTRAINT PK_Temp PRIMARY KEY(id)

But why? SQL Server is supposed to make a temp table name unique by adding a unique integer suffix.

I opened two seperate connections in Query Analyzer and execute the above code. Same Error!

It turns that even though the temprary table names automatically made unique between concurrent sessions, the constraint names still has to be unique across a database.

I ended up adding "Do not use named constraints on temporary tables" to the list of best pracices.

Alternatively you can define constraints on temporary tables either by specifying a nameless constructor when you create table, like,

CREATE TABLE #Temp(id int NOT NULL PRIMARY KEY)

Or by implicitly adding a constraint with the following syntax

ALTER TABLE #Temp ADD PRIMARY KEY(id)

SQL Server 2005 System Table map

2005-12-21T12:19:00.000+05:30

The SQL Server 2005 System Table Map shows the system tables included in SQL Server 2005, and the relationships between them.

You can download it here

What is your 2005 IQ?

2005-11-01T13:01:00.000+05:30

Test your SQL Server 2005 knowledge and win grand prizes.

SQL Server 2005 Articles

2005-10-31T11:41:00.000+05:30

A Big collection of SQL Server 2005 related article links are posted at the SQL.RU site.

Here is the link

T-SQL Coding Challenge

2005-10-30T11:46:00.000+05:30

Do you know what quine is?

If yes, there is an interesting challenge on Ken Henderson's blog for self replicating T-SQL code.

As always, Steve Kass came up with the smartest one.

Here are the links

T-SQL Coding Challenge

More about self-reproducing T-SQL

The New Generation of Microsoft Certifications

2005-10-30T11:42:00.000+05:30

The Microsoft Certifications are getting more specific, and is going to be available by early 2006.

Here is an overview

Good News!

2005-10-10T13:10:00.000+05:30

I am committed that I will be posting only things that are 'SQL', not anything personal, here.
But what will I do when I have some great news to tell you guys?

Hmmm.. I will tell that in an SQL (read crazy) way.

So here you go.

SELECT
CHAR(LEFT(P.m,2)) +
CHAR(SUBSTRING(P.m,3,2^1)) +
CHAR(SUBSTRING(P.m,6,~2 * -1)) +
CHAR(RIGHT(P.m,(-3 & 4 -1))) +
SPACE(1)+
STUFF(REPLACE(STUFF(OBJECT_NAME(1), 1, 4, ''),'j',''),4,1,REVERSE('emo'))+
SPACE(1)+ CHAR(ASCII('`')+1)+ SPACE(1)+
LEFT(@@VERSION,20)+
SPACE(1)+ UPPER(LEFT(DB_NAME(1),1))+
CHAR(CAST(LEFT(P.m,2) AS INT)+4)+
CONVERT(CHAR(2), 0x5021) AS [Good News]
FROM
(SELECT REVERSE(V.hugo+V.adam+V.david+V.louis)
FROM
(SELECT
CAST(M.a AS VARCHAR) AS thanksto_JCelko,
CAST(M.a + POWER(8^2,DB_ID('tempdb')) AS VARCHAR) AS thanksto_Sommarskog,
REPLICATE(DB_ID('master'),3) As thanksto_ItzikBenGan,
CAST(M.s AS VARCHAR) As thanksto_Tibor
FROM
(SELECT
(DB_ID('msdb') + 1) * POWER(10,DB_ID('tempdb')) + 1 AS thanksto_ASen,
DATEDIFF(yy, '19760509','20051007')-1 AS thanksto_SKass) As M(a,s)
)AS V(hugo, adam, david, louis)) P(m)
UNION ALL
SELECT CONVERT(VARCHAR, 0x5468616E6B7320746F20796F7520616C6C21)

-- :)

Sampling Using TABLESAMPLE

2005-08-23T15:48:00.000+05:30

With due credits to Steve Kass

SQL Server 2005 Implements the TABLESAMPLE clause, which can be used to retrieve a sample (of rows) from a table.

Here is the syntax of the TABLESAMPLE command.

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

eg.

SELECT * FROM Person.Contact
TABLESAMPLE SYSTEM(50 ROWS)

The optional SYSTEM keyword specifies the sampling algorithm to use. In the case of SQL Server 2005 SYSTEM is the only sampling method available and applied by default. Other products like DB2 has implemented sampling methods like BERNOULLI.

You can specify the number of rows or percentage of rows you want to get back. But remeber that the sampling method gives an approximate number of rows back. So when you are trying to apply TABLESAMPLE on a small table chances are likely that, you may get zero rows back.

WHEN used with the REPEATABLE option, with the same seed value, SQL Server will return the same sample as before. When specified with a different repeat_seed value, SQL Server will likely return a different sample of the rows in the table.

TABLESAMPLE does not work with views or inline table-valued functions.

When I tried TABLESAMPLE first, I tried

SELECT * FROM Sales.StoreContact
TABLESAMPLE SYSTEM(10 ROWS)

And to my surprize, most of the time I was getting Zero rows back, and sometimes 37 or 179 rows. I had reported this issue in a beta newsgroup and SQL Server MVP Steve Kass help me understand it better. Here is the excerpts from his reply.

<QUOTE>
As documented in Books Online ("Limiting Results Sets by Using TABLESAMPLE"),
the sampling algorithm can only return full data pages. Each page is
selected or skipped with probability [desired number of rows]/[rows in table]

The StoreContact table fits on 4 data pages. Three of those pages contain
179 rows, and one contains 37 rows. When you sample for 10 rows (1/75
of the table), each of the 4 pages is returned with probability 1/75 and
skipped with probabiliy 74/75. The chance that no rows are returned is
about (74/75)^4, or about 87%. When rows are returned, about 3/4 of the
time you will see 179 rows, and about 1/4 of the time you will see 37
rows. Very rarely, you will see more rows, if two or more pages are
returned, but this is very unlikely.

As BOL suggests, SYSTEM sampling (which is the only choice) is not
recommended for small tables. I would add that if the table fits on
N data pages, you should not try to sample fewer than 1/N-th of the
rows, or that you should never try to sample fewer rows than fit on
at least 2 or 3 data pages.

If you were to sample roughly two data pages worth of rows, say
300 rows, the chance of seeing no rows would be about 13%. The
larger (more data pages) the table, the smaller the chance of
seeing no rows when at least a couple of pages worth are requested.
For example, if you request 300 rows from a 1,000,000 row table that
fits on 10,000 data pages, only in 5% of trials would you see no
rows, even though the request is for far less than 1% of the rows.

By choosing the REPEATABLE option, you will get the same sample
each time. For most seeds, this will be an empty sample in your
case. With other seeds, it will contain 37, 179, 216, 358, 395,
537, 574, or 753 rows, depending on which pages were selected,
with the larger numbers of rows returned for very few choices
of seed.

That said, I agree that the consequences of returning only
full data pages results in very confusing behavior!
</QUOTE>

That makes things clear. For my question about the usability of the feature, Conor Cunningham, Development Lead of SQL Server Query Optimization team, gave the following answer.

<QUOTE>
From a usability standpoint, we are following ANSI - we are allowed to
arbitrarily over- or under-sample using the SYSTEM keyword. For small
tables, we happen to over-sample by sampling all rows.

We believe that this provides an effective sampling algorithm for some
classes of applications.
</QUOTE>

But my concern was mostly about the zero rows case. I gave the following reply to Conor.

<QUOTE>
I agree the point that in the SYSTEM sampling method, the implementor is
allowed to arbitrarly over/under sample any given set. And I am OK with
that.

But my cause of concern is when I try to get a 10% SAMPLE on a table that has
over 700 rows, I get zero rows back.

To me, Any sampling algorithm, that applied on a set that has one or more
rows MUST return one or more rows. I dont think zero rows can be a proper
sample.
</QUOTE>

I was asked to open a bug, which I did here

Later the issue was Resolved as By Design by Microsoft.

Meanwhile Steve Kass gave me the following T-SQL code to implement Bernoulli Sampling.

select * from Sales.StoreContact
where rand(checksum(newid())%1000000000+CustomerID)< 0.1
-- 0.1 is the desired probability of choosing a row, change as needed.

Eventhough TABLESAMPLE can be confusing with small tables, you will still find it useful in some cases.

MAX Sized datatypes

2005-08-07T13:18:00.000+05:30

You are no longer limited to that 8000 charcter limit for VARCHAR variables. SQL Server 2005 enhances the VARCHAR, NVARCHAR and VARBINARY datatypes by raising the capacity to Approximately 2GB. You can declare the variables with the MAX specifier. Lets quickly try some VARCHAR(MAX) operations.

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE('AA', 5000)
SELECT LEN(@vchTest) as Length

Length
--------
8000

Ofcourse, I expected to see 10000 back as result. I refer the REPLICATE command in BOL which clearly says

"If character_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, character_expression must be explicitly cast to the appropriate large-value data type. "

So I tried

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE(CAST('AA' AS VARCHAR(MAX)), 5000)
SELECT LEN(@vchTest) as Length

Length
--------
10000

Then I tried Concatenating many VARCHAR(n) values and assign it to VARCHAR(MAX) .

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE('AA',4000)+ REPLICATE('AA', 4000)
SELECT LEN(@vchTest) as Length

Length
--------
8000

This time I expected to see 16000 as the result. But I still get 8000 back!

I got help from Christian Wade's blog Which says,

<QUOTE>
The problem is that if you concatenate two varchar(8000) variables, you will be left with a varchar(8000) variable. This is the same issue as populating a float with the division of two integers.
The reason is that varchar(n) variables are physically stored in the same way - irrespective of length. Whereas an varchar(MAX) is effectively a text under the covers and needs to worry about managing pointers to the relevant data page (although this is abstracted away from the developer).
<QUOTE/>

To ensure I just tried

DECLARE @S VARCHAR(9000)

which returned the error.

Msg 131, Level 15, State 3, Line 1
The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

The workaround Christian Wade suggested iscasting the two expressions individually to the large-valued type and then do the assignment.

However, I find it more easy to do an Initialisation and Concantenation with the actual variable, like

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = 'Start'
SET @vchTest = @vchTest + REPLICATE('AbC2',2000) + 'Test'+ REPLICATE('AbC2',2000) + 'End'
SELECT LEN(@vchTest) as Length

Length
---------
16012

Then I tried all String functions on the variable

SELECT LEN(LEFT(@vchTest,9000)) --OK
SELECT RIGHT(@vchTest,3)
SET @vchTest= REPLACE(@vchTest,'End','XXX')
SELECT RIGHT(@vchTest,3)
SET @vchTest = REVERSE(@vchTest)
SELECT RIGHT(@vchTest,3)
SET @vchTest = REVERSE(@vchTest)
SET @vchTest = SPACE(9000) + @vchTest
SELECT LEN(@vchTest)
SELECT CHARINDEX('Test',@vchTest) --Correct
SET @vchTest = STUFF(@vchTest,24001,4,'SQL')
SELECT CHARINDEX('SQL',@vchTest) --Correct
SELECT SUBSTRING(@vchTest,32000,3)
SET @vchTest = LOWER(UPPER(@vchTest))
SELECT LEN(@vchTest)
SET @vchTest = LTRIM(@vchTest)
SELECT LEN(@vchTest)

All seems to work fine.

The text, ntext and image data types will be deprecated. The varchar(max), nvarchar(max) and varbinary(max) data types are far superior than these old datatypes, because you dont have to use special statements like WRITETEXT and UPDATE text. You can use the standard INSERT and UPDATE statements. . For more information, see the topic Using Large Value Data Types in Books Online.

New APPLY Operator

2005-08-06T19:55:00.000+05:30

The new APPLY Operator lets you reference one or more columns from the LEFT table in the right Derived table or table valued function. There are to variations of the Operator, CROSS APPLY and OUTER APPLY. You can use CROSS APPLY like an INNER JOIN except that you dont have to specify any JOIN condition and you can reference the left table columns in the right hand side of the Operator. OUTER APPLY is like LEFT JOIN, that all rows from the left table is included in the result even when there is no match.

The main advantage of the CROSS APPLY operator is that it lets you pass values from the columns of the joined table to a table valued UDF, which was not possible in previous versions of SQL Server.

Suppose you have the following table structure.

CREATE TABLE #Client(ClientId int, ClientName VARCHAR(35))
CREATE TABLE #Account(AccountId int, ClientId int, AccountValue numeric(18,2))

INSERT INTO #Client VALUES(1, 'Anil')
INSERT INTO #Client VALUES(2, 'Robin')

INSERT INTO #Account VALUES(1,1,100)
INSERT INTO #Account VALUES(2,1,200)
INSERT INTO #Account VALUES(3,1,300)
INSERT INTO #Account VALUES(4,2,100)
INSERT INTO #Account VALUES(5,2,400)
INSERT INTO #Account VALUES(6,2,500)

And you were asked to get the TOP 2 Accounts based on AccountValue of each client.

Is SQL Server 2000 you can achieve it by writing a query like,

SELECT C.ClientName, A.AccountValue
FROM #Client C
INNER JOIN #Account A
ON C.ClientId=A.ClientId
AND A.AccountID IN(
SELECT TOP 2 AccountId
FROM #Account D
WHERE D.ClientID=A.ClientId
ORDER BY D.AccountValue DESC)

Which is ofcourse costly. See how CROSS APPLY makes life easy for you.

SELECT C.ClientName, A.AccountValue
FROM #Client C
CROSS APPLY
(SELECT TOP 2 AccountId, AccountValue
FROM #Account D
WHERE D.ClientID=C.ClientId
ORDER BY D.AccountValue DESC)A

Here is the result of the above two queries.

ClientName	AccountValue
Anil	300.00
Anil	200.00
Robin	500.00
Robin	400.00

To see OUTER APPLY at work, Lets add a client without any accounts and try the above query with OUTER APPLY

INSERT INTO #Client VALUES(3, 'Roji')

SELECT C.ClientName, A.AccountValue
FROM #Client C
OUTER APPLY
(SELECT TOP 2 AccountId, AccountValue
FROM #Account D
WHERE D.ClientID=C.ClientId
ORDER BY D.AccountValue DESC)A

And here is the result.

ClientName	AccountValue
Anil	300.00
Anil	200.00
Robin	500.00
Robin	400.00
Roji	NULL

You feel the real power of OUTER/CROSS APPLY When you use them with the Table Values UDFs. There is a UDF named dbo.ufnGetContactInformation in the new AdventureWorks Sample database, which accepts a ContactID as the input and return all contact details as a table. Lets try using that function to get the contact details of all the contacts in the Sales.ContactCreditCard table.

SELECT B.FirstName, B.LastName, B.JobTitle, B.ContactType
FROM Sales.ContactCreditCard A
CROSS APPLY dbo.ufnGetContactInformation(A.ContactId) B

And here is the (abridged) result.

FirstName	LastName	JobTitle	ContactType
Catherine	Abel	Owner	Store Contact
Kim	Abercrombie	Owner	Store Contact
Humberto	Acevedo	Owner	Store Contact
Pilar	Ackerman	Owner	Store Contact

But thats just an introduction. I had seen a lot of smart SQL using CROSS APPLY in NG posting recently. So stay tuned.

DML With Output

2005-08-04T14:14:00.000+05:30

SQL Server 2005 introduces a new feature for retrieving rows affected by an INSERT, UPDATE or Delete statement. It is a common requirement to get the affected rows back for further processing, auditing or simply return them back to the client.

Those who had worked with triggers are familiar with the special inserted and deleted tables. The new OUTPUT clause also work in the same manner. You can use the OUTPUT keyword to return information about the results of a Transact-SQL statement into a table variable, temporary table, permanent table or just return it back to the client.

Using the OUTPUT Clause with INSERT

CREATE Table #OutputTest(someid int IDENTITY(1,1), sometext varchar(100))
--Table variable to collect changes

DECLARE @InsertDetails TABLE(someid int,sometext varchar(100))
INSERT INTO #OutputTest(sometext)
OUTPUT Inserted.SomeId, Inserted.someText INTO @InsertDetails
VALUES('Some Text goes Here')

SELECT * FROM @InsertDetails

Here is the result.
(1 row(s) affected)
someid sometext
----------- ----------------------------------
1 Some Text goes Here

(1 row(s) affected)

Note that we are retriving the just inserted IDENTITY value also.

Now lets try to insert a batch of rows.

--Table variable to collect changes
DECLARE @InsertDetails TABLE(someid int,sometext varchar(100))

--Inserting a batch
INSERT INTO #OutputTest(sometext)
OUTPUT Inserted.SomeId, Inserted.someText INTO @InsertDetails
SELECT 'Test 1' UNION
SELECT 'Test 2' UNION
SELECT 'Test 3'

SELECT * FROM @InsertDetails

And here is the OUTPUT.
(3 row(s) affected)
someid sometext
----------- -----------------------------------------------------------------
1 Test 1
2 Test 2
3 Test 3

(3 row(s) affected)

Using the OUTPUT Clause with UPDATE

DECLARE @UpdateDetails TABLE(id int, prevtext VARCHAR(100), newText VARCHAR(100))

UPDATE #OutputTest
SET sometext = 'XYZ'
OUTPUT deleted.someid, deleted.sometext,inserted.sometext INTO @UpdateDetails
WHERE SomeID < 3

SELECT * FROM @UpdateDetails

Here is the output.

(2 row(s) affected)
id prevtext newText
----------- ---------- ----------
1 Test 1 XYZ
2 Test 2 XYZ

(2 row(s) affected)

Note that here we are capturing both the current and previous values of the affected column.

Using the OUTPUT Clause with DELETE

DECLARE @DeleteDetails TABLE(id int, DeletedBy sysname)
DELETE FROM #OutputTest
OUTPUT deleted.someid, SUSER_NAME() INTO @DeleteDetails
WHERE SomeID=3

SELECT * FROM @DeleteDetails

Here is the result

(1 row(s) affected)
id DeletedBy
----------- --------------------------------------------------------------------------------------------------------------------------------
3 DOMAIN\rojipt

(1 row(s) affected)

I believe the DML OUTPUTwill be one of the most favourite feature of T-SQL developers.

Common (TOP 10?) T-SQL Programming mistakes

2005-08-03T16:24:00.000+05:30

1. TOP Without ORDER BY
SQL Server doesn’t guarantee the order of records returned, if an explicit ORDER BY clause is not specified. Records are not returned in the order they were created. Most of the times records are returned in the order of the clustered index, but not necessarily always.
Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Add a clustered index on ClientName
--CREATE CLUSTERED INDEX idx1 ON #Client(ClientName)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

SELECT TOP 1 * FROM #Client
--ORDER BY ClientID

DROP TABLE #Client

2. TOP Without TIES
If you are expecting TIES in the result and you want all tied records, (like in the case of finding rank) you should use WITH TIES clause with the SELECT statement.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 30000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

SELECT TOP 1 * FROM #Client
ORDER BY AnnualIncome DESC

DROP TABLE #Client

3. Not Using an Alias

Consider the following query.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(3, 2000)

SELECT * FROM #Account
WHERE AccountId IN(SELECT AccountId FROM #Client)

DROP TABLE #Client
DROP TABLE #Account

Obviously there is no column called AccountId in the #Client table. Probably you are expecting an error, but the query returns all the records from the #Account table. What happened behind the scenes is that, SQL Server looks for the accountId column in the #Client table and when it fails to find the column there, it looks for the column in the outer table. This behaviour is referred as fancy scoping.

4. Aggreagation And JOINS

Be careful when doing aggregation on joined resultset. Chances are there that you may getting the wrong result. Consider the following example.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(3, 2000)

SELECT C.ClientId, C.ClientName,SUM(C.AnnualIncome)
FROM #Client C
INNER JOIN #Account A
ON C.ClientId = A.ClientId
GROUP BY C.ClientId, C.ClientName
ORDER BY C.ClientId

DROP TABLE #Client
DROP TABLE #Account

5. NOT IN AND NULL
If you are using the NOT IN Opereator with a subquery and the subquery contains any NULL values, the subquery will return NULL!. This can be dangerous, and this is not the case if you use IN.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(NULL, 2000)

SELECT * FROM #Client
WHERE ClientID NOT IN(SELECT ClientId FROM #Account)

DROP TABLE #Client
DROP TABLE #Account

6. Aggregation And NULL

If any of the columns you are apllying aggregate functions contains NULL , SQL Server will return a warning, “Warning: Null value is eliminated by an aggregate or other SET operation.”. If you are accessing the recordset using ADO, you might face problems. Either use ISNULL() function or SET ANSI_WARNINGS OFF. Setting ANSI_WARNINGS inside an SP will cause it to recompile everytime.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 30000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', NULL)

SELECT SUM(AnnualIncome) FROM #Client

DROP TABLE #Client

7. OUTER JOIN AND WHERE Condition

Consider the following query.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)

--To find Clients who has an annualIncome > 10000
SELECT C.ClientName
FROM #Client C
LEFT JOIN #Account A
ON C.ClientId = A.ClientId
AND C.AnnualIncome > 10000

DROP TABLE #Client
DROP TABLE #Account

This query returns incorrect result. In case of INNER JOINs putting the filter condition on the WHERE clause or ON Clause has the same effect. But in case of OUTER JOINS, it’s a different story. You specify the JOIN Criteria on the ON clause and filter criteria on the WHERE clause. The condition ON the join criteria doesn’t have an impact on the number of rows returned.

A Select statement works in the following way.

SELECT
JOIN
WHERE
GROUP BY
HAVING
DISTINCT
ORDER BY
TOP

(See this link for a detailed description by Joe CELKO)

8. Concatenation And NULL

If you are concatenating a field which has a NULL value, the result will be NULL. To avoid this either use ISNULL() function or SET CONCAT_NULL_YIELDS_NULL OFF

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

-- Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', NULL)

SELECT * FROM #Client

SELECT ClientName + ' - '+ CONVERT(VARCHAR,AnnualIncome) AS [ClientInfo]
FROM #Client

DROP TABLE #Client

9. NULL valued Parameters

If the parameter you are passing has a NULL value and even if there is matching records for NULL, the query wont return any rows back.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(NULL, 2000)

DECLARE @intClientId int
SET @intClientId = NULL

SELECT * FROM #Account
WHERE ClientId = @intClientId

DROP TABLE #Client
DROP TABLE #Account

10. Error Handling

consider the following example.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

-- Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 3000)

--SELECT * FROM #Client

DECLARE @intVar int
SET @intVar = 0
SELECT AnnualIncome/@intVar AS Something
FROM #Client

IF @@ERROR <> 0
SELECT @@ERROR
ELSE
SELECT @@ROWCOUNT

DROP TABLE #Client

In both the cases, the Error handling section will not work as expected, because both @@ERROR and @@ROWCOUNT return the status of the last statement executed.

SQL Server 2005 Notes

2005-07-31T14:38:00.000+05:30

TOP Enhancements
Structured error Handling
Except And Intersect
~~Where~~ How many Times you want to GO Today..!
SOME, ANY, ALL are NOT new Features
AdventureWorks OLTP Database
Ranking and Windowing Functions
DML With OUTPUT
New APPLY Operator
MAX Sized datatypes
Sampling Using TABLESAMPLE

SQL Articles

2005-07-31T14:00:00.000+05:30

BUG: Combination of GROUP BY with HAVING Clause and LEFT OUTER JOIN with Derived Table With LEFT Function Produces Incorrect Result

Named Constraints on Temporary tables

Common (TOP 10?) T-SQL Programming mistakes

Path Enumeration Using Prime Number Products

Difference between Table Variable and Temporary Table

Difference Between SET and SELECT

Let Us Count them!

Differences between COALESCE and ISNULL

Fancy Scoping

Ranking and Windowing Functions

2005-07-31T12:59:00.000+05:30

SQL Server 2005 ships with four functions exclusively for ranking and windowing operations. These functions will make the life of tthe T-SQL programmer easy. The following are the four functions.

1. ROW_NUMBER()

This function allows you to provide a pseudo column containing sequential integer values to result rows of a query, which makes many tasks like paging easy. Before 2005, to get the same result, you had to write complex queries like,

use pubs
GO

Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc

Lets see how easy its in 2005

CREATE TABLE #Students(StudentName VARCHAR(10), subject VARCHAR(10), Mark int)
INSERT INTO #Students VALUES('Jaison','.Net',90)
INSERT INTO #Students VALUES('Senthil','.Net',70)
INSERT INTO #Students VALUES('Joe','.Net',50)
INSERT INTO #Students VALUES('Jaison','SQL',70)
INSERT INTO #Students VALUES('Senthil','SQL',90)
INSERT INTO #Students VALUES('Joe','SQL',80)

--SELECT * FROM #Students

SELECT ROW_NUMBER() OVER (ORDER BY Mark DESC) as rownum, * FROM #Students

And here is the output.
rownum StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil SQL 90
3 Joe SQL 80
4 Senthil .Net 70
5 Jaison SQL 70
6 Joe .Net 50

Note that in the above example we have 2 ties, for marks 90 and 70. The ROW_NUMBER() break ties and assign a unique integer number to each row. If the column specified in the ORDER BY clause is not unique, then the ROW_NUMBER() function is not deterministic. That is the assignment of sequential values for tied rows may vary from execution to execution.

2. RANK()
The Rank function is similiar to ROW_NUMBER function in the sense that Rank() also produces a ranking column. But the difference is that the Rank function does not break ties. It assigns the same value to the tied rows. Lets see Rank() in action.

SELECT RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students

rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
3 Joe SQL 80
3 Ann SQL 80
5 Senthil .Net 70
5 Jaison SQL 70
7 Joe .Net 50

Noe that the result has ties and gaps.

3. DENSE_RANK()
The DENSE_RANK() function is same as RANK() except that it doesnt leave gaps.

SELECT DENSE_RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students

rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
3 Senthil .Net 70
3 Jaison SQL 70
4 Joe .Net 50

4.NTILE()

NTile(n) will evenly divide all the results into approximately even pieces based on the input argument, and assign each piece by the same number in the result set. For eg, if you want to assign grades to students based on their mark you can use NTILE function like

SELECT NTILE(2) OVER (ORDER BY Mark DESC) as grade, * FROM #Students

grade StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
1 Joe SQL 80
1 Ann SQL 80
2 Senthil .Net 70
2 Jaison SQL 70
2 Joe .Net 50

PARTITION BY
Ranking functions can be used for windowing with the PARTITION BY Clause. PARTITION BY clause lets you apply ranking functions for subgroups. For eg. if you want to get the ranks in the Subject Level, you canget that by using the PARTITION BY clause along with the ORDER BY Clause.

SELECT RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank, * FROM #Students

Here is the result.

rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil .Net 70
3 Joe .Net 50
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
4 Jaison SQL 70

You can apply the PARTITION BY Clause on all the ranking functions.

SELECT
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY Mark DESC) as rownum,
RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as dese_rank,
NTILE(3) OVER (PARTITION BY subject ORDER BY Mark DESC) as grade, * FROM #Students

Here is the result.

rownum rank dese_rank grade StudentName subject Mark
-------------------- -------------------- -------------------- -------------------- ----------- ---------- -----------
1 1 1 1 Jaison .Net 90
2 2 2 2 Senthil .Net 70
3 3 3 3 Joe .Net 50
1 1 1 1 Senthil SQL 90
2 2 2 1 Joe SQL 80
3 2 2 2 Ann SQL 80
4 4 3 3 Jaison SQL 70

For more info on ranking functions, see these articles(1, 2) by SQL MVP Itzik Ben Gan.

AdventureWorks

2005-07-30T13:24:00.000+05:30

The samples used in these notes use the new AdventureWorks database. You can download the adventureworks databas diagram from this link.

SOME, ANY, ALL are NOT new Features

2005-07-29T15:34:00.000+05:30

This more of a Whats not new rather than Whats NEW.

I had seen many authentic links like this and even the book Introducing Microsoft SQL Server(TM) 2005 for Developers saying that SOME, ANY, ALL are new T-SQL extensions in SQL Server 2005. But the fact is that they are present in SQL Server 200 also.

See the below code samples, executed from Query Analyser, using the pubs database, in SQL Server 2000

USE PUBS
GO

SELECT * FROM Titles WHERE Type = 'business'
AND price > SOME(SELECT Price FROM Titles WHERE Type = 'psychology')

SELECT * FROM Titles WHERE Type = 'business'
AND price > ANY(SELECT Price FROM Titles WHERE Type = 'psychology')

SELECT * FROM Titles WHERE Type = 'business'
AND price > ALL(SELECT Price FROM Titles WHERE Type = 'psychology')

How Many Times you want To GO Today

2005-07-28T15:16:00.000+05:30

~~Where~~ How Many Times you want To GO Today !

This is about a really INTERESTING SQL server 2005 feature.

You can execute a transact sql batch any number of times just by Giving the number with GO.

See Go in action here

PRINT 'Hai.. How many times you expect to see this message..?'
GO 10

And here is the output.

Beginning execution loop
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Batch execution completed 10 times.

Thats COOL right?

TOP Enhancements

2005-07-27T17:43:00.000+05:30

Okay, Lets start at the Top. (Afterall this blog is named TopOneWithTies.) The Good news is that, in SQL Server 2005, you can specify a variable as the argument for the Top clause. Wait, not just a variable, you can specify a sub query or a UDF as an argument for the TOP clause.

Also you can specify the TOP clause NOT just with the select, but also with INSERT, DELETE and UPDATE.

Sounds great right!. Lets see TOP in action.

USE AdventureWorks
GO
DECLARE @n int
SET @n = 5
SELECT TOP(@n) * FROM Sales.SalesOrderHeader

Now lets try to use a User Defined function as the input for TOP.

CREATE FUNCTION dbo.MyFunc()
RETURNS INT
AS
BEGIN
RETURN 3
END
GO

SELECT TOP(dbo.MyFunc()) * FROM Sales.SalesOrderHeader

And a subquery,

SELECT TOP ((SELECT COUNT(*) FROM Production.Product)) * FROM Sales.SalesOrderHeader

Now lets try to run some INSERT, UPDATE, DELTE Statements with the TOP Clause.

CREATE TABLE #TopSales(SalesOrderId int, OrderDate datetime)

INSERT TOP (5) INTO #TopSales
SELECT SalesOrderId, OrderDate
FROM Sales.SalesOrderHeader
ORDER By TotalDue DESC

SELECT * FROM #TopSales

UPDATE TOP (2) #TopSales
SET OrderDate = OrderDate +1

SELECT * FROM #TopSales

DELETE TOP (2) FROM #TopSales

SELECT * FROM #TopSales

Note that in case of UPDATE and DELETE you cannot specify an ORDER BY Clause (It seems).
So use this option carefully (for eg. Splitting the update of large no of rows into batches) as you cannot predict which rows will get affected. (I may have to do some further research on this)

EXCEPT AND INTERSECT

2005-07-27T15:06:00.000+05:30

It was a common question in SQL server newsgroups that how one can implement EXCEPT and INTERSECT set operations in T-SQL. The answer is, When NULLs are not involved, you can achieve the INTERSECT effect by using INNER JOIN and except effect using NOT EXISTS or LEFT JOIN with a NULL check.

But When NULLS are present in data, the above methods will fail because, INTERSECT treats NULLs as equal for matching rows. This NG thread by SQL MVP David Portas discuss some some full equivalents. Also, SQL MVP Itzik Ben Gan has an excellent article on Mimicking INTERSECT and EXCEPT in T-SQL

But those are the days when EXCEPT and INTERSECT were not part of the T-SQL. SQL Sever 2005 adds both these set operators to the language, to make it more powerful.

See this link for for a list of Relational Operators

INTERSECT

INTERSECT is one of the the basic relational operators. INTERSECT works on two sets. It returns rows exists in both sets eliminating duplicates.

INTERSECT ALL also returns rows existis in both the inputs, but if a row repeat n times in the first input and m times in the second input, then the output will contain n or m number of the same row, whichever is less.
Note that both the set should be UNION compatible, that is the number of columns should be same etc..

The ALL version of the INTERSECT is NOT Currently supported in SQL Server 2005.

Enough theory, its time to see the new INTERSECT Operator in action

CREATE TABLE #Skills(Programmer VARCHAR(20), Skill CHAR(3))

INSERT INTO #Skills VALUES('Jaison','NET')
INSERT INTO #Skills VALUES('Roji','NET')
INSERT INTO #Skills VALUES('Roji','SQL')
INSERT INTO #Skills VALUES('Srinivas','SQL')

SELECT * FROM #Skills

Lets get the list of Programmers who has both SQL server and .Net skills

SELECT Programmer FROM #Skills WHERE Skill='NET'
INTERSECT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output

Programmer
--------------------
Roji

(1 row(s) affected)

EXCEPT

EXCEPT, also called MINUS returns DISTINCT rows from the LEFT input, which is not present in the RIGHT input.
With EXCEPT ALL, if a row appears more times in the LEFT set than the RIGHT set, then the row will appear in the output n number of times, where n is the number of times the row appear in the left set minus the number of times the row appears in the right set. The ALL version of the EXCEPT is NOT Currently supported in SQL Server 2005.

Here is the EXCEPT version of our above query

Lets get the list of programmers who knows .NEt, but doesnt know SQL.

SELECT Programmer FROM #Skills WHERE Skill='NET'
EXCEPT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output.

Programmer
--------------------
Jaison

(1 row(s) affected)

Structured Error handling

2005-07-26T19:34:00.000+05:30

Its time to to forget the @@Error Global variable. SQL Server 2005 Provides you the power of structured Error handling as you do in any .Net Language, Which eliminates the need for checking IF @@ERROR > 0 ...

Now lets just create a table

CREATE TABLE #Student(StudentID int PRIMARY KEY, StudentName VARCHAR(50))

And try the following.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
END CATCH

When you run the above code for the first time you will get the message 'Successfully Inserted'. When you try the code for the second time, You will get the message saying 'Insert failed', which means that our Catch block executed.

You can see that, when a statement within the TRY block fails, control is passed to the first statement within the catch block.

In the catch block you can use the new ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY() and ERROR_STATE() functions to get the details of the error. For eg. you can modify the above code as follows to get the details of the Error.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

TRANSACTIONS and Error Handling
The new XACT_State function will come handy when you are using explicit transactions in the Try block. The XACT_STATE() function returns the transaction state. The possible values are -1,0 and 1.

-1 : Means that a transaction is open, but in a doomed (uncommitable) state. This typically happens when an error occutred, which has a severity level higher than 17.
0 : No transactions are open
1 : a transaction is open and commitable.

The following code shows the usage of XACT_STATE() function.

BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT XACT_STATE()
ROLLBACK TRAN
PRINT XACT_STATE()
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

Note that the error will not be propogated to the application from the try block, unless you explicitly throw the error using RAISERROR, as in the following code segment.

BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName)
VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('Insert Failed',16,1)
END CATCH

For more information see the following MSDN article.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Path Enumeration using Prime Number Products

2005-03-14T12:58:00.000+05:30

Managing Hierarchies in Relational Database Environment is always a challenging task. Over the years many models like Adjacency List Model, Nested Set Model, Nested Interval Model, Path Enumeration Model etc. were proposed and implemented. There are also other popular methods using recursion and user defined function. SQL Server 2005 T-SQL Extensions has special operators for managing recursive queries. Joe CELKO has written a book exclusively on Managing Trees and Hierarchies.

The basic Hierarchical operations in a typical Employee-Manager type of relation, include

1. Add a new Employee
2. Show the whole Hierarchy with levels.
3. Show the superiors of a given employee.
4. Show the subordinates of a given employee.
5. Show the immediate subordinates of a given employee.
6. List all the Leaf Level Employees.
7. Change the manager of an Employee
8. Delete an Employee and all his subordiantes.
9. Delete an Employee.

Here I am trying to propose a simple model which works almost like Path Enumeration model. This model uses a Prime Number table and basic mathematical operations to answer the above questions. Also the Model only uses set based solutions and can be implemented as a self managing system with the help of triggers.

This model works on two basic concepts.

1. There is a unique path to every node in a hierarchy from the root node.
2. The Prime Number that can be a divisor of the Product of a set of Prime Numbers, are only the Prime numbers in the set.

That is, Let product of n prime numbers be Px, then, the factors of Px can only be the Prime Numbers that participated in the multiplication, or the sub products.
So the product cannot be divided by any other Prime Number.

For eg.

P1, P2, P3,...Pn is the list of Prime Numbers.

Let P be the Product.

Then P1 * P2 * P3 * ... * Pn = P

Then the factors of P can be either P1, P2, P3,...Pn Or the sub product of P1, P2, P3,...Pn.

Now let's try to answer the above questions.

The following is the table used in the examples.

CREATE TABLE dbo.EmpHierarchy
(EmpId int PRIMARY KEY,
EmpName VARCHAR(15),
ManagerId int,
HKey bigint UNIQUE,
Path bigint)

GO

The purpose of the first three columns are obvious. The column Hkey is a Unique Prime Number assigned to each employee. The column Path Holds the product of the Hkey values of the current node and nodes above the current node.

The script used for creating and Populating the PrimeNumber table is here.

1. Add a new Employee
For inserting a new node (employee), we will get the least available Prime Number and use it as the Hkey of the node. The Path will be the Product of the Hkey and the Path of the parent node. For the root level entry the path will be the Hkey itself.

Here is the script for the stored procedure dbo.InsertNode

Now lets insert some sample data.

EXEC dbo.InsertNode 1,'Albert', NULL
EXEC dbo.InsertNode 2,'Bert', 1
EXEC dbo.InsertNode 3,'Chuck', 1
EXEC dbo.InsertNode 4,'Dona', 3
EXEC dbo.InsertNode 5,'Eddie', 3
EXEC dbo.InsertNode 6,'Fred', 3

SELECT * FROM EmpHierarchy

Now we have this data in the table.

EmpId	EmpName	ManagerId	HKey	Path
2	Bert	1	3	6
3	Chuck	1	5	10
4	Dona	3	7	70
5	Eddie	3	11	110
6	Fred	3	13	130

2. Show the whole Hierarchy with levels.
The level of a node in a hierarchy depends on the number of levels above it. So the level of a given node can be found by counting the levels above it. Thats the technique used in the stored procedure dbo.PrintHierarchy. The SP also accepts an EmployeeId, to display a partial hierarchy.

EXEC dbo.PrintHierarchy

Here is the output

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Bert	Albert
--	2	Chuck	Albert
---	3	Dona	Chuck
---	3	Eddie	Chuck
---	3	Fred	Chuck

3. Show the superiors of a given employee.
The path of a given employee is the product of the Hkeys of his superiors and his Hkey. As per our rule the path can be divided only by the Prime Numbers participated in it. Based on that finding the superiors logic is failrly easy. You can get the list of the superios of a given employee by

SELECT A.EmpId, A.EmpName
FROM EmpHierarchy A
WHERE [Path of the given Employee] % A.Hkey = 0

I have this Stored Procedure dbo.getSuperiors which accepts an Employee Id and return the list of his superiors.

To get the Superiors of 'Fred', execute

EXEC dbo. getSuperiors 6

Here is the output

EmpId	Employee	Manager
6	Fred	Chuck
3	Chuck	Albert
1	Albert	NULL

4. Show the subordinates of a given employee.
The Hkey of a given employee will be a factor of the Path of all his subordinates and only his subordinates.
So to get the list of the subordinates of an employee, you can run a query like

SELECT A.EmpId, A.EmpName AS Employee
FROM EmpHierarchy A
WHERE A.Path % [HKey of the given Employee] = 0

Here is the stored procedure dbo. getSubordinates for doing the same.

Now to get the list of subordiantes of 'Chuck', execute

EXEC dbo. getSubordinates 3

Here is the output

EmpId	Employee	Manager
3	Chuck	Albert
4	Dona	Chuck
5	Eddie	Chuck
6	Fred	Chuck

5. Show the immediate subordinates of a given employee.
The path of the immediate subordiantes of a given employee will be the product of the path of the manager and the HKey of the subordinate.
So one can find the immediate subordinates of a given employee by writing a query like

SELECT EmpId, EmpName
FROM EmpHierarchy
WHERE Path = HKey * [Path of the Given Employee]

For eg, to get All the immediate subordinates of Albert, the query will be

SELECT EmpId, EmpName
FROM EmpHierarchy
WHERE Path = HKey *2 --Path of Albert

Here is the output

EmpId	EmpName
2	Bert
3	Chuck

6. List all the Leaf Level Employees.
Leaf level employees are those who dont have any subordinates. So finding them is a trivial task.
The following query gives you the list of leaf level employees.

SELECT EmpId, EmpName
FROM EmpHierarchy A
WHERE NOT Exists(SELECT 1 FROM EmpHierarchy T
WHERE T.Path % A.Hkey = 0
AND T.Path > A.Path)

And here is the output.

EmpId	EmpName
2	Bert
4	Dona
5	Eddie
6	Fred

7. Change the manager of an Employee
Updating can be of two types, promotion and demotion. When updating an Employees manager, you need to update the rows of the given employee and all his subordinates Path. Also you need to perform some validation to avoid cyclic reference. Here is the stored Procedure dbo.UpdateNode to update a given node.

So now to change Fred's Manager to Bert from Chuck, you can Execute

EXEC dbo.UpdateNode EmployeeId, NewManagerId

EXEC dbo.UpdateNode 6, 2

After the update, the hierarchy will look like

EXEC dbo.PrintHierarchy

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Bert	Albert
--	2	Chuck	Albert
---	3	Dona	Chuck
---	3	Eddie	Chuck
---	3	Fred	Bert

Now, lets try a promotion. Eddie directly reports to Albert instead of Chuck

EXEC dbo.UpdateNode 5, 1

This is how the hierarchy will look like now.

EXEC dbo.PrintHierarchy

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Bert	Albert
--	2	Chuck	Albert
--	2	Eddie	Albert
---	3	Fred	Bert
---	3	Dona	Chuck

Finally lets demote chuck one level down the hierarchy. Instead of reporting to Albert, he will now report to Bert

EXEC dbo.UpdateNode 3, 2

EXEC dbo.PrintHierarchy

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Bert	Albert
--	2	Eddie	Albert
---	3	Fred	Bert
---	3	Chuck	Bert
----	4	Dona	Chuck

8. Delete an Employee and all his subordiantes.
Deleting a subtree is fairly easy. we can use the same logic of finding subordinates and apply that in the where clause.
Here is the stored procedure dbo.deleteSubTree

Now to fire the whole team of Chuck, you can execute

EXEC dbo.deleteSubTree 3 --EmployeeId of chuck

After the deletion, the Hierarchy will look like

EXEC dbo.PrintHierarchy

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Bert	Albert
--	2	Eddie	Albert
---	3	Fred	Bert

9. Delete an Employee.
This can be more tricky, because when you delete an Employee, If he has subordinates, the hierarchy will be broken. So all the subordinates of the deleted employee should report to the Manager of the deleted employee. Here is the Stored Procedure dbo.deleteNode, which takes care of it.

So Now lets try deleting Bert.

EXEC dbo.deleteNode 2

After the deletion, the hierarchy will look like

Hierarchy	Level	Employee	Manager
-	1	Albert	NULL
--	2	Eddie	Albert
--	2	Fred	Albert

And thats it :)

These are the pro's and con's of the model from my point of view.

Pros
1. Use only standard SQL constructs.
2. Use only set based solutions.
3. Can be implemented as self manageable using triggers.
4. Gives better performance because it uses Numeric operations instead of string operations.

Cons
1. For larger Hierarchies, you may encounter out of range errors because the Path value grows in an exponential rate.
Still you can replace the bigint with a larger datatype like numeric and replace the % operator with division operator and overcome the issue.

AS I mentioned earlier, this model is still in its evolving face. So as always, your feedback is highly appreciated.

2005-03-14T12:57:00.000+05:30

EmpHierarchy Table Script
This is the table script used in the examples for Path enumeration using Prime Number Products.

CREATE TABLE dbo.EmpHierarchy(EmpId int PRIMARY KEY,
EmpName VARCHAR(50),
ManagerId int,
HKey bigint UNIQUE,
Path bigint)

2005-03-14T12:56:00.000+05:30

Prime Numbers table and Insert Script

Here is the script to create the Prime Numbers table and populate the first 1000 Prime numbers.

CREATE TABLE dbo.PrimeNumbers(Number bigint PRIMARY KEY)
GO

--Insert First 1000 Prime Numbers

DECLARE @num bigint
DECLARE @cnt int
DECLARE @isPrime bit
DECLARE @nsqrt int
DECLARE @divisor int

INSERT INTO PrimeNumbers VALUES(2) -- First Prime Number

SET @num = 3 --Next Prime Number
SET @cnt = 1 --One row already inserted.

WHILE @cnt <>
BEGIN
SET @isPrime = 1 -- Be Optimistic ;)
SET @nsqrt = FLOOR(SQRT(@num))
SET @divisor = 3
WHILE @divisor <= @nsqrt
BEGIN
IF @num % @divisor = 0
BEGIN
SET @isPrime = 0
BREAK
END

SET @divisor = @divisor + 2
END

IF @isPrime = 1
BEGIN
SET @cnt = @cnt + 1
INSERT INTO PrimeNumbers VALUES(@num)
END

SET @num = @num+2
END

--SELECT * FROM PrimeNumbers

2005-03-14T12:55:00.000+05:30

Insert Node
Stored Procedure for Inserting a new Employee

/*
Inserts a new Employee into the table
*/
CREATE PROC dbo.InsertNode
(@empid int,
@empName VARCHAR(50),
@MgrId int)
AS

BEGIN
DECLARE @Hkey int
DECLARE @PathVal Numeric(32,0)

BEGIN TRAN

--Get the next Prime Number
SELECT @Hkey = Min(Number) FROM PrimeNumbers
WHERE Number > (SELECT ISNULL(Max(Hkey),0) FROM EmpHierarchy)

IF @MgrID IS NULL OR @MgrId = @EmpId --Root Node
SET @PathVal = @Hkey
ELSE --Get Managers Path and Calculate the path of the new node
SELECT @PathVal = Path * @Hkey FROM EmpHierarchy
WHERE EmpId = @MgrId

INSERT INTO EmpHierarchy
VALUES(@empid, @empName, @MgrId,@Hkey, @PathVal)

IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

END

2005-03-14T12:54:00.000+05:30

Print Hierarchy

/*
Print Hierarchy with Graphical representation of the Level
*/

CREATE PROC dbo.PrintHierarchy
@EmpID as bigint = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE @PathVal as bigint
SELECT @PathVal = Path FROM EmpHierarchy
WHERE EmpId = @EmpID

SELECT REPLICATE('-',(SELECT COUNT(*) FROM EmpHierarchy B WHERE A.Path % B.Path = 0)) As Hierarchy,
(SELECT COUNT(*) FROM EmpHierarchy B WHERE A.Path % B.Path = 0) as [Level],
A.EmpName AS Employee, C.EmpName As Manager
FROM EmpHierarchy A
LEFT JOIN EmpHierarchy C
ON A.ManagerId = C.EmpId
WHERE A.Path % @PathVal = 0
ORDER By [Level]
END

2005-03-14T12:53:00.000+05:30

List of Superiors

/*
Get the List of Superiors and their managers
*/

CREATE PROC dbo.getSuperiors
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Path bigint

SELECT @Path = Path
FROM EmpHierarchy
WHERE EmpId = @EmpId

SELECT A.EmpId, A.EmpName AS Employee, C.EmpName As Manager
FROM EmpHierarchy A
LEFT JOIN EmpHierarchy C
ON A.ManagerId = C.EmpId
WHERE @Path % A.Hkey = 0
ORDER By A.Path DESC

END

2005-03-14T12:52:00.000+05:30

List of Subordinates

/*
Get the List of Subordinates and their managers
*/

CREATE PROC dbo.getSubordinates
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Hkey bigint

SELECT @HKey = Hkey
FROM EmpHierarchy
WHERE EmpId = @EmpId

SELECT A.EmpId, A.EmpName AS Employee, C.EmpName As Manager
FROM EmpHierarchy A
LEFT JOIN EmpHierarchy C
ON A.ManagerId = C.EmpId
WHERE A.Path % @HKey = 0
ORDER By A.Path

END

GO

2005-03-14T12:51:00.000+05:30

Update Node

/*
Update an Employee with a new Manager. Also adjusts the path of the
Employee and his Subordinates
*/

CREATE PROC dbo.UpdateNode
(@Empid bigint,
@NewMgrId bigint)
AS

BEGIN
SET NOCOUNT ON

DECLARE @CurrentMgrPath bigint
DECLARE @NewMgrPath bigint
DECLARE @CurrentMgr bigint
DECLARE @CurrentPath bigint
DECLARE @NewPath bigint

--Employee Exists?
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @empid)
RAISERROR('Employee Id %d Does not exists',16,1,@Empid)

--Manager Exists
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId)
RAISERROR('Manager %d Does not exists',16,1,@NewMgrId)

--Get the required values to variables
SELECT @CurrentMgr = ManagerId,
@CurrentPath = Path
FROM EmpHierarchy
WHERE EmpId = @empid

SELECT @CurrentMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @CurrentMgr

SELECT @NewMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @NewMgrId

--Cyclic Reference
IF EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId
AND Path % @CurrentPath = 0)
RAISERROR('Error: Cyclic Reference',16,1)

--Seems OK. Update.
BEGIN TRAN
--Update Manager
UPDATE EmpHierarchy
SET ManagerId = @NewMgrId
WHERE EmpId = @Empid

IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END

--Update Path
UPDATE EmpHierarchy
SET Path = ((Path / @CurrentMgrPath)*@NewMgrPath)
WHERE Path % @CurrentPath = 0

IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END
ELSE
BEGIN
COMMIT
RETURN 1
END
END

GO

2005-03-14T12:50:00.000+05:30

Delete a SubTree

/*
Delete a subtree from the hierarchy
*/

CREATE PROC dbo.deleteSubTree
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Hkey bigint

SELECT @Hkey = Hkey
FROM EmpHierarchy
WHERE EmpId = @EmpId

DELETE FROM EmpHierarchy
WHERE Path % @Hkey = 0

END

2005-03-14T12:49:00.000+05:30

Delete a Single Node

/*
Delete a given node. All Employees reporting to
the deleted employee will now report to the manager of the deleted employee

*/

ALTER PROC dbo.deleteNode
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Hkey bigint
DECLARE @MgrId bigint
DECLARE @EmpPath bigint
DECLARE @MgrPath bigint

SELECT @Hkey = Hkey,
@MgrId = ManagerId,
@EmpPath = Path
FROM EmpHierarchy
WHERE EmpId = @EmpId

SELECT @MgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @MgrId

--Delete Employee
DELETE FROM EmpHierarchy
WHERE EmpId = @EmpId

--Update ManagerID of Immediate Subordiantes
UPDATE EmpHierarchy
SET ManagerId = @MgrId
WHERE Path / Hkey = @EmpPath

--Connect subordinates to the immediate superior
UPDATE EmpHierarchy
SET Path = ((Path/@EmpPath)*@MgrPath)
WHERE PAth % @Hkey = 0

END

GO

Difference between Table Variable and Temporary Table

2004-08-24T03:06:00.000+05:30

Table variables were introduced in SQL Server 2000. Many times they are presented as a replacement for temporary tables. There is a lot of arguement about the performance advantages of one over the other. It is generally believed that table variables give better performance than temporary tables. But there are so many cases reported where the usage of table variable slow down the performance, especially when the number of rows involved are high. So now the general recommendation is that use a table variable only if there is a performance gain over the temp table.

Lets see what are the basic difference between them.

1. Contrary to the popular belief that table variables exists in memory, KB Article 305977 states that "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)."

The implementation of table variables is from a logging and tempdb usage very similar to temporary tables.

2. You can create indexes on a temp table. Table variables does not support explicit index creation. You can create indexes on table variables using the PRIMARY KEY and Unique constraints.

3. User defined functions are not permitted to do DDL operations. But UDFs can CREATE, INSERT, UPDATE, DELETE and RETURN table variables. (But you cannot use user-defined data types in the table variable you declare for the return value.)

4. Table variables are always local to its scope while Temporary tables can be global or local. So table variables cannot be accessed from calling procedures in case of nested sps.

5. Stored Procedure Recompilations. Using table variables can reduce the number of stored procedure recompilations. thats one place where table variables excel over temp tables.

6. Table variables do not maintain statistics like temporary tables. This might lead to bad execution plan and slow performance when dealing with higher number of rows.

7. Temp tables can be ALTERed. But you cannot change the definition of a table variable after the initial DECLARE statement.

8. Parallelism is not possible when you insert into a table variable.

9. You cannot do

INSERT INTO @tablevar
EXEC yourspname

But

INSERT INTO #temptable
EXEC yourspname

is possible.

10. You cannot do

SELECT * INTO @tableVar FROM source

But

SELECT * INTO #temptable FROM source

is possible.

11. You cannot truncate a table variable.

DECLARE @x table(a int)
INSERT INTO @x VALUES(1)
TRUNCATE TABLE @x

produces a syntax Error.

But the same is possible with temp table.

DECLARE #x table(a int)
INSERT INTO #x VALUES(1)
TRUNCATE TABLE #x

12. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources. That is table variables are automatically committed, where as temporary tables can be included in a transaction. This can make a performance difference.

13. Join Syntax. You MUST use an alias when referring a table variable in a JOIN. The following query will throw an error.

USE PUBS
GO
DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x
ON Titles.title_id = @x.someid

It should be written as

DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x a
ON Titles.title_id = a.someid

Temporary tables doesnt have such limitations.

14. Temporary tables can be explicitly destroyed using DROP statement.

15. You cannot assign a table variable to another as in the case of other variables.

16. CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.

17. "Display Estimated Execution Plan" option in query analyser does'nt work with T-SQL code that has a reference to Temporary tables.

18. More, anyone...?

Let Us Count them!

2004-08-18T21:26:00.000+05:30

Lets have a quick look on the differences between using COUNT(*) and COUNT(colname). Also discuss other ways to get the count of rows.

1. COUNT(*) returns the number of total rows. Count(colname) returns the total number of rows with Non NULL value for colname. If the specified column doesnt allow NULLs the result will be same.

eg. (USE PUBS)

SELECT count(*) FROM Titles
-- Returns 18

SELECT count(title_id) FROM Titles
-- Returns 18

SELECT count(royalty) FROM Titles
-- Returns 16

When you are doing Count on a column with NULLs SQL server will give you the warning. "Warning: Null value is eliminated by an aggregate or other SET operation."

As Count(colname) excludes columns with NULL values, the following query

SELECT count(royalty) FROM Titles where royalty IS NULL

will return zero. If you want to get the count of rows with NULL in a specific column, you should use,

SELECT count(*) FROM Titles where royalty IS NULL

which returns two in this case.

2. COUNT(colname) supports the use of ALL/DISTINCT clause (ALL is the default). COUNT(*) doesn't. So to get the UNIQUE Royalty values, you can use the query

SELECT COUNT(DISTINCT royalty) FROM Titles

You can combine these to see the difference.

SELECT COUNT(*), COUNT(royalty), COUNT(DISTINCT royalty) FROM Titles

3. As COUNT(*) returns the total number of records (in each group when GROUP BY is present in the query), like SELECT @@ROWCOUNT, SELECT COUNT(*) is also a valid statement in its own, which always returns 1.

SELECT COUNT(*)
GO

4. Performance. (with due credits to Itzik Ben Gan) If the specified column name does not allow NULLS, there isn't any performance difference between using COUNT(*) or COUNT(colname). The Optimizer will choose the smallest index available to get the number of records.

SELECT COUNT(*) FROM Titles
---------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))

SELECT Count(type) FROM Titles
---------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))

As the type column doesnt allow NULLS, both the counts return the same number and SQL Server uses the smallest index available.

If you issue the COUNT(colname) on a column that allows NULL, the execution plan might be different, as in

SELECT count(notes) FROM Titles
---------------------------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=COUNT_BIG([titles].[notes])))
--Clustered Index Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))

In this case, SQL Server will use a nonclustered index, if one is defined on the specified column. Otherwise a clustered index scan(as in the above case) or a table scan will be performed.

Here you can also notice that SQL Server internally replace the COUNT() function with COUNT_BIG() function. COUNT_BIG works like the COUNT function. The only difference between them is their return values: COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

5. Conditional Count
Using the CASE Expression, you can get count of items conditionally. See the following example.

Use NothWind
Go
SELECT COUNT(*) as ProductCount,
COUNT(CASE WHEN DisContinued = 0 Then 1 END) As Available,
COUNT(CASE WHEN DisContinued = 1 Then 1 END) As Discontinued
FROM Products

6. Getting Count from multiple tables
Here is two methods to get the count of rows from more than one table.

SELECT SUM(cnt) as EmployeesandCustomers FROM
(SELECT COUNT(*) cnt FROM Employees
UNION
SELECT COUNT(*) cnt FROM Customers) T

SELECT (SELECT COUNT(*) cnt FROM Employees)+
(SELECT COUNT(*) cnt FROM Customers) as EmployeesandCustomers

7. Count('AnythingGoesHere')
The parameter for the count function can be a column name, number, literal or an expression. The BOL says that "expression can be of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted."

SELECT COUNT(1) or SELECT COUNT('abc') is equivalent to SELECT COUNT(*)

8. Alternatives.
If you don't require an exact answer, it isn't necessary use a SELECT count(*) query on the rows in a table to get the row count, because it might be a time consuming process to get the count of rows from a table with millions of rows. SQL Server keeps the row count in sysindexes and it can be retrieved there. But it may not always reflect the exact number of records.

See below.

SELECT rows as linecount
FROM sysindexes
WHERE id = OBJECT_ID('Titles') AND indid <> 2

If you have a tip on count, plz post a comment.

Difference Between SET and SELECT

2004-08-02T14:40:00.000+05:30

T-SQL Programmers use both SET and SELECT Statements for variable assignments. Lets see what are the differences.

1. SET is the ANSI Standard for variable assignment. Variable assignment using SELECT is T-SQL Proprietory.

2. SELECT can be used for multiple variable assignment. Using SET you can only assign one variable at a time.

DECLARE @myval1 int
DECLARE @myval2 int

SET @myVal1 = 5
SET @myVal2 = 6

SELECT @myVal1 = 5, @myVal2 = 6

3. Different Behavior when No rows are Returned.
If variables are assigned in the SELECT then the value of the variables will remain unchanged, but thats not the case with SET. See the following example.

DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns Zero

SET @id= (SELECT id FROM sysobjects WHERE name = 'does not exist')
SELECT @id -- Returns NULL

But if the SELECT includes an aggregate function it will NOT retain the initial value.

DECLARE @id INTEGER
SET @id = 0
SELECT @id = Max(id) FROM sysobjects WHERE name = 'does not exist'
SELECT @id -- Returns NULL

4. Different Behavior when Multiple rows are returned by the expression in the right side.

When using SET, it returns the 'Subquery Returned more than one value' Error.

DECLARE @id INTEGER
SET @id = 0

SET @id= (SELECT id FROM sysobjects)
-- Returns Error

But with SELECT it assigns the 'last' returned value to the variable, whic can be anything, if an ORDER BY clause is not specified, as in -

DECLARE @id INTEGER
SET @id = 0
SELECT @id = id FROM sysobjects
SELECT @id

So you have to be careful, to avoid disatsters. Lets take an example.
Suppose you want to assign the orderid of the Last order placed by a given customer, to a variable.
(There might be better way to do that with an aggregate function, but this is just an example)

USE Northwind
GO

DECLARE @OrderId int
SELECT @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC

SELECT @OrderId

Here we have used ORDER BY OrderId ASC. So the last OrderID of the customer will be assigned to the variable @OrderId. But suppose you add TOP 1 to the above query, thinking that you only want one OrderId anyway, then you will be getting only the first order of the customer.

USE Northwind
GO

DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId ASC

SELECT @OrderId

To get the expected result you have to change the ORDER BY clause to DESC.

USE Northwind
GO

DECLARE @OrderId int
SELECT TOP 1 @OrderId = OrderId
FROM Orders
WHERE CustomerId = 'VINET'
ORDER BY OrderId DESC

SELECT @OrderId

5. Looping Behavior of SELECT
Further exploring the above point, we can introduce some intuitive looping behaviour using SELECT. Consider the following example.

CREATE TABLE #tmp (a int)
INSERT INTO #tmp VALUES(1)
INSERT INTO #tmp VALUES(2)
INSERT INTO #tmp VALUES(3)
INSERT INTO #tmp VALUES(4)
INSERT INTO #tmp VALUES(5)

DECLARE @p int
SELECT @p = isnull(@p, 1) * a FROM #tmp
SELECT @p

Here @p become 120, the product of all values of a. We can observe that the assignment is carried out once for each row of in the table #tmp.

The same logic is used in the varchar concatenation technique shown below.

USE PUBS
GO

DECLARE @vchAuthors VARCHAR(400)
SET @vchAuthors = ''
SELECT @vchAuthors = @vchAuthors +
CASE WHEN @vchAuthors = '' THEN '' ELSE ', ' END + au_lname
FROM authors

SELECT @vchAuthors

Thats all I have about it. SQL Server MVP Narayana Vyas has an article abt the differences between SET and SELECT, which also includes a performance comparison between the two.

Differences between COALESCE and ISNULL

2004-08-01T13:22:00.000+05:30

I am trying to document the difference between some of the Keywords/functions, that are used for the same purpose and has some differences.

Let me start with COALESCE and ISNULL today. What comes first to my mind is COALESCE is hard to spell ;)

Lets see what are the relevant differences.

1. COALESCE is ANSI standard and ISNULL is T-SQL proprietary.

2. You can work with only one value at a time with ISNULL, but COALESCE can deal with multiple values. as in

SELECT ISNULL(NULL, 'SomeValue')
GO
SELECT COALESCE(NULL, NULL, NULL, 'SomeValue')
GO

3. With ISNULL, the Alternate value you specify is limited to the length of the first parameter. In case of COALESCE, you dont have such restrictions.

See the example below.

DECLARE @somestring CHAR(4)
SET @somestring = NULL

SELECT ISNULL(@somestring, 'Roji Thomas')
--Returns 'Roji'

SELECT COALESCE(@somestring, 'Roji Thomas')
--Returns 'Roji Thomas'

4. When using COALESCE All expressions must be of the same type or must be implicitly convertible to the same type. If you are not careful, this can give you incorrect results. See the examples below.

SELECT COALESCE(NULL, GetDate())
--Returns : 2004-07-26 13:47:15.937
--Correct

SELECT COALESCE(1, GetDate())
--Returns 1900-01-02 00:00:00.000
--Incorrect Value

SELECT COALESCE(1,'abcd')
--Returns 1
SELECT COALESCE('abcd',1)
--Returns Error
-- Server: Msg 245, Level 16, State 1, Line 1
-- Syntax error converting the varchar value 'abcd' to a column of data type int.

SELECT COALESCE(NULL,435,'abcd', CURRENT_TIMESTAMP,'xyzzz',435)
--Returns 1901-03-12 00:00:00.000
--Incorrect

SELECT COALESCE(NULL,GetDate(),9999999999)
--Returns : 2004-07-26 13:47:15.937
--Correct

SELECT COALESCE(NULL,9999999999, GetDate())
--Returs Error
-- Server: Msg 8115, Level 16, State 2, Line 1
-- Arithmetic overflow error converting expression to data type datetime.

5. Eventhough COALESCE is generally the preferred way, there are some performance consideration especially when you have a select statement as an arguument to COALESCE. So always analyse the execution plan. You can read further abt it here.

Here is two interesting Performance comparison between COALESCE and ISNULL.

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Feel Free to postback your comments.

Fancy Scoping

2004-06-28T20:45:00.000+05:30

Try the following query.

USE Pubs
GO
SELECT * FROM Authors
WHERE au_id IN
(SELECT au_id FROM Titles)

As we know there is no column called au_id
in the TITLES table, and the query returns
all the records in the Authors table, instead
of an invalid column name Error.

Amazed..? Actually I was amzed when I see
this behaviour first.

But this is the expected behaviour and thats
what makes the correlated subqueries work.

When SQL Server fails to find the au_id in the
TITLES table, it looks for the au_id column in
the Authors table.

So if our statement was
IN (SELECT T.au_id FROM Titles T)
the query will throw an Error.

Joe CELKO referred this behaviour as "fancy scoping"
You can read his original posting here.

Why BLOG?

2004-06-28T12:04:00.000+05:30

Hmmm Good question. Especially when
its considered that I hate people blogging
abt what breakfast they had and what dress they
wear.

I want to blog because, I believe in learning something
new everyday and I always try to do. As I am currently
focusing on The RDBMS side, I'll try to post my SQL Server
experiments as and when possible, not necessarily on a
daly basis.