After Omnibuzz's challenge this morning I decided to come up with a challenge of my own
Let's say you have a table with data (what else would be in the table bananas? )The data looks like this
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5
You want to return the results grouped together by company name but in random order
So for example the first resultset is
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5
you highlight the query hit F5 and the next result is
toshiba 1
toshiba 3
sony 4
sony 6
mitsubishi 2
mitsubishi 5
See where I am going? The resultset is random but the company names are grouped together
Here is the deal no CTE or windowing functions (RANK, DENSE_RANK, ROWNUMBER or NTILE) you know what forget about SQL Server 2005, this has to be able to run on SQL Server 2000
Also no temp tables or table variables (added after first response ;-) )
I have 2 solutions to this, I will post the solutions some time tomorrow
Below is DDL + Insert script
Enjoy
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
Here are the 2 solutions I had in mind
--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))
Does anyone else have a different solution than these two or the two in the comments?
Thursday, October 05, 2006
Answer To A SQL Challenge By Omnibuzz (SQL Garbage Collector)
Omnibuzz has posted the following challenge: A scenario to ponder #1
This challenge is about returning a random number of customers and returnig them in random order, here is what he said:
Say you have a table:
Customers (CustomerID int primary key, CustomerName varchar(50))
A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.
How will I go about doing it?
I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)
And here is my solution
CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE @value INT
SELECT @value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)
SELECT TOP @value *
FROM Customers
ORDER BY NEWID()
SET NOCOUNT OFF
GO
Since we are using SQL Server 2005 we can use TOP with a variable, and to set that variable we us the RAND function
The SQL Server 2000 version would look like this
CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE @value INT
SELECT @value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)
SET ROWCOUNT @value
SELECT *
FROM Customers
ORDER BY NEWID()
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
This challenge is about returning a random number of customers and returnig them in random order, here is what he said:
Say you have a table:
Customers (CustomerID int primary key, CustomerName varchar(50))
A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.
How will I go about doing it?
I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)
And here is my solution
CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE @value INT
SELECT @value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)
SELECT TOP @value *
FROM Customers
ORDER BY NEWID()
SET NOCOUNT OFF
GO
Since we are using SQL Server 2005 we can use TOP with a variable, and to set that variable we us the RAND function
The SQL Server 2000 version would look like this
CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE @value INT
SELECT @value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)
SET ROWCOUNT @value
SELECT *
FROM Customers
ORDER BY NEWID()
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
Wednesday, October 04, 2006
Red Gate's SQL Refactor Public CTP Released
That great company Red Gate has released a public CTP of their latest tool SQL Refactor. Thanks to Louis Davidson for sharing this info, you can get all the details including a download link on his blog right here: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1422.entry
Download it, play with it and let me know what you think
Download it, play with it and let me know what you think
Tuesday, October 03, 2006
Three Team Edition for Database Professionals Screencasts On Channel 9
Channel 9 has three screencast about Team Edition for Database Professionals
Team Edition for DB Pros 5 min Demo
"I'd like to introduce you to the latest edition of Visual Studio Team System - Team Edition for Database Professionals.
Check out this quick 5 minute demo to get a whirlwind tour of exactly what Team Data can do for you."
Creating a database project with Team Edition for Database Professionals
"I'd like to introduce you to how to create your database project using the latest edition of Visual Studio Team System - Team Edition for Database Professionals.
Check out this quick 10 minute demo to get a whirlwind tour of project creation within VSTE for DB Pro."
Configuring Design DB for Team Edition for Database Professionals
"This video will describe how to install and configure SQL Server 2005 to support Visual Studio Team Edition for Database Professionals database projects.
Richard Waymire is the Program Management Architect for Visual Studio Team System for Database Professionals. He’s been with Microsoft for more than 8 years, having been in the SQL Server team for most of that time. He’s the author of several books on SQL Server, a contributing editor to SQL Server Magazine, and a frequent speaker at SQL Server events."
Enjoy them.
Team Edition for DB Pros 5 min Demo
"I'd like to introduce you to the latest edition of Visual Studio Team System - Team Edition for Database Professionals.
Check out this quick 5 minute demo to get a whirlwind tour of exactly what Team Data can do for you."
Creating a database project with Team Edition for Database Professionals
"I'd like to introduce you to how to create your database project using the latest edition of Visual Studio Team System - Team Edition for Database Professionals.
Check out this quick 10 minute demo to get a whirlwind tour of project creation within VSTE for DB Pro."
Configuring Design DB for Team Edition for Database Professionals
"This video will describe how to install and configure SQL Server 2005 to support Visual Studio Team Edition for Database Professionals database projects.
Richard Waymire is the Program Management Architect for Visual Studio Team System for Database Professionals. He’s been with Microsoft for more than 8 years, having been in the SQL Server team for most of that time. He’s the author of several books on SQL Server, a contributing editor to SQL Server Magazine, and a frequent speaker at SQL Server events."
Enjoy them.
Monday, October 02, 2006
SQL Server Teaser
Here is a quick SQL Server teaser
Create the following table
CREATE TABLE [barney ]
(
barneyId INT
)
Then look at the following 4 statements which one will fail?
Do not run the statements try to guess, Is it A, B, C or D (or more than one?)
--A
INSERT [barney ] VALUES (1)
--B
INSERT barney VALUES (1)
--C
INSERT "barney" VALUES (1)
--D
INSERT [barney] VALUES (1)
BTW the idea for this post came after reading "Another reason to hate quoted identifiers..." on Louis Davidson's blog
Create the following table
CREATE TABLE [barney ]
(
barneyId INT
)
Then look at the following 4 statements which one will fail?
Do not run the statements try to guess, Is it A, B, C or D (or more than one?)
--A
INSERT [barney ] VALUES (1)
--B
INSERT barney VALUES (1)
--C
INSERT "barney" VALUES (1)
--D
INSERT [barney] VALUES (1)
BTW the idea for this post came after reading "Another reason to hate quoted identifiers..." on Louis Davidson's blog
Top 5 Posts For September 2006
Below are the top 5 posts according to Google Analytics for the month of September 2006 in order by pageviews descending
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
COALESCE And ISNULL Differences
Top 10 Articles of all time
OPENROWSET And Excel Problems
Store The Output Of A Stored Procedure In A Table Without Creating A Table
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
COALESCE And ISNULL Differences
Top 10 Articles of all time
OPENROWSET And Excel Problems
Store The Output Of A Stored Procedure In A Table Without Creating A Table
Top SQL Server Google Searches For September 2006
These are the top SQL Searches on this site for the month of September I have left out searches that have nothing to do with SQL Server or programming (for example atlantic city escorts)
calculating application availability
pl/sql code to calculate application availability
vb .net Datagrid column naming
does not have the identity property
application availability report and pl/sql
autoincrement
datetime string
sqldatareader stored proc clr
OUTER JOIN
OUTER JOIN SQL 2000 example
I always find it interesting to see what people are searching for and it also gives me ideas for things to write about
calculating application availability
pl/sql code to calculate application availability
vb .net Datagrid column naming
does not have the identity property
application availability report and pl/sql
autoincrement
datetime string
sqldatareader stored proc clr
OUTER JOIN
OUTER JOIN SQL 2000 example
I always find it interesting to see what people are searching for and it also gives me ideas for things to write about
Sunday, October 01, 2006
iWoz: From Computer Geek to Cult Icon: How I Invented the Personal Computer, Co-Founded Apple, and Had Fun Doing It
How I wish I had more time and needed less sleep (less than the 4-5 hours I am getting now) I am very excited about this book and will for sure put it on my Christmas listBook Description
The mastermind behind Apple sheds his low profile and steps forward to tell his story for the first time.
Before cell phones that fit in the palm of your hand and slim laptops that fit snugly into briefcases, computers were like strange, alien vending machines. They had cryptic switches, punch cards and pages of encoded output. But in 1975, a young engineering wizard named Steve Wozniak had an idea: What if you combined computer circuitry with a regular typewriter keyboard and a video screen? The result was the first true personal computer, the Apple I, a widely affordable machine that anyone could understand and figure out how to use.
Wozniak's life—before and after Apple—is a "home-brew" mix of brilliant discovery and adventure, as an engineer, a concert promoter, a fifth-grade teacher, a philanthropist, and an irrepressible prankster. From the invention of the first personal computer to the rise of Apple as an industry giant, iWoz presents a no-holds-barred, rollicking, firsthand account of the humanist inventor who ignited the computer revolution. 16 pages of illustrations.
Amazon link is here for those interested
Return All 78498 Prime Numbers Between 1 and 1000000 Continues in the Land Down Under
So this Prime Number challenge won't die, the other day I wrote about it in THIS post. Rob Farley from Down Under let me a comment with two approaches he took, I decided to link to them from a seperate post. His first attempt is primes and his second attempt is More On Primes. His approach is interesting since he doesn't delete from the table but actually inserts into the table. Make sure you check it out
Friday, September 29, 2006
Trouble With ISDATE And Converting To SMALLDATETIME
If you want to use the ISDATE function to convert a value to a SMALLDATETIME you also have to take into consideration that SMALLDATETIME stores date and time data from January 1, 1900, through June 6, 2079 but DATETIME stores date and time data from January 1, 1753 through December 31, 9999
So even though the ISDATE function returns 1 for the date 1890-01-01 this can not be converted to SMALLDATETIME and you will receive an error message after you run the following statement
SELECT CONVERT(SMALLDATETIME,'18900101')
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Also be careful with rounding
Run these four statements
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.998')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.999')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:30')
The first two are fine , the second two blow up because the value gets rounded up to the next day after it gets rounded up to the next minute (and hour)
I decided to roll out my own fnIsSmallDateTime() function because who wants to write the same CASE ISDATE when Value between this and that code all over the place?
Here is the code for the user defined function
CREATE FUNCTION fnIsSmallDateTime(@d VARCHAR(50))
RETURNS BIT
AS
BEGIN
DECLARE @bitReturnValue BIT
SELECT @bitReturnValue =CASE
WHEN ISDATE(@d) = 1 THEN CASE
WHEN CONVERT(DATETIME,@d) > ='19000101'
AND CONVERT(DATETIME,@d) <= '20790606 23:59:29.998' THEN 1
ELSE 0
END
ELSE 0
END
RETURN @bitReturnValue
END
GO
Let's create a test table with values
CREATE TABLE TestSmallDate (SomeDate VARCHAR(40))
INSERT TestSmallDate VALUES ('19000101')
INSERT TestSmallDate VALUES ('18991231')
INSERT TestSmallDate VALUES ('19010101')
INSERT TestSmallDate VALUES('20790607')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.677')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.998')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.999')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:59.000')
INSERT TestSmallDate VALUES('2079-06-06 01:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:01')
INSERT TestSmallDate VALUES('WhoIsYourDaddy')
If you want NULL for values that can not be converted to SMALLDATETIME use this code
SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate) END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate
if you want to convert the values that can not be converted to SMALLDATETIME to '1901-01-01 00:00:00' use the code below
SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate)
ELSE CONVERT(SMALLDATETIME,'19000101') END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate
Return only data that can be converted to SMALLDATETIME
SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =1
Return only data that can not converted to SMALLDATETIME
SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =0
So even though the ISDATE function returns 1 for the date 1890-01-01 this can not be converted to SMALLDATETIME and you will receive an error message after you run the following statement
SELECT CONVERT(SMALLDATETIME,'18900101')
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Also be careful with rounding
Run these four statements
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.998')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.999')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:30')
The first two are fine , the second two blow up because the value gets rounded up to the next day after it gets rounded up to the next minute (and hour)
I decided to roll out my own fnIsSmallDateTime() function because who wants to write the same CASE ISDATE when Value between this and that code all over the place?
Here is the code for the user defined function
CREATE FUNCTION fnIsSmallDateTime(@d VARCHAR(50))
RETURNS BIT
AS
BEGIN
DECLARE @bitReturnValue BIT
SELECT @bitReturnValue =CASE
WHEN ISDATE(@d) = 1 THEN CASE
WHEN CONVERT(DATETIME,@d) > ='19000101'
AND CONVERT(DATETIME,@d) <= '20790606 23:59:29.998' THEN 1
ELSE 0
END
ELSE 0
END
RETURN @bitReturnValue
END
GO
Let's create a test table with values
CREATE TABLE TestSmallDate (SomeDate VARCHAR(40))
INSERT TestSmallDate VALUES ('19000101')
INSERT TestSmallDate VALUES ('18991231')
INSERT TestSmallDate VALUES ('19010101')
INSERT TestSmallDate VALUES('20790607')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.677')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.998')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.999')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:59.000')
INSERT TestSmallDate VALUES('2079-06-06 01:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:01')
INSERT TestSmallDate VALUES('WhoIsYourDaddy')
If you want NULL for values that can not be converted to SMALLDATETIME use this code
SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate) END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate
if you want to convert the values that can not be converted to SMALLDATETIME to '1901-01-01 00:00:00' use the code below
SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate)
ELSE CONVERT(SMALLDATETIME,'19000101') END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate
Return only data that can be converted to SMALLDATETIME
SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =1
Return only data that can not converted to SMALLDATETIME
SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =0
SQL Server Application Platform Podcast About SQL Server Service Broker On Channel 9
Channel 9 has a two part podcast with Roger Wolter about SQL Server Service Broker. WMA, MP3 and Video formats are available for download
From the site: "You are thinking of a messaging solution for your application. A solution that can exchange messages reliably, predictably and in-order. A solution that offers queue like functionality only better. What is it you ask? None other than SQL Server 2005 and this very interesting technology known as SQL Service Broker that is built right into it. On today’s program I’m joined by my colleague Roger Wolter who is going to give us all the juicy details"
Get the episodes here --> part1, part2
From the site: "You are thinking of a messaging solution for your application. A solution that can exchange messages reliably, predictably and in-order. A solution that offers queue like functionality only better. What is it you ask? None other than SQL Server 2005 and this very interesting technology known as SQL Service Broker that is built right into it. On today’s program I’m joined by my colleague Roger Wolter who is going to give us all the juicy details"
Get the episodes here --> part1, part2
Wednesday, September 27, 2006
Cool And Sexy New SQL Server Blog
That's right! What is more cool or sexy than Query Optimizations? It doesn't matter how beautiful or complex your data model is, if you show to your boss that a query used to take 17 seconds and now runs in 300 milli-seconds then you are the new SQL superhero.
If some of the following terms are foreign to you (CTRL + K, Index Scan, Index Seek, Table Scan, Sargable, Index Hint, Parameter Sniffing, Missing Statistics, L2 Cache, Compilation, Optimal Plans) then I have the blog for you right here
Tips, Tricks, and Advice from the SQL Server Query Processing Team
Even if you do know about those terms then this is still the blog for you since there is tons of stuff that you did not know yet. so make sure to check it out and add it to your feed
If some of the following terms are foreign to you (CTRL + K, Index Scan, Index Seek, Table Scan, Sargable, Index Hint, Parameter Sniffing, Missing Statistics, L2 Cache, Compilation, Optimal Plans) then I have the blog for you right here
Tips, Tricks, and Advice from the SQL Server Query Processing Team
Even if you do know about those terms then this is still the blog for you since there is tons of stuff that you did not know yet. so make sure to check it out and add it to your feed
Tuesday, September 26, 2006
Return A Rowcount By Using Count Or Sign
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function
For CASE we will do this
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0
Using the SIGN function is even easier, all you have to do is this
RETURN SIGN(@@ROWCOUNT)
That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action
USE pubs
GO
--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO
--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN SIGN(@@ROWCOUNT)
GO
--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO
--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO
--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO
--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO
--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function
For CASE we will do this
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0
Using the SIGN function is even easier, all you have to do is this
RETURN SIGN(@@ROWCOUNT)
That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action
USE pubs
GO
--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO
--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN SIGN(@@ROWCOUNT)
GO
--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO
--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO
--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO
--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO
--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO
Monday, September 25, 2006
Happy One Year Anniversary
So here we are one year and 236 posts later. I can not believe that it has been one year already. First of all I will make 2 small changes. The first change is that I will feature a blog/site of the week; this will always happen on a Friday. I will link to the blog and link to the 5 most interesting posts/articles. If possible I will say a little something about the person whose site it is, something like author of this book and an interview is available here.
The second change is that I will write some stuff that has nothing to do with SQL Server but might still be of interest to you. This I will publish on weekends so that you can skip that easily if you check on weekdays only. What will I write? Maybe something that goes on in my life or a book or movie review. However I will not review the Matrix, Titanic or some other well know movie. No I will pick something that is not as popular for example Ghost In The Machine, The Seven Samurai, Animatrix. For books this could be Crypto, The Cobra Event or The Coming Plague
Or I could write that once you have kids and you do NOT have TIVO then Comcast On Demand really rocks. For example Jericho is a show that I just started to watch, this show reminded me a little bit of The Stand by Stephen King (his best book together with Thinner, It and Salems Lot)
So what is so cool about On Demand? No commercials, that’s right; nada. Pause and Resume for up to 24 hours, this is a must have with newborns.
Comcast announced a deal with CBS to have the following shows free the day after it airs: CSI: Crime Scene Investigation, CSI: Miami, CSI: NY, Survivor, NCIS, Numb3rs, Jericho and Big Brother
That’s it for now
The second change is that I will write some stuff that has nothing to do with SQL Server but might still be of interest to you. This I will publish on weekends so that you can skip that easily if you check on weekdays only. What will I write? Maybe something that goes on in my life or a book or movie review. However I will not review the Matrix, Titanic or some other well know movie. No I will pick something that is not as popular for example Ghost In The Machine, The Seven Samurai, Animatrix. For books this could be Crypto, The Cobra Event or The Coming Plague
Or I could write that once you have kids and you do NOT have TIVO then Comcast On Demand really rocks. For example Jericho is a show that I just started to watch, this show reminded me a little bit of The Stand by Stephen King (his best book together with Thinner, It and Salems Lot)
So what is so cool about On Demand? No commercials, that’s right; nada. Pause and Resume for up to 24 hours, this is a must have with newborns.
Comcast announced a deal with CBS to have the following shows free the day after it airs: CSI: Crime Scene Investigation, CSI: Miami, CSI: NY, Survivor, NCIS, Numb3rs, Jericho and Big Brother
That’s it for now
Return All 78498 Prime Numbers Between 1 and 1000000 In 3 seconds
That is right folks; SQL Server is capable of returning all 78498 prime numbers between 1 and 1000000 in 3 seconds. Who said that SQL Server isn't suitable for this task?
Let's start with a little bit of history; Ward Pond had a posting on his blog on how to create a table with 1000000 rows. Hugo Kornelis replied with a solution that ran in 1110 ms. For fun I left the following comment: “How about the next challenge is to return all 78498 prime numbers between 1 and 1000000?”
Ward took the challenge and posted a solution that would take hours to complete. Then Hugo Kornelis posted a solution that took 8 seconds. After that Ward tweaked Hugo’s solution and got it down to 3 seconds. That is just unbelievable. I wonder how long it would run if you were to code something like that in C, C++, C# or your favorite language?
Any takers?
Let's start with a little bit of history; Ward Pond had a posting on his blog on how to create a table with 1000000 rows. Hugo Kornelis replied with a solution that ran in 1110 ms. For fun I left the following comment: “How about the next challenge is to return all 78498 prime numbers between 1 and 1000000?”
Ward took the challenge and posted a solution that would take hours to complete. Then Hugo Kornelis posted a solution that took 8 seconds. After that Ward tweaked Hugo’s solution and got it down to 3 seconds. That is just unbelievable. I wonder how long it would run if you were to code something like that in C, C++, C# or your favorite language?
Any takers?
Wednesday, September 20, 2006
Five Ways To Return Values From Stored Procedures
I have answered a bunch of questions over the last couple of days and some of them had to do with returning values from stored procedures
Everyone knows that you can return a value by using return inside a stored procedure. What everyone doesn't know is that return can only be an int data type
So how do you return something that is not an int (bigint, smallint etc etc) datatype
Let's take a look
We will start with a regular return statement, everything works as expected
--#1 return
CREATE PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i int
SELECT @i = DATEPART(hh,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue int
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
Now let's try returning a varchar
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i VARCHAR(50)
SELECT @i = DATENAME(mm,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
Oops, it doesn't work the following message is returned (if you run it in September)
Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'September' to a column of data type int.
Let's try hard coding a character value
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
RETURN 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
It is interesting that the procedure compiles without a problem. But when we try to run it the following message is displayed
Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'ab' to a column of data type int.
So what can we do? well we can use an OUTPUT parameter. By the way the following 4 ways to return a varchar values are in the order from best to worst
--#2 OUTPUT
ALTER PROCEDURE TestReturn @SomeParm VARCHAR(50) OUTPUT
AS
SET NOCOUNT ON
SELECT @SomeParm = 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC TestReturn @SomeParm = @SomeValue OUTPUT
SELECT @SomeValue
GO
Another way is to create a temp table and call the proc with insert..exec
--#3 Insert Into TEMP Table outside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
SELECT 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
INSERT INTO #Test
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test
SELECT @SomeValue
DROP TABLE #Test
GO
This one is almost the same as the previous example, the only difference is that ther insert happens inside the proc
And of course if you call the proc without creating the table you will get a nice error message
--#4 Insert Into TEMP Table inside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
INSERT INTO #Test
SELECT 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test
SELECT @SomeValue
DROP TABLE #Test
And last you create a permanent table with an identity, in the proc you insert into that table and you return the identity value. You can then use that identity value to get the varchar value
--#5 Insert Into A Table And Return The Identity value
CREATE TABLE HoldingTable(ID INT IDENTITY,SomeValue VARCHAR(50))
GO
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i INT
INSERT INTO HoldingTable
SELECT 'ab'
SELECT @I = SCOPE_IDENTITY()
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50), @i INT
EXEC @i = TestReturn
SELECT @SomeValue = SomeValue
FROM HoldingTable
WHERE ID = @i
SELECT @SomeValue
DROP PROCEDURE TestReturn
Everyone knows that you can return a value by using return inside a stored procedure. What everyone doesn't know is that return can only be an int data type
So how do you return something that is not an int (bigint, smallint etc etc) datatype
Let's take a look
We will start with a regular return statement, everything works as expected
--#1 return
CREATE PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i int
SELECT @i = DATEPART(hh,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue int
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
Now let's try returning a varchar
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i VARCHAR(50)
SELECT @i = DATENAME(mm,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
Oops, it doesn't work the following message is returned (if you run it in September)
Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'September' to a column of data type int.
Let's try hard coding a character value
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
RETURN 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO
It is interesting that the procedure compiles without a problem. But when we try to run it the following message is displayed
Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'ab' to a column of data type int.
So what can we do? well we can use an OUTPUT parameter. By the way the following 4 ways to return a varchar values are in the order from best to worst
--#2 OUTPUT
ALTER PROCEDURE TestReturn @SomeParm VARCHAR(50) OUTPUT
AS
SET NOCOUNT ON
SELECT @SomeParm = 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
EXEC TestReturn @SomeParm = @SomeValue OUTPUT
SELECT @SomeValue
GO
Another way is to create a temp table and call the proc with insert..exec
--#3 Insert Into TEMP Table outside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
SELECT 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
INSERT INTO #Test
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test
SELECT @SomeValue
DROP TABLE #Test
GO
This one is almost the same as the previous example, the only difference is that ther insert happens inside the proc
And of course if you call the proc without creating the table you will get a nice error message
--#4 Insert Into TEMP Table inside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
INSERT INTO #Test
SELECT 'ab'
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test
SELECT @SomeValue
DROP TABLE #Test
And last you create a permanent table with an identity, in the proc you insert into that table and you return the identity value. You can then use that identity value to get the varchar value
--#5 Insert Into A Table And Return The Identity value
CREATE TABLE HoldingTable(ID INT IDENTITY,SomeValue VARCHAR(50))
GO
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i INT
INSERT INTO HoldingTable
SELECT 'ab'
SELECT @I = SCOPE_IDENTITY()
RETURN @i
SET NOCOUNT OFF
GO
DECLARE @SomeValue VARCHAR(50), @i INT
EXEC @i = TestReturn
SELECT @SomeValue = SomeValue
FROM HoldingTable
WHERE ID = @i
SELECT @SomeValue
DROP PROCEDURE TestReturn
Tuesday, September 19, 2006
You Can Rollback Tables That You Have Truncated (Inside A Transaction)
There seems to be a misconception that when you issue a TRUNCATE command against a table you will not be able to roll back.
That simply is not true; TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
What does this mean? This means that SQL Server will use the mimimum amount of logging that it can to delete the data and still make it recoverable. in contrast to that the DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
You see why TRUNCATE is so much faster; it deals with pages not with rows. and we all know that 1 extent is 8 pages and a page is 8K and can hold 8060 bytes. Well if you rows are 20 bytes wide then you need to log 403 delete statements with DELETE but TRUNCATE just uses a pointer to the page
So let's see how that works
--Create the table and inser 6 values
CREATE TABLE RollBacktest(id INT)
INSERT RollBacktest VALUES( 1 )
INSERT RollBacktest VALUES( 2 )
INSERT RollBacktest VALUES( 3 )
INSERT RollBacktest VALUES( 4 )
INSERT RollBacktest VALUES( 5 )
INSERT RollBacktest VALUES( 6 )
GO
--Should be 6 rows
SELECT 'Before The Transaction',* FROM RollBacktest
BEGIN TRAN RollBackTestTran
TRUNCATE TABLE RollBacktest
--Should be empty resultset
SELECT * FROM RollBacktest
--should be 0
SELECT COUNT(*) AS 'TruncatedCount' FROM RollBacktest
ROLLBACK TRAN RollBackTestTran
--Yes it is 6 again
SELECT 'ROLLED BACK',* FROM RollBacktest
DROP TABLE RollBacktest
That simply is not true; TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
What does this mean? This means that SQL Server will use the mimimum amount of logging that it can to delete the data and still make it recoverable. in contrast to that the DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
You see why TRUNCATE is so much faster; it deals with pages not with rows. and we all know that 1 extent is 8 pages and a page is 8K and can hold 8060 bytes. Well if you rows are 20 bytes wide then you need to log 403 delete statements with DELETE but TRUNCATE just uses a pointer to the page
So let's see how that works
--Create the table and inser 6 values
CREATE TABLE RollBacktest(id INT)
INSERT RollBacktest VALUES( 1 )
INSERT RollBacktest VALUES( 2 )
INSERT RollBacktest VALUES( 3 )
INSERT RollBacktest VALUES( 4 )
INSERT RollBacktest VALUES( 5 )
INSERT RollBacktest VALUES( 6 )
GO
--Should be 6 rows
SELECT 'Before The Transaction',* FROM RollBacktest
BEGIN TRAN RollBackTestTran
TRUNCATE TABLE RollBacktest
--Should be empty resultset
SELECT * FROM RollBacktest
--should be 0
SELECT COUNT(*) AS 'TruncatedCount' FROM RollBacktest
ROLLBACK TRAN RollBackTestTran
--Yes it is 6 again
SELECT 'ROLLED BACK',* FROM RollBacktest
DROP TABLE RollBacktest
Monday, September 18, 2006
DDL Trigger Events Documented In Books On Line
A while back I wrote about DDL trigger events in a post named DDL Trigger Events Revisited
And I claimed that this stuff wasn't documented
Well I am wrong, this information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.
The link to the online Books On Line is below
http://msdn2.microsoft.com/en-us/library/ms191441.aspx
Anyway they have an image, at least you can copy and paste the code I gave you ;-)
And I claimed that this stuff wasn't documented
Well I am wrong, this information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.
The link to the online Books On Line is below
http://msdn2.microsoft.com/en-us/library/ms191441.aspx
Anyway they have an image, at least you can copy and paste the code I gave you ;-)
Friday, September 15, 2006
Do Not Concatenate VARCHAR and VARCHAR(MAX) Variables
Do Not Concatenate VARCHAR and VARCHAR(MAX) Variables, what happens is that the whole string will be implicitly converted to varchar(8000)
Run these examples to see what I mean
declare @v varchar(max)
select @v = (cast('a' as varchar)) + replicate('a', 9000)
select len(@v)
--8000
GO
declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate('a', 9000)
select len(@v)
--8000
GO
declare @v varchar(max)
select @v = (cast('a' as varchar)) +replicate (cast('a' as varchar(max)), 9000)
select len(@v)
--9001
GO
declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate(cast('a' as varchar(max)), 9000)
select len(@v)
--9001
GO
Or how about this? If you don't convert to varchar(max) while doing the LEN function it returns 8000
declare @v varchar(max)
select @v = replicate('a', 9000)
select len(@v)
declare @v varchar(max)
select @v = replicate(cast('a' as varchar(max)), 9000)
select len(@v)
Run these examples to see what I mean
declare @v varchar(max)
select @v = (cast('a' as varchar)) + replicate('a', 9000)
select len(@v)
--8000
GO
declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate('a', 9000)
select len(@v)
--8000
GO
declare @v varchar(max)
select @v = (cast('a' as varchar)) +replicate (cast('a' as varchar(max)), 9000)
select len(@v)
--9001
GO
declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate(cast('a' as varchar(max)), 9000)
select len(@v)
--9001
GO
Or how about this? If you don't convert to varchar(max) while doing the LEN function it returns 8000
declare @v varchar(max)
select @v = replicate('a', 9000)
select len(@v)
declare @v varchar(max)
select @v = replicate(cast('a' as varchar(max)), 9000)
select len(@v)
Thursday, September 14, 2006
O'Reilly Code Search
Here is something handy:
Announcing O'Reilly Code Search, where you can enter search terms to find relevant sample code from nearly 700 O'Reilly books. The database currently contains over 123,000 individual examples, comprises 2.6 million lines of code, all edited and ready to use.
it's pretty neat, all the source code from all the O'Reilly books is searchable online
So to Search for the term SELECT in category SQL you would enter "cat:sql select" and this would return these results http://labs.oreilly.com/search.xqy?t=code&q=cat%3Asql+select
For C# you would do "cat:csharp select" and just SQL Server instead of SQL would be "cat:sql server select"
Let me know what you think
Announcing O'Reilly Code Search, where you can enter search terms to find relevant sample code from nearly 700 O'Reilly books. The database currently contains over 123,000 individual examples, comprises 2.6 million lines of code, all edited and ready to use.
it's pretty neat, all the source code from all the O'Reilly books is searchable online
So to Search for the term SELECT in category SQL you would enter "cat:sql select" and this would return these results http://labs.oreilly.com/search.xqy?t=code&q=cat%3Asql+select
For C# you would do "cat:csharp select" and just SQL Server instead of SQL would be "cat:sql server select"
Let me know what you think
Wednesday, September 13, 2006
What Is Your Corporate Standard
If you are not a consultant and you work for a company then does your company have a corporate standard for development languages/products?
Our IT department is about 800 people and to get good support you can not have 3 thousands different products in your shop. As of today this is what is supported in our company
Java Stack
Sun's Project Tango
Apache Web Server 2.x
Tomcat 5.x (web container), JBoss 4.x (EJB and Web Container), WebSphere Network Edition 6.1.x (web and EJB container)
Hibernate 2.x, Spring 1.2.x
Sun's J2SE 5 (aka J2SE 1.5.x)
MySQl 5.x, Oracle 10g, SQL Server 2005
.NET Stack
WCF
IIS 6
.NET 2.0
CLR Version 2
MySQl 5.x, Oracle 10g, SQL Server 2005
Of course we have other things that we use ColdFusion, SQL Server 2000, that is fine but no NEW development is supposed to be done with those tools/products
So here is my question to you; what is your corporate standard?
Our IT department is about 800 people and to get good support you can not have 3 thousands different products in your shop. As of today this is what is supported in our company
Java Stack
Sun's Project Tango
Apache Web Server 2.x
Tomcat 5.x (web container), JBoss 4.x (EJB and Web Container), WebSphere Network Edition 6.1.x (web and EJB container)
Hibernate 2.x, Spring 1.2.x
Sun's J2SE 5 (aka J2SE 1.5.x)
MySQl 5.x, Oracle 10g, SQL Server 2005
.NET Stack
WCF
IIS 6
.NET 2.0
CLR Version 2
MySQl 5.x, Oracle 10g, SQL Server 2005
Of course we have other things that we use ColdFusion, SQL Server 2000, that is fine but no NEW development is supposed to be done with those tools/products
So here is my question to you; what is your corporate standard?
The sum or average aggregate operation cannot take a bit data type as an argument
The sum or average aggregate operation cannot take a bit data type as an argument.
Oh yes I fell for this one yesterday. It's not that I didn't know about it (in the back of my head) it's just that I forgot
I was answering one question in the microsoft forums and someone wanted to sum something, unfortunately the datatype was bit and as we all know bit data types can not be used with average or sum.
You see that's why it is important when asking question to provide DDL and INSERT scripts. If I had that then I would have gotten the error myself and would have modified the query by converting to int
So instead of this (simplified)
SELECT SUM(col1)
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
I would have done this
SELECT SUM(CONVERT(INT,col1))
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
And of course we should all read this-->
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.htm l
Does this qualify as a rant? I hope not.
Oh yes I fell for this one yesterday. It's not that I didn't know about it (in the back of my head) it's just that I forgot
I was answering one question in the microsoft forums and someone wanted to sum something, unfortunately the datatype was bit and as we all know bit data types can not be used with average or sum.
You see that's why it is important when asking question to provide DDL and INSERT scripts. If I had that then I would have gotten the error myself and would have modified the query by converting to int
So instead of this (simplified)
SELECT SUM(col1)
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
I would have done this
SELECT SUM(CONVERT(INT,col1))
FROM (SELECT CONVERT(BIT,1) AS col1 UNION ALL
SELECT CONVERT(BIT,0) )P
And of course we should all read this-->
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.htm l
Does this qualify as a rant? I hope not.
Sunday, September 10, 2006
sys.dm_db_index_usage_stats
This is the second article about the dynamic managment views in SQL Server 2005, to see all of them click here
Today we are going to talk about the sys.dm_db_index_usage_stats dynamic managment view
This view is extremely helpful in a couple of ways, I will list some of them
It can help you identify if an index is used or not
You can also find out the scan to seek ratio
Another helpful thing is the fact that the last seek and scan dates are in the view, this can help you determine if the index is still used
So let's get started shall we?
CREATE TABLE TestIndex(id INT identity,
SomeID INT not null,
SomeDate DATETIME not null)
GO
CREATE CLUSTERED INDEX IX_TestIndexID ON TestIndex(SomeID)
GO
CREATE NONCLUSTERED INDEX IX_TestIndexDate ON TestIndex(SomeDate)
GO
INSERT TestIndex VALUES(1,GETDATE())
GO
INSERT TestIndex VALUES(2,GETDATE()-1)
GO
--Run the sys.dm_db_index_usage_stats query
SELECT
TableName = OBJECT_NAME(s.[object_id]),
IndexName = i.name,
s.last_user_seek,
s.user_seeks,
CASE s.user_seeks WHEN 0 THEN 0
ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage,
s.last_user_scan,
s.user_scans,
CASE s.user_scans WHEN 0 THEN 0
ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage,
s.last_user_lookup,
s.user_lookups,
s.last_user_update,
s.user_updates,
s.last_system_seek,
s.last_system_scan,
s.last_system_lookup,
s.last_system_update,*
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE
s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND OBJECT_NAME(s.[object_id]) = 'TestIndex';
After each of the select queries below run the sys.dm_db_index_usage_stats query above
--user_updates should be 2 but user_seeks,user_scans, user_lookups should be 0
SELECT *
FROM TestIndex
WHERE ID =1
--IX_TestIndexID user_scans = 1
SELECT *
FROM TestIndex
WHERE SomeID =1
--IX_TestIndexID user_seeks = 1
SELECT *
FROM TestIndex
WHERE SomeDate > GETDATE() -1
AND SomeID =1
--IX_TestIndexID user_seeks = 2
--let's force the optimizer to use the IX_TestIndexDate index
SELECT *
FROM TestIndex WITH (INDEX = IX_TestIndexDate)
WHERE SomeDAte > GETDATE() -1
--IX_TestIndexDate user_seeks = 1
IX_TestIndexID
SeekPercentage = 66.66% and ScanPercentage = 33.33
As you can see I have added the following code
CASE s.user_seeks WHEN 0 THEN 0
ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage
CASE s.user_scans WHEN 0 THEN 0
ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage
This is helpful to determine the seek/scan ratio if you have mostly scans then maybe you have to look at your queries to optimize them
If you run the sys.dm_db_index_usage_stats query again you will se that the user_updates column is 2, that's because we inserted 2 rows (2 batches)
Let's do this
UPDATE TestIndex
SET SomeID = SomeID + 1
--(2 row(s) affected)
Now user_updates is 3 since we used 1 batch that modified 2 rows
Now restart your server and run the same query again. as you can see the resultset is empty this is because the counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.
When you run this query
SELECT *
FROM TestIndex
You will see a row again after you run the sys.dm_db_index_usage_stats query
Also note that every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.
The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly all by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.
sys.dm_db_index_usage_stats
database_id smallint
ID of the database on which the table or view is defined.
object_id int
ID of the table or view on which the index is defined
index_id int
ID of the index.
user_seeks bigint
Number of seeks by user queries.
user_scans bigint
Number of scans by user queries.
user_lookups bigint
Number of lookups by user queries.
user_updates bigint
Number of updates by user queries.
last_user_seek datetime
Time of last user seek
last_user_scan datetime
Time of last user scan.
last_user_lookup datetime
Time of last user lookup.
last_user_update datetime
Time of last user update.
system_seeks bigint
Number of seeks by system queries.
system_scans bigint
Number of scans by system queries.
system_lookups bigint
Number of lookups by system queries.
system_updates bigint
Number of updates by system queries.
last_system_seek datetime
Time of last system seek.
last_system_scan datetime
Time of last system scan.
last_system_lookup datetime
Time of last system lookup.
last_system_update datetime
Time of last system update.
Today we are going to talk about the sys.dm_db_index_usage_stats dynamic managment view
This view is extremely helpful in a couple of ways, I will list some of them
It can help you identify if an index is used or not
You can also find out the scan to seek ratio
Another helpful thing is the fact that the last seek and scan dates are in the view, this can help you determine if the index is still used
So let's get started shall we?
CREATE TABLE TestIndex(id INT identity,
SomeID INT not null,
SomeDate DATETIME not null)
GO
CREATE CLUSTERED INDEX IX_TestIndexID ON TestIndex(SomeID)
GO
CREATE NONCLUSTERED INDEX IX_TestIndexDate ON TestIndex(SomeDate)
GO
INSERT TestIndex VALUES(1,GETDATE())
GO
INSERT TestIndex VALUES(2,GETDATE()-1)
GO
--Run the sys.dm_db_index_usage_stats query
SELECT
TableName = OBJECT_NAME(s.[object_id]),
IndexName = i.name,
s.last_user_seek,
s.user_seeks,
CASE s.user_seeks WHEN 0 THEN 0
ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage,
s.last_user_scan,
s.user_scans,
CASE s.user_scans WHEN 0 THEN 0
ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage,
s.last_user_lookup,
s.user_lookups,
s.last_user_update,
s.user_updates,
s.last_system_seek,
s.last_system_scan,
s.last_system_lookup,
s.last_system_update,*
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE
s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND OBJECT_NAME(s.[object_id]) = 'TestIndex';
After each of the select queries below run the sys.dm_db_index_usage_stats query above
--user_updates should be 2 but user_seeks,user_scans, user_lookups should be 0
SELECT *
FROM TestIndex
WHERE ID =1
--IX_TestIndexID user_scans = 1
SELECT *
FROM TestIndex
WHERE SomeID =1
--IX_TestIndexID user_seeks = 1
SELECT *
FROM TestIndex
WHERE SomeDate > GETDATE() -1
AND SomeID =1
--IX_TestIndexID user_seeks = 2
--let's force the optimizer to use the IX_TestIndexDate index
SELECT *
FROM TestIndex WITH (INDEX = IX_TestIndexDate)
WHERE SomeDAte > GETDATE() -1
--IX_TestIndexDate user_seeks = 1
IX_TestIndexID
SeekPercentage = 66.66% and ScanPercentage = 33.33
As you can see I have added the following code
CASE s.user_seeks WHEN 0 THEN 0
ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage
CASE s.user_scans WHEN 0 THEN 0
ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage
This is helpful to determine the seek/scan ratio if you have mostly scans then maybe you have to look at your queries to optimize them
If you run the sys.dm_db_index_usage_stats query again you will se that the user_updates column is 2, that's because we inserted 2 rows (2 batches)
Let's do this
UPDATE TestIndex
SET SomeID = SomeID + 1
--(2 row(s) affected)
Now user_updates is 3 since we used 1 batch that modified 2 rows
Now restart your server and run the same query again. as you can see the resultset is empty this is because the counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.
When you run this query
SELECT *
FROM TestIndex
You will see a row again after you run the sys.dm_db_index_usage_stats query
Also note that every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.
The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly all by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.
sys.dm_db_index_usage_stats
database_id smallint
ID of the database on which the table or view is defined.
object_id int
ID of the table or view on which the index is defined
index_id int
ID of the index.
user_seeks bigint
Number of seeks by user queries.
user_scans bigint
Number of scans by user queries.
user_lookups bigint
Number of lookups by user queries.
user_updates bigint
Number of updates by user queries.
last_user_seek datetime
Time of last user seek
last_user_scan datetime
Time of last user scan.
last_user_lookup datetime
Time of last user lookup.
last_user_update datetime
Time of last user update.
system_seeks bigint
Number of seeks by system queries.
system_scans bigint
Number of scans by system queries.
system_lookups bigint
Number of lookups by system queries.
system_updates bigint
Number of updates by system queries.
last_system_seek datetime
Time of last system seek.
last_system_scan datetime
Time of last system scan.
last_system_lookup datetime
Time of last system lookup.
last_system_update datetime
Time of last system update.
Saturday, September 09, 2006
Don't Use Union On Tables With Text Columns
When you have a SQL UNION between 2 or more tables and some of these tables have columns with a text data type use UNION ALL instead of UNION.
If you use UNION you will be given the following message
Server: Msg 8163, Level 16, State 4, Line 10
The text, ntext, or image data type cannot be selected as DISTINCT.
What happens is that UNION use distinct behind the scenes and you can not use distinct on text, ntext or image data types
Run this script to see what I mean
CREATE TABLE TestUnion1 (id INT,textCol TEXT)
CREATE TABLE TestUnion2 (id INT,textCol TEXT)
GO
INSERT TestUnion1 VALUES(1,'abc')
INSERT TestUnion2 VALUES(1,'abc')
INSERT TestUnion1 VALUES(1,'aaa')
INSERT TestUnion1 VALUES(1,'zzz')
INSERT TestUnion1 VALUES(3,'abc')
--problem
SELECT * FROM TestUnion1
UNION --ALL
SELECT * FROM TestUnion2
--no problem
SELECT * FROM TestUnion1
UNION ALL
SELECT * FROM TestUnion2
DROP TABLE TestUnion1,TestUnion2
If you use UNION you will be given the following message
Server: Msg 8163, Level 16, State 4, Line 10
The text, ntext, or image data type cannot be selected as DISTINCT.
What happens is that UNION use distinct behind the scenes and you can not use distinct on text, ntext or image data types
Run this script to see what I mean
CREATE TABLE TestUnion1 (id INT,textCol TEXT)
CREATE TABLE TestUnion2 (id INT,textCol TEXT)
GO
INSERT TestUnion1 VALUES(1,'abc')
INSERT TestUnion2 VALUES(1,'abc')
INSERT TestUnion1 VALUES(1,'aaa')
INSERT TestUnion1 VALUES(1,'zzz')
INSERT TestUnion1 VALUES(3,'abc')
--problem
SELECT * FROM TestUnion1
UNION --ALL
SELECT * FROM TestUnion2
--no problem
SELECT * FROM TestUnion1
UNION ALL
SELECT * FROM TestUnion2
DROP TABLE TestUnion1,TestUnion2
Thursday, September 07, 2006
SQL Server 2005 Failover Clustering White Paper
Microsoft has published a comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services
Overview
This white paper is intended for a technical audience and not technical decision makers. It complements the existing documentation around planning, implementing, and administering of a failover cluster that can be found in Microsoft SQL Server 2005 Books Online. To ease the upgrade process for existing users of failover clustering, this white paper also points out differences in the failover clustering implementation of SQL Server 2005 compared to SQL Server 2000.
Get it here
Overview
This white paper is intended for a technical audience and not technical decision makers. It complements the existing documentation around planning, implementing, and administering of a failover cluster that can be found in Microsoft SQL Server 2005 Books Online. To ease the upgrade process for existing users of failover clustering, this white paper also points out differences in the failover clustering implementation of SQL Server 2005 compared to SQL Server 2000.
Get it here
Kalen Delaney Has Finished Inside SQL Server 2005: The Storage Engine And Is Also Blogging On SQLblog.com
Some good news that I am very excited about; Kalen Delaney has finished Inside SQL Server 2005: The Storage Engine. I have already pre-ordered her book but will have to wait until November 8, 2006 when it will ship (hopefully). I have her 2000 edition and it's my favorite book together with Ken Henderson's Guru series. Kalen also has started to blog on SQLblog.com So what am I currently reading and what else am I going to buy.
Currently I am reading a very good SQL book by Louis Davidson named Pro SQL Server 2005 Database Design and Optimization. I hope to be done by the time Inside SQL Server 2005: The Storage Engine ships, I should be if the kids let me. Pro SQL Server 2005 Database Design and Optimization is a very good book and starts from Data Model and goes all the way to Database Interoperability. some other things covered are Protecting the Integrity of Your Data,Table Structures and Indexing,Coding for Concurrency
This book does also a very good job of explaining Codd’s 12 Rules for an RDBMS
What am I going to buy next?
Next book on my list is Expert SQL Server 2005 Development by Adam Machanic. I like the chapters that Adam wrote in Pro SQL server 2005, I like what he does in newsgroups and I like his blog. So that is enough for me to check out the book
After that I will buy SQL Server 2005 Practical Troubleshooting: The Database Engine by Ken Henderson which will be published December 5, 2006 (Sinterklaas dag for all you Dutch people)
I have 3 of Ken's books and I will get this one and the follow up to The Guru's Guide to SQL Server Stored Procedures, XML, and HTML which will be published May 31, 2007
So I went a little overboard with the links, this post has more blue characters than black ones.
So what is on your list and what are you currently reading?
I am also interested in getting A Developer's Guide to SQL Server 2005 by Bob Beauchemin. We will see; if I finish these books and the others are not published yet then I will. I did not have this problem when I used to take the Amtrak/NJ Transit train from Princeton to New York City (lots of time to read). Right now I work and live in Princeton and my commute is about 8 minutes
Wednesday, September 06, 2006
Microsoft SQL Server 2005 Everywhere Edition Access Database Synchronizer
Microsoft SQL Server 2005 Everywhere Edition Access Database Synchronizer provides a way to synchronize data between Microsoft Access database on a desktop and Microsoft SQL Server 2005 Everywhere Edition database on a device.
The setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device. It also includes a read me file which has the documentation for the solution and a sample application. The sample application shows how the solution works and how to write applications for this solution. The components installed on the desktop can be used by third party applications to provide data synchronization between Microsoft Access database on the desktop and SQL Server Everywhere/SQL Mobile database on the device.
Download it here
The setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device. It also includes a read me file which has the documentation for the solution and a sample application. The sample application shows how the solution works and how to write applications for this solution. The components installed on the desktop can be used by third party applications to provide data synchronization between Microsoft Access database on the desktop and SQL Server Everywhere/SQL Mobile database on the device.
Download it here
Tuesday, September 05, 2006
Count Those Parentheses
This was a question on the microsoft.public.sqlserver.programming newsgroup, I thought it would be interesting to you to see wat i answered to this one
I believe that I have never used this many parenthese in my life before in a simple 2 column split
This is the question:
I have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:
245 HELIUM, COMPRESSED 2.2 UN1046
I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
Here is the link to the original question at the microsoft.public.sqlserver.programming newsgroup
And here is my solution
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )
SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory
I believe that I have never used this many parenthese in my life before in a simple 2 column split
This is the question:
I have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:
245 HELIUM, COMPRESSED 2.2 UN1046
I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
Here is the link to the original question at the microsoft.public.sqlserver.programming newsgroup
And here is my solution
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )
SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory
SQL Server 2005 And SOA (Service-Oriented Architecture)
A white paper written by Don Kiely about one of the biggest buzzwords of this moment: SOA, Ruby on Rails is the other of course.
Here is an excerpt from the introduction: "The dominant client-server and n-tier application architectures of the 1990s ran into serious scalability and availability issues when used to implement massive Internet e-commerce sites. One of the major problems is that data tended to be stored in a massive, centralized database that all client components had direct access to. Virtually all communication with the database was in the form of SQL statements or batches of statements in a stored procedure, so that the client received a set of data for the specific task at hand.
Other problems arose when trying to incorporate “legacy” systems into newer applications. After decades of deploying a wide variety of systems using various proprietary technologies and platforms, the world was awash in systems that did their job perfectly well but had no clear path to interact with other applications in an increasingly connected environment. Achieving the agility needed by today’s applications has been extremely difficult. Business-to-business (B2B) interactions complicate things even further, requiring standard and reliable ways of conducting business electronically. Clearly, evolving systems that meet the needs of today’s global"
Read the complete white paper here: How SQL Server 2005 Enables Service-Oriented Database Architectures
Here is an excerpt from the introduction: "The dominant client-server and n-tier application architectures of the 1990s ran into serious scalability and availability issues when used to implement massive Internet e-commerce sites. One of the major problems is that data tended to be stored in a massive, centralized database that all client components had direct access to. Virtually all communication with the database was in the form of SQL statements or batches of statements in a stored procedure, so that the client received a set of data for the specific task at hand.
Other problems arose when trying to incorporate “legacy” systems into newer applications. After decades of deploying a wide variety of systems using various proprietary technologies and platforms, the world was awash in systems that did their job perfectly well but had no clear path to interact with other applications in an increasingly connected environment. Achieving the agility needed by today’s applications has been extremely difficult. Business-to-business (B2B) interactions complicate things even further, requiring standard and reliable ways of conducting business electronically. Clearly, evolving systems that meet the needs of today’s global"
Read the complete white paper here: How SQL Server 2005 Enables Service-Oriented Database Architectures
Friday, September 01, 2006
SQL Server 2005 Best Practices Analyzer Coming Soon??
Microsoft Events has the following TechNet Webcast: Using the SQL Server Upgrade Advisor and New SQL Server 2005 Best Practices Analyzer Tools (Level 200)
Start Time: Thursday, September 07, 2006 9:30 AM Pacific Time (US & Canada)
End Time: Thursday, September 07, 2006 11:00 AM Pacific Time (US & Canada)
Description: Do you have plans to upgrade to Microsoft SQL Server 2005 in the near future? In this presentation, we describe two valuable tools from Microsoft that can help you identify and address potential issues proactively for a smoother upgrade experience. The Microsoft SQL Server 2005 Upgrade Advisor analyzes existing instances of Microsoft SQL Server 7.0 and SQL Server 2000, identifies feature and configuration changes that might affect your upgrade, and provides links to documentation that describes each issue and how to resolve it. The new SQL Server 2005 Best Practices Analyzer tool helps you ensure that SQL Server instances, databases, and SQL Server Integration Services (SSIS) packages adhere to Microsoft best practices. Attend this webcast to learn how to use these tools and how they can help you upgrade your SQL Server environment effectively and efficiently.
Presenter: Paul Mestemaker, Program Manager, Microsoft Corporation
So does this mean that the SQL Server 2005 Best Practices Analyzer is near completion? I don't know but I did ask the same question on the SQL Server Relational Engine Manageability Team Blog
And to give credit where credit is due I saw this first on Dis4ea's SQL Blog
Start Time: Thursday, September 07, 2006 9:30 AM Pacific Time (US & Canada)
End Time: Thursday, September 07, 2006 11:00 AM Pacific Time (US & Canada)
Description: Do you have plans to upgrade to Microsoft SQL Server 2005 in the near future? In this presentation, we describe two valuable tools from Microsoft that can help you identify and address potential issues proactively for a smoother upgrade experience. The Microsoft SQL Server 2005 Upgrade Advisor analyzes existing instances of Microsoft SQL Server 7.0 and SQL Server 2000, identifies feature and configuration changes that might affect your upgrade, and provides links to documentation that describes each issue and how to resolve it. The new SQL Server 2005 Best Practices Analyzer tool helps you ensure that SQL Server instances, databases, and SQL Server Integration Services (SSIS) packages adhere to Microsoft best practices. Attend this webcast to learn how to use these tools and how they can help you upgrade your SQL Server environment effectively and efficiently.
Presenter: Paul Mestemaker, Program Manager, Microsoft Corporation
So does this mean that the SQL Server 2005 Best Practices Analyzer is near completion? I don't know but I did ask the same question on the SQL Server Relational Engine Manageability Team Blog
And to give credit where credit is due I saw this first on Dis4ea's SQL Blog
Top SQL Server Google Searches For August 2006
These are the top SQL Searches on this site for the month of August I have left out searches that have nothing to do with SQL Server or programming (for example atlantic city escorts)
Here are the results...
dtsrun from sp
query multiple databases
first business day of each month query
Truncated table recovery
check constraint
dbcc report files
first business day of each month
String or binary data would be truncated.
SQL SELECT *
substr()
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
xp_fileexist
SQL SELECT WHERE DATE
CONCAT_NULL_YIELDS_NULL
check constrant
SQL 2000 parallel backup restore
dtsrun
Let's talk about a couple of these
query multiple databases
I covered that in this post
first business day of each month query
You really need to have a calendar table for this one. You can also use a number table and check for the min date where select datepart(dw,date) between 2 and 6 but what about holidays. A calendar table is your best bet. And I know just a place to get some code for that-->A way to load a calendar table
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
That can be found here: 2000 version, 2005 version
xp_fileexist
That is covered here
String or binary data would be truncated
And that was covered here
So there you have it, those were some of the searches and I covered some of that stuff already. I always like to look at the searches because it gives me ideas for future blog posts
Here are the results...
dtsrun from sp
query multiple databases
first business day of each month query
Truncated table recovery
check constraint
dbcc report files
first business day of each month
String or binary data would be truncated.
SQL SELECT *
substr()
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
xp_fileexist
SQL SELECT WHERE DATE
CONCAT_NULL_YIELDS_NULL
check constrant
SQL 2000 parallel backup restore
dtsrun
Let's talk about a couple of these
query multiple databases
I covered that in this post
first business day of each month query
You really need to have a calendar table for this one. You can also use a number table and check for the min date where select datepart(dw,date) between 2 and 6 but what about holidays. A calendar table is your best bet. And I know just a place to get some code for that-->A way to load a calendar table
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
That can be found here: 2000 version, 2005 version
xp_fileexist
That is covered here
String or binary data would be truncated
And that was covered here
So there you have it, those were some of the searches and I covered some of that stuff already. I always like to look at the searches because it gives me ideas for future blog posts
Top 5 Posts For The Month Of August
Below are the top 5 posts according to Google Analytics for the month of August
Here are the posts in order by pageviews descending
Store The Output Of A Stored Procedure In A Table Without Creating A Table
6 Different Ways To Get The Current Identity Value From A Table
COALESCE And ISNULL Differences
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
And I have also updated the Top 10 Articles of all time
Here are the posts in order by pageviews descending
Store The Output Of A Stored Procedure In A Table Without Creating A Table
6 Different Ways To Get The Current Identity Value From A Table
COALESCE And ISNULL Differences
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
And I have also updated the Top 10 Articles of all time
Thursday, August 31, 2006
sys.dm_exec_sessions
As I promised earlier today, here is the first of many posts about the Dynamic Management Views in SQL Server 2005.
What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant
Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status
Let's list all SPID's
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
Let's just grab our own SPID
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Now let's query all user sessions
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0
Then for the server you would change 0 to 1
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level
Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results vertically
SELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt
Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
It's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself
Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description
sys.dm_exec_sessions
session_id smallint
Identifies the session associated with each active primary connection.
login_time datetime
Time when session was established.
host_name nvarchar(128)
Host associated with the session.
program_name nvarchar(128)
Program associated with the session.
host_process_id int
Process ID associated with the session.
client_version int
Version of the interface used by the client to connect to the Server.
client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.
security_id varbinary(85)
Microsoft Windows security ID associated with the login.
login_name nvarchar(128)
SQL Login Name associated with the session.
nt_domain nvarchar(128)
Domain from which session connection was made.
nt_user_name nvarchar(128)
Name of the user associated with the session.
status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state
context_info varbinary(128)
CONTEXT_INFO value for the session.
cpu_time int
CPU time, in milliseconds, that was used by this session.
memory_usage int
Number of 8-KB pages of memory used by this session.
total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time int
Time, in milliseconds, since the session was established.
endpoint_id int
ID of the Endpoint associated with the session.
last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.
last_request_end_time datetime
Time of the last completion of a request on the session.
reads bigint
Number of reads performed, by requests in this session, during this session.
writes bigint
Number of writes performed, by requests in this session, during this session.
logical_reads bigint
Number of logical reads that have been performed on the session.
is_user_process bit
0 if the session is a system session. Otherwise, it is 1.
text_size int
TEXTSIZE setting for the session.
language nvarchar(128)
LANGUAGE setting for the session.
date_format nvarchar(3)
DATEFORMAT setting for the session.
date_first smallint
DATEFIRST setting for the session.
quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.
arithabort bit
ARITHABORT setting for the session.
ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults bit
ANSI_DEFAULTS setting for the session.
ansi_warnings bit
ANSI_WARNINGS setting for the session.
ansi_padding bit
ANSI_PADDING setting for the session.
ansi_nulls bit
ANSI_NULLS setting for the session.
concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot
lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority int
DEADLOCK_PRIORITY setting for the session.
row_count bigint
number of rows returned on the session up to this point.
prev_error int
ID of the last error returned on the session.
So that's it for today, the view contains a lot more than I covered. i might expand this if I have time. If you have any suggestions leave me a comment or drop me an Email
The link to the post with all the views is here (as of today this is the only view that is covered)
What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant
Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status
Let's list all SPID's
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
Let's just grab our own SPID
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Now let's query all user sessions
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0
Then for the server you would change 0 to 1
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level
Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results vertically
SELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt
Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
It's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself
Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description
sys.dm_exec_sessions
session_id smallint
Identifies the session associated with each active primary connection.
login_time datetime
Time when session was established.
host_name nvarchar(128)
Host associated with the session.
program_name nvarchar(128)
Program associated with the session.
host_process_id int
Process ID associated with the session.
client_version int
Version of the interface used by the client to connect to the Server.
client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.
security_id varbinary(85)
Microsoft Windows security ID associated with the login.
login_name nvarchar(128)
SQL Login Name associated with the session.
nt_domain nvarchar(128)
Domain from which session connection was made.
nt_user_name nvarchar(128)
Name of the user associated with the session.
status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state
context_info varbinary(128)
CONTEXT_INFO value for the session.
cpu_time int
CPU time, in milliseconds, that was used by this session.
memory_usage int
Number of 8-KB pages of memory used by this session.
total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.
total_elapsed_time int
Time, in milliseconds, since the session was established.
endpoint_id int
ID of the Endpoint associated with the session.
last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.
last_request_end_time datetime
Time of the last completion of a request on the session.
reads bigint
Number of reads performed, by requests in this session, during this session.
writes bigint
Number of writes performed, by requests in this session, during this session.
logical_reads bigint
Number of logical reads that have been performed on the session.
is_user_process bit
0 if the session is a system session. Otherwise, it is 1.
text_size int
TEXTSIZE setting for the session.
language nvarchar(128)
LANGUAGE setting for the session.
date_format nvarchar(3)
DATEFORMAT setting for the session.
date_first smallint
DATEFIRST setting for the session.
quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.
arithabort bit
ARITHABORT setting for the session.
ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.
ansi_defaults bit
ANSI_DEFAULTS setting for the session.
ansi_warnings bit
ANSI_WARNINGS setting for the session.
ansi_padding bit
ANSI_PADDING setting for the session.
ansi_nulls bit
ANSI_NULLS setting for the session.
concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.
transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot
lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.
deadlock_priority int
DEADLOCK_PRIORITY setting for the session.
row_count bigint
number of rows returned on the session up to this point.
prev_error int
ID of the last error returned on the session.
So that's it for today, the view contains a lot more than I covered. i might expand this if I have time. If you have any suggestions leave me a comment or drop me an Email
The link to the post with all the views is here (as of today this is the only view that is covered)
Dynamic Management Views Blog Post Series Coming To A Screen Near You
Dynamic Management Views Blog Post Series Coming To A Screen Near You
Since I don't always know what to write I decided to give myself enough material for at least 6 months (if I cover 1 view a week). So here it is I will write about the Dynamic Management Views in SQL server 2005. The first view that I will cover is sys.dm_exec_sessions. You can use sys.dm_exec_sessions instead of DBCC USEROPTIONS only it's much better since you can query multiple SPID's
I will have the first post ready about 4:30 PM EST today
Here is a list of all the Dynamic Management Views in SQL Server 2005, I will link the ones that I write about to the post so that you can access all of this from 1 post (in theory)
Common Language Runtime Related Dynamic Management Views
sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring Related Dynamic Management Views
sys.dm_db_mirroring_connections
Database Related Dynamic Management Views
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
Execution Related Dynamic Management Views and Functions
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_plan_attributes
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions added on 2006-08-31
sys.dm_exec_sql_text
Full-Text Search Related Dynamic Management Views
sys.dm_fts_active_catalogs
sys.dm_fts_crawls
sys.dm_fts_crawl_ranges
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
Index Related Dynamic Management Views and Functions
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats added on 2006-09-10
I/O Related Dynamic Management Views and Functions
sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Query Notifications Related Dynamic Management Views
sys.dm_qn_subscriptions
Replication Related Dynamic Management Views
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Service Broker Related Dynamic Management Views
sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Operating System Related Dynamic Management Views
sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_performance_counters
sys.dm_os_cluster_nodes
sys.dm_os_schedulers
sys.dm_os_hosts
sys.dm_os_stacks
sys.dm_os_latch_stats
sys.dm_os_sys_info
sys.dm_os_loaded_modules
sys.dm_os_tasks
sys.dm_os_memory_cache_clock_hands
sys.dm_os_threads
sys.dm_os_memory_cache_counters
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_entries
sys.dm_os_wait_stats
sys.dm_os_memory_cache_hash_tables
sys.dm_os_waiting_tasks
sys.dm_os_memory_clerks
sys.dm_os_workers
sys.dm_os_memory_objects
Transaction Related Dynamic Management Views and Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store
Since I don't always know what to write I decided to give myself enough material for at least 6 months (if I cover 1 view a week). So here it is I will write about the Dynamic Management Views in SQL server 2005. The first view that I will cover is sys.dm_exec_sessions. You can use sys.dm_exec_sessions instead of DBCC USEROPTIONS only it's much better since you can query multiple SPID's
I will have the first post ready about 4:30 PM EST today
Here is a list of all the Dynamic Management Views in SQL Server 2005, I will link the ones that I write about to the post so that you can access all of this from 1 post (in theory)
Common Language Runtime Related Dynamic Management Views
sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring Related Dynamic Management Views
sys.dm_db_mirroring_connections
Database Related Dynamic Management Views
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
Execution Related Dynamic Management Views and Functions
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_plan_attributes
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions added on 2006-08-31
sys.dm_exec_sql_text
Full-Text Search Related Dynamic Management Views
sys.dm_fts_active_catalogs
sys.dm_fts_crawls
sys.dm_fts_crawl_ranges
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
Index Related Dynamic Management Views and Functions
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats added on 2006-09-10
I/O Related Dynamic Management Views and Functions
sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Query Notifications Related Dynamic Management Views
sys.dm_qn_subscriptions
Replication Related Dynamic Management Views
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Service Broker Related Dynamic Management Views
sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Operating System Related Dynamic Management Views
sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_performance_counters
sys.dm_os_cluster_nodes
sys.dm_os_schedulers
sys.dm_os_hosts
sys.dm_os_stacks
sys.dm_os_latch_stats
sys.dm_os_sys_info
sys.dm_os_loaded_modules
sys.dm_os_tasks
sys.dm_os_memory_cache_clock_hands
sys.dm_os_threads
sys.dm_os_memory_cache_counters
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_entries
sys.dm_os_wait_stats
sys.dm_os_memory_cache_hash_tables
sys.dm_os_waiting_tasks
sys.dm_os_memory_clerks
sys.dm_os_workers
sys.dm_os_memory_objects
Transaction Related Dynamic Management Views and Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store
Wednesday, August 30, 2006
A SQL Super Hero Is Born
Ken Henderson promised to give away a signed book to the person who made the best Celko superheroTom Øyvind Hogstad has created one on his blog and I think it looks pretty good, even the S in the logo can stay; we can assume it stands for SQL
Below you will find my favorite Celko answer ever
>> how to set the PK in SS Mgmt Studio ? <<
Who cares?? You are not not a real SQL programmer!! You are a "mousey, mousey, click , click" non-programmer. (with a French accent) we spit on you, Video gamer! to be serious, real programmers use a text editor. They know the language they write in. Those stinking "video game tools"slow us down. And they lead us to ask questiosn like this in newsgroups where people liek me will maek fun of you.
You can find more of these Celko ‘answers on Joe Celko The SQL Apprentice
Tuesday, August 29, 2006
Visual Studio 2005 Team Edition For Database Professionals Review On Regdeveloper
The Register has a review on their subsite Regdeveloper about Visual Studio 2005 Team Edition For Database Professionals (also know as Data Dude) There are 3 pictures so that you can see what the tool looks like if you are afraid to install the latest Community Technology Preview on your system( you can get the latest release here )The review is very positive about the tool and this will be one of those tools that you have to have in your shop, just as is the case with SQL Compare and SQL LiteSpeed right now.
There is one little problem with the review in the following sentence: "DD was partially the brainchild of Ewan Garden and Gert Drapers" they managed to spell Euan Garden's name wrong
Read the review here
Subscribe to:
Posts (Atom)