Use sp_executesql Or EXEC To Get The Count For Dynamic Table Names

You need the record count for a table but this table could change every day or change based on which user executes the stored procedure
There is no point in creating dozens of stored procedures, all this can be done by using exec (sql) or sp_executesql
Of course sp_executesql is much better and if you run the statements below you will also see that it's execution plan is only 29.04 percent relative to the batch
So let's get started with sp_executesql

USE pubs
GO

--sp_executesql
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO


--EXEC (SQL)
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)


CREATE TABLE #temp (Totalcount INT)
SELECT @chvTableName = 'Authors'
SELECT @chvSQL = 'Insert into #temp Select Count(*) from ' + @chvTableName

EXEC( @chvSQL)

SELECT @intTableCount = Totalcount from #temp

SELECT @intTableCount

DROP TABLE #temp


First Hit CTRL + K (this will show the execution plan) , then highlight the complete code, hit F5 and look at the Execution Plan tab
As you can see sp_executesql is more than twice as efficient as exec (SQL)

Use DATEADD And DATEDIFF To Get The First And Last Day Of The Month

Here are a couple different ways to get the first day of the current month, the first day of next month and finally the last day of the current month
Nothing special really, it is just a matter of applying datediff and dateadd
This was a question that I answered in the Transact-SQL MSDN Forum

The first day of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01'))

The first day of next month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)
SELECT DATEADD(mm,1,CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01')))

The last day of the current month
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0))
SELECT DATEADD(d,-1,DATEADD(mm,1,CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01'))))

ROW_NUMBER, NTILE, RANK And DENSE_RANK

Yesterday I showed how to do ranking in SQL Server 2000, today we will look at how it's done in SQL Server 2005

CREATE TABLE Rankings (Value Char(1),id INT)
INSERT INTO Rankings
SELECT 'A',1 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'B',4 UNION ALL
SELECT 'B',5 UNION ALL
SELECT 'C',2 UNION ALL
SELECT 'D',6 UNION ALL
SELECT 'E',6 UNION ALL
SELECT 'F',5 UNION ALL
SELECT 'F',9 UNION ALL
SELECT 'F',10



ROW_NUMBER()
This will just add a plain vanilla row number

SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings


The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value
That happens when you use PARTITION with ROW_NUMBER

SELECT ROW_NUMBER() OVER( ORDER BY value ) AS 'rownumber',
ROW_NUMBER() OVER(PARTITION BY value ORDER BY ID ) AS 'Occurance',*
FROM Rankings
ORDER BY 1,2


This is just ordered in alphabetical order descending

SELECT ROW_NUMBER() OVER( ORDER BY Value DESC) AS 'rownumber',*
FROM Rankings

RANK()
Rank will skip numbers if there are duplicate values

SELECT RANK() OVER ( ORDER BY Value),*
FROM Rankings


DENSE_RANK()
DENSE_RANK will not skip numbers if there are duplicate values

SELECT DENSE_RANK() OVER ( ORDER BY Value),*
FROM Rankings


NTILE()
NTILE splits the set in buckets
So for 11 values we do something like this: 11/2 =5 + 1 remainder, the first 6 rows get 1 the next 5 rows get 2
If we use NTILE(3) we would have something like this: 11/3 =3 + 2 remainders, so 3 buckets of 3 and the first 2 buckets will get 1 of the remainders each

SELECT NTILE(2) OVER ( ORDER BY Value ),*
FROM Rankings

SELECT NTILE(3) OVER ( ORDER BY Value ),*
FROM Rankings

Ranking In SQL Server 2000

SQL server 2005 has 4 new ranking/windowing functions
These functions are RANK(), DENSE_RANK(), NTILE() and ROW_NUMBER()
I will show you tomorrow how you can use these new functions, today I will show you all the hard work you have to do to accomplish the same in SQL Server 2000
I am only going to show how to implement RANK(), DENSE_RANK() and ROW_NUMBER() in SQL Server 2000

CREATE TABLE Rankings (Value Char(1))
INSERT INTO Rankings
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F' UNION ALL
SELECT 'F' UNION ALL
SELECT 'F'


So let's start with ROW_NUMBER()
Since we have duplicates we can't do a running count, we will use that for DENSE_RANK
Duplicates are not considered each row has a unique number

ROW_NUMBER()
SELECT IDENTITY(INT, 1,1) AS Rank ,Value
INTO #Ranks FROM Rankings WHERE 1=0

INSERT INTO #Ranks
SELECT Value FROM Rankings
ORDER BY Value

SELECT * FROM #Ranks


Next up is DENSE_RANK()
Duplicates are considered and same values have the same number, numbers are not skipped

DENSE_RANK()
SELECT x.Ranking ,x.Value
FROM (SELECT (SELECT COUNT( DISTINCT t1.Value) FROM Rankings t1 WHERE z.Value>= t1.Value)AS Ranking, z.Value
FROM #Ranks z ) x
ORDER BY x.Ranking


And last we have RANK()
Duplicates are considered and same values have the same number, however numbers are skipped

RANK()
SELECT z.Ranking ,t2.Value
FROM (SELECT MIN(t1.Rank) AS Ranking,t1.Value FROM #Ranks t1 GROUP BY t1.Value) z
JOIN #Ranks t2 ON z.Value = t2.Value
ORDER BY z.Ranking


That is it, tomorrow I will do the SQL Server 2005 version of this code

Microsoft SQL Server 2005 Jolt Winner

Microsoft SQL Server 2005 is the Jolt Product Excellence and Productivity Award winner for database engines and data tools

DATABASE ENGINES AND DATA TOOLS
--------------------------------------------------
Jolt Winner: Microsoft SQL Server 2005 (Microsoft)

Productivity Winners:
• Berkeley DB 4.4 (Sleepycat Software)
• Google Maps API 2005 (Google)
• MySQL 5.0 (MySQL)

The rest of the categories are here

Programming SQL Server 2005 Book

I have just ordered this book without reading a sample chapter. I have about 15 O'Reilly books and all of them are excellent, that's why I did not hesitate purchasing this book. Once it arrives I will let you know more about it


This information is from the O'Reilly site:
SQL Server 2005, Microsoft's next-generation data management and analysis solution, represents a huge leap forward. It comes with a myriad of changes that deliver increased security, scalability, and power--making it the complete data package. Used properly, SQL Server 2005 can help organizations of all sizes meet their data challenges head on.


Programming SQL Server 2005 from O'Reilly provides a practical look at this updated version of Microsoft's premier database product. It guides you through all the new features, explaining how they work and how to use them. The first half of the book examines the changes and new features of the SQL Server Engine itself. The second addresses the enhanced features and tools of the platform, including the new services blended into this popular version. Each chapter contains numerous code samples-written in C# and compiled using the Visual Studio 2005 development environment-that show you exactly how to program SQL Server 2005.


Programming SQL Server 2005 can help you:

Build, deploy, and manage enterprise applications that are more secure, scalable, and reliable

Maximize IT productivity by reducing the complexity of building, deploying, and managing database applications

Share data across multiple platforms, applications, and devices to make it easier to connect internal and external systems


Because the goal of Programming SQL Server 2005 is to introduce all facets of Programming SQL Server 2005, it's beneficial to programmers of all levels. The book can be used as a primer by developers with little or no experience with SQL Server, as a ramp up to the new programming models for SQL Server 2005 for more experienced programmers, or as background and primer to specific concepts.


Any IT professional who wants to learn about SQL Server 2005's comprehensive feature set, interoperability with existing systems, and automation of routine tasks will find the answers in this authoritative guide.

Sample Chapter 18: Notification Services is available now

Amazon link is here

OPENROWSET And Open Excel file Problems

Last week I created a blog post name OPENROWSET And Excel Problems

I forgot to add one other example, and this is when you have an excel file open and you are trying to open it with OPENROWSET.
If you do this you will get this error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Just keep this in mind

SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP)

The Community Technology Preview (CTP) version of Microsoft SQL Server 2005 Service Pack 1 is now available. You can use these packages to upgrade any of the following SQL Server 2005 editions:
Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup

To upgrade SQL Server 2005 Express Edition, obtain the SP1 CTP version of Express Edition.

These packages have been made available for general testing purposes only. Do not deploy the CTP software in production


Click here to go to the download page

OPENROWSET And Excel Problems

Create an excel sheet
In the first 2 rows put some data, save the excel sheet as testing.xls on the c drive
Execute the command below
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')

You will see 1 row since the first row will be used for header names
If you want 2 rows you need to add HDR=No like this
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')


Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=SS:\testing.xls','SELECT * FROM [Sheet1$]')

The path can't be found (SS) you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].


Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing2.xls','SELECT * FROM [Sheet1$]')

When you spell the filename (testing2) wrong you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet11$]')

When you spell the sheet name (Sheet11) wrong you get this error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [Sheet11$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [P2 2003 DJIA updates$]'].

Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls','SELECT * FROM [Sheet1$]')

When you type the wrong path (D) but the path exists, then you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

So hopefully the next time you get an error you can quickly figure out if it's the file name, sheet name or path name that is wrong

Find Out If A Table Has An Identity Column

Here are 2 ways to find out if a table has an identity column
The first way is using the COLUMNPROPERTY function and the second way is using the OBJECTPROPERTY function

USE northwind
GO

DECLARE @tableName VARCHAR(50)
SELECT @tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO


DECLARE @intObjectID INT
SELECT @intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Search All Stored Procedures That Contain Specific Text

You have changed the column name from titleauthor to t_author in your table and you have 200 stored procedures
The problem is that you don't remember which of these 200 procedures use that column
You run sp_depends 'authors' but like we all know this procedure is not always accurate
So instead of running sp_depends we can take a look at the INFORMATION_SCHEMA.ROUTINES ANSI SQL Schema view

Let's search for all procedures that have the text titleauthor in them

USE pubs
GO

SELECT SPECIFIC_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%titleauthor%'

Create Stored Procedures With Optional Parameters

Sometimes you want to create stored procedures in which all the parameters are not required or will be used. You can create optional parameters and give them a value in the procedure. There are two ways to call procedures; you can pass parameters to stored procedures by name or by position. To pass parameters by name you would do something like this
EXEC prTestOptionalParameters @intID =1

To pass parameters by position you would do something like this
EXEC prTestOptionalParameters 1

Just keep in mind that if you pass by position that you don’t mix up the position because that makes for fun debugging time. Another problem with optional parameters is that if you call the procedure by using position for parameters then those parameters have to be at the end of the procedure. Let’s take a look at how this works

--Create the procedure with optional parameters at the end
CREATE PROCEDURE prTestOptionalParameters
@intID INT,
@chvName VARCHAR(80) = NULL

AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END

SET NOCOUNT OFF


--Run the proc without the optional parameter
EXEC prTestOptionalParameters 1

--Run the proc with the second parameter
EXEC prTestOptionalParameters 1,'abc'

--Run the proc without the optional parameter and use name parameters
EXEC prTestOptionalParameters @intID =1

--Run the proc with a null value for the optional parameter
EXEC prTestOptionalParameters 1 ,null


--Let's switch the parameters around
CREATE PROCEDURE prTestOptionalParameters2
@chvName VARCHAR(80) = NULL,
@intID INT

AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END

SET NOCOUNT OFF


--Run the proc without the optional parameter
EXEC prTestOptionalParameters2 1

You will get this error message
Server: Msg 201, Level 16, State 4, Procedure prTestOptionalParameters2, Line 0Procedure 'prTestOptionalParameters2' expects parameter '@intID', which was not supplied.

--Run the procedure with the named parameter
EXEC prTestOptionalParameters2 @intID =1

Create Procedures That Run At SQL Server Startup

Let's say you have a table and you want to make sure that you clear it every time SQL Server is restarted
What would be the easiest way to accomplish that? Well you can create a procedure and have it execute every time the SQL Server is restarted
The procedure has to be created in the master database, after it is created you have to use sp_procoption to have the procedure execute when SQL Server starts up

--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO


--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'

--This will return the proc name since we enabled the ExecIsStartup property
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1

--disable the execution of the proc on start up
sp_procoption prStartUp,startup,'off'

--Let's check again, no rows should be returned now
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1

Returning Grouped Random Results

Let's say you have data and you want to return this random but grouped, what do I mean by that? For example you have the following data
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5

When you rerun the query you want the order to be different but the companies have to be grouped together. So the next result could be something like this

toshiba 1
toshiba 3
sony 4
sony 6
mitsubishi 2
mitsubishi 5


To order a result set randomly every single time is easy you just have to add ORDER BY NEWID() and it will be different every single time

To order randomly and keeping it 'ordered/grouped' by company is a little bit trickier
Below are 2 ways to accomplish that. The first way is with a sub query the second is by using RAND and CHECKSUM
If you run the two queries at the same time and look at the execution plan you will notice that the query cost for the query with the sub query is 66.75% (relative to the batch) and for the query with RAND and CHECKSUM it's only 33.25%


For those of you who don't know how to see the execution plan, this is how you do that
CTRL + K (you can also select Query-->Show Execution Plan from the menu bar) then highlight both queries and press F5. Between Grids and Messages you will see a new tab named Execution Plan, click on that tab and you will see a graphical representation of the execution plan


CREATE TABLE #Testcompanies (
Name VARCHAR(50),
ID INT)


INSERT INTO #Testcompanies
SELECT 'toshiba' ,1
UNION ALL
SELECT 'mitsubishi', 2
UNION ALL
SELECT 'toshiba', 3
UNION ALL
SELECT 'sony', 4
UNION ALL
SELECT 'mitsubishi', 5
UNION ALL
SELECT 'sony', 6



--Query using a sub query and NEWID()
SELECT T.*
FROM #Testcompanies T
JOIN (SELECT DISTINCT TOP 100 PERCENT Name,
NEWID() AS GroupedOrder
FROM #Testcompanies
GROUP BY Name
ORDER BY NEWID()) Z
ON T.Name = Z.Name
ORDER BY Z.GroupedOrder


--Query using RAND()
DECLARE @R FLOAT
SET @R = RAND()

SELECT TOP 100 PERCENT *
FROM #TESTCOMPANIES
ORDER BY RAND(@R * CHECKSUM(NAME))

Conversation With Database Legend Jim Gray

Channel 9 has an interesting video with Turing award winner Jim Gray. From the website: "This episode features Jim Gray. He is a "Technical Fellow" in the Scaleable Servers Research Group (Sky Server, Terra Server) and manager of Microsoft's Bay Area Research Center (BARC). Jim has been called a "giant" in the fields of database and transaction processing computer systems. In 1998, Jim was awarded the ACM’s prestigious A.M. Turing Award.

Before joining Microsoft, Jim worked at Digital Equipment Corp (DEC)., Tandem Computers Inc., IBM Corp. and AT&T and he is the editor of the “Performance Handbook for Database and Transaction Processing Systems,” and co-author of “Transaction Processing: Concepts and Techniques.” In this interview, Jim is joined by former colleague from DEC and partner on the Terra Server project, Researcher, Tom Barclay."

Pro SQL Server 2005 Assemblies

Pro SQL Server 2005 Assemblies provides a detailed and example-driven tutorial on how to build and use .NET assemblies. The authors focus on building assemblies in C#, but also provide the equivalent VB .NET code in the supplied code download.

Assemblies are not a complete replacement for T-SQL stored procedures and triggers; rather, they’re enhancements, to be used at the right place and right time. This book examines the ins and outs of assemblies—when they should and should not be used, what you can do with them, and how you can get the most out of them.

Table Of Contents
CHAPTER 1 Introducing Assemblies
CHAPTER 2 Writing a Simple SQL Assembly
CHAPTER 3 The SQL Server NET Programming Model
CHAPTER 4 CLR Stored Procedures
CHAPTER 5 User-Defined Functions
CHAPTER 6 User-Defined Types
CHAPTER 7 User-Defined Aggregates
CHAPTER 8 CLR Triggers
CHAPTER 9 Error Handling and Debugging Strategies
CHAPTER 10 Security
CHAPTER 11 Integrating Assemblies with Other Technologies
INDEX

Sample Chapter: The SQL Server NET Programming Model


Amazon link here

SQL Server Cursors Not Always Evil

By know you probably heard a thousand times not to use cursors but to use a set based solution instead. Cursors are bad, almost everything that can be written in a cursor can be rewritten to use a set based approach etc etc etc. Well SQL Server MVP Adam Machanic has a great post on his blog that will show you that a cusrsor sometimes is actually faster than a set based solution. Read the post Running sums, redux
A day later Adam created another entry on his blog, this time using SQLCLR code to rewrite the cursor code and improving the execution time by 25%. You can read that entry here (Running sums yet again: SQLCLR saves the day!)
So as you can see cursors do have a place in SQL Server and are sometimes a better option

Top 5 SQL Posts Of All Time

Below are the top 5 posts according to Google Analytics for all time!!

SQL Server 2005 Free E-Learning
Fun With SQL Server Update Triggers
Feature Pack for Microsoft SQL Server 2005
Find all Primary and Foreign Keys In A Database
SQL Query Optimizations


I will update this post once a month and might make it 10, don't know yet

Top 5 SQL Server Posts for February 2006

Below are the top 5 posts according to Google Analytics for the month of February

SQL Query Optimizations
Fun With SQL Server Update Triggers
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Find all Primary and Foreign Keys In A Database
SQL Server 2005 Free E-Learning

Top SQL Server Google Searches For February 2006

These are the top SQL Searches on this site for the month of February. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...

HOW TO IMPROVE SQL SERVER Query PERFORMANCE
sql server tips tricks
HOW INCREASE THE SPEED OF SQL QUERY
hOW TO FINE TUNE SQL Query
sql code automation
bulk insert
deadlock
triggers
return multiple rows t-sql
CmdExec sql backup filename parameter
backup filename
how to backup a sql server db
SQl server books