That's right it's here. Download it now and play around with vardecimal and custom reports for Management Studio
http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en
Wednesday, December 20, 2006
Tuesday, December 19, 2006
How Well Do You Interview And Do You Use Wizard Driven Programming?
We were interviewing people for developer and architect positions. The developer position required about 5 years C# and SQL Server. We got a couple of good candidates but some of them were really terrible. They claim SQL server experience since 1998 but don't know what a clustered index is or they can not name even one isolation level. There were people who had 10 years experience in web development but could not explain what MVC (Model-view-controller) is or name a design pattern. People created webservices but did not know what a WSDL is?????
You see this is the problem I have with WDP (Wizard Driven Programming); It's okay to use wizards but you HAVE to know what is going on behind the scenes. It's okay to use Enterprise Manager but you have to know how to do the same thing in Query Analyzer. If you never write your own scripts you will sooner or later come to a shop where they don't use these tools and changes have to be submitted via scripts. Not only do you have to script everything you also have to provide back-out scripts in case something goes wrong.
So here are some of the questions I asked; with 5 years experience I think you should not miss more than two (probably traceflag 1204 and parameter sniffing questions)
Let me know how many you couldn't get without looking them up
What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
Between index scan, index seek and table scan; which one is fastest and which one is slowest
How do you return a value from a proc
How do you return a varchar value from a proc
If I have a column that will only have values between 1 and 250 what data type should I use
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date
Which date format is the only safe one to use when passing dates as strings
How do you suppress rows affected messages when executing an insert statement
Can you name the 4 isolation levels in SQL Server 2000
How would you select all last names that start with S
How would you select all rows where the date is 20061127
What is horizontal partitioning
What does schemabinding do
How do you test for nulls
Name some differences between isnull and coalesce
What is a temp table
what is the difference between a local and global temporary table
If you create a local temp table and then call a proc is the temp table available inside the proc
What is referential integrity
what is the fastest way to populate a table (performance wise)
using the method above what can you do to make it even faster
What data type should you use to store monetary values
What is a cascade delete
Name a couple of types of joins
What is a SQL injection
What is parameter sniffing
Name 2 differences between a primary key and UNIQUE Constraints
How do you ensure that SQL server will use an index
What does option fast (10) do
What is the difference between union and union all
What does trace flag 1204 do
You see this is the problem I have with WDP (Wizard Driven Programming); It's okay to use wizards but you HAVE to know what is going on behind the scenes. It's okay to use Enterprise Manager but you have to know how to do the same thing in Query Analyzer. If you never write your own scripts you will sooner or later come to a shop where they don't use these tools and changes have to be submitted via scripts. Not only do you have to script everything you also have to provide back-out scripts in case something goes wrong.
So here are some of the questions I asked; with 5 years experience I think you should not miss more than two (probably traceflag 1204 and parameter sniffing questions)
Let me know how many you couldn't get without looking them up
What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
Between index scan, index seek and table scan; which one is fastest and which one is slowest
How do you return a value from a proc
How do you return a varchar value from a proc
If I have a column that will only have values between 1 and 250 what data type should I use
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date
Which date format is the only safe one to use when passing dates as strings
How do you suppress rows affected messages when executing an insert statement
Can you name the 4 isolation levels in SQL Server 2000
How would you select all last names that start with S
How would you select all rows where the date is 20061127
What is horizontal partitioning
What does schemabinding do
How do you test for nulls
Name some differences between isnull and coalesce
What is a temp table
what is the difference between a local and global temporary table
If you create a local temp table and then call a proc is the temp table available inside the proc
What is referential integrity
what is the fastest way to populate a table (performance wise)
using the method above what can you do to make it even faster
What data type should you use to store monetary values
What is a cascade delete
Name a couple of types of joins
What is a SQL injection
What is parameter sniffing
Name 2 differences between a primary key and UNIQUE Constraints
How do you ensure that SQL server will use an index
What does option fast (10) do
What is the difference between union and union all
What does trace flag 1204 do
Sunday, December 17, 2006
BadVista: The Worst FUD You Will Ever Read.... St. Ignucious Has Gone Wild
Richard Stallman (RMS, St. Ignucious ) is at it again. let's see what the site says:
More interesting is the piece of garbage that they link to: 25 Shortcomings Of Vista
Here are some of the 'gems'
1. SMB2: Vista introduces a new variant of the SMB protocol called SMB2, which may pose problems for those connecting to non-Microsoft networks, such as Samba on Linux.
--Speculation and pure FUD
7. Five Versions: The array of Vista editions could prove to be three too many, and upgrades between versions remain an unknown.
8. Activation: The need to activate the product via the Web could prove to be a time-waster during mass deployments.
--Could prove? Could prove? Who writes this stuff? For mass deployments there is something called Key Management Service
Key Management Service
Your organization can host the Key Management Service (KMS) internally to automatically activate computers running Windows Vista. To use the KMS, you must have a minimum of 25 computers running Windows Vista that are connected together. Computers that have been activated through KMS will be required to reactivate by connecting to your organization's network at least every six months.
Currently the KMS software runs on a local computer running Windows Vista or the Microsoft Windows Server Code Name "Longhorn" operating system. In the future, it will run on the Microsoft Windows Server 2003 operating system.
9. Storage Space: With Vista taking as much as 10 Gbytes of hard drive space, big and fast hard drives will be a must.
--Last time I checked you can't really buy anything less than 100GB
10. Backup: See No. 9. Backing up desktops will take a great deal of space.
--So backup only your personal data.
18
Buried Controls
Many options and controls are further buried, requiring a half-dozen mouse clicks or more to get to. Network settings and display settings are offenders here.
--Some people said the same thing in WinXP, until they realized there is also a classic view. Vista has this classic view also.
19. Installation Can take hours on some systems. Upgrades are even slower.
--This is BS; it should not take more than 30 minutes unless you got a pirated copy on 1500 floppy discs
20. HHD Hybrid Hard Drives. These are potentially a huge performance booster, but there's little information and support is available (even though should be available).
--Since when is having an emerging technology that will boost performance a shortcoming?
#25:
WordPadAbility to open .doc files has been removed.
--Hahahahahaha who wrote this piece of crap. You either use Word or OpenOffice
The writer of this article is Frank J. Ohlhorst send him an email if you want to tell him what you think
The Free Software Foundation (FSF) today launched BadVista.org, a campaign
with a twofold mission of exposing the harms inflicted on computer users by the
new Microsoft Windows Vista and promoting free software alternatives that
respect users' security and privacy rights.
“Vista is an upsell masquerading
as an upgrade. It is an overall regression when you look at the most important
aspect of owning and using a computer: your control over what it does. Obviously
MS Windows is already proprietary and very restrictive, and well worth
rejecting. But the new 'features' in Vista are a Trojan Horse to smuggle in even
more restrictions. We'll be focusing attention on detailing how they work, how
to resist them, and why people should care”, said FSF program administrator John
Sullivan.
More interesting is the piece of garbage that they link to: 25 Shortcomings Of Vista
Here are some of the 'gems'
1. SMB2: Vista introduces a new variant of the SMB protocol called SMB2, which may pose problems for those connecting to non-Microsoft networks, such as Samba on Linux.
--Speculation and pure FUD
7. Five Versions: The array of Vista editions could prove to be three too many, and upgrades between versions remain an unknown.
8. Activation: The need to activate the product via the Web could prove to be a time-waster during mass deployments.
--Could prove? Could prove? Who writes this stuff? For mass deployments there is something called Key Management Service
Key Management Service
Your organization can host the Key Management Service (KMS) internally to automatically activate computers running Windows Vista. To use the KMS, you must have a minimum of 25 computers running Windows Vista that are connected together. Computers that have been activated through KMS will be required to reactivate by connecting to your organization's network at least every six months.
Currently the KMS software runs on a local computer running Windows Vista or the Microsoft Windows Server Code Name "Longhorn" operating system. In the future, it will run on the Microsoft Windows Server 2003 operating system.
9. Storage Space: With Vista taking as much as 10 Gbytes of hard drive space, big and fast hard drives will be a must.
--Last time I checked you can't really buy anything less than 100GB
10. Backup: See No. 9. Backing up desktops will take a great deal of space.
--So backup only your personal data.
18
Buried Controls
Many options and controls are further buried, requiring a half-dozen mouse clicks or more to get to. Network settings and display settings are offenders here.
--Some people said the same thing in WinXP, until they realized there is also a classic view. Vista has this classic view also.
19. Installation Can take hours on some systems. Upgrades are even slower.
--This is BS; it should not take more than 30 minutes unless you got a pirated copy on 1500 floppy discs
20. HHD Hybrid Hard Drives. These are potentially a huge performance booster, but there's little information and support is available (even though should be available).
--Since when is having an emerging technology that will boost performance a shortcoming?
#25:
WordPadAbility to open .doc files has been removed.
--Hahahahahaha who wrote this piece of crap. You either use Word or OpenOffice
The writer of this article is Frank J. Ohlhorst send him an email if you want to tell him what you think
Friday, December 15, 2006
Three Ways To Display Two Counts From a Table Side By Side
This question came up on tek-tips.
I want to count the CA and the UT states from the authors table and output this in different columns named CaCount and UtCount
CaCount UtCount
----------- -----------
15 2
How do you do such a thing? To display the following is very easy
state StateCount
----- -----------
CA 15
UT 2
You just do a count and group by
SELECT state,COUNT(*) AS StateCount
FROM authors
WHERE state in ('CA', 'UT')
GROUP BY state
How do we display them side by side? We can do this 3 different ways
1 stick the count in variables
2 run the count twice a s a separate query
3 sum with case
USE PUBS
GO
--stick the count in variables
DECLARE @UtCount INT
DECLARE @CaCount INT
SELECT @UtCount = COUNT(*) FROM authors WHERE state ='UT'
SELECT @CaCount = COUNT(*) FROM authors WHERE state ='CA'
SELECT @CaCount AS CaCount,@UtCount AS UtCount
--run the count twice as a separate query
SELECT (SELECT COUNT(*) FROM authors
WHERE state ='CA') AS CaCount,
(SELECT COUNT(*) FROM authors WHERE state ='UT') AS UtCount
--sum with case
SELECT SUM(CASE state WHEN 'CA' THEN 1 ELSE 0 END) AS CaCount,
SUM(CASE state WHEN 'UT' THEN 1 ELSE 0 END) AS UtCount
FROM authors
If you look at the execution plan then you will see that the best option is the third one (sum with case). The third option only queries the table once instead of twice like the other two solutions and will thus be twice as fast
So keep that in mind when you have to do such a query next time
[edit]
Oracle Version
jbjornson left the Oracle version in a comment; I decided to add it to the post. I don't know about the syntax coloring on Oracle/TOAD so you are getting a black and white only ;-(
--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT
--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors
[/edit]
I want to count the CA and the UT states from the authors table and output this in different columns named CaCount and UtCount
CaCount UtCount
----------- -----------
15 2
How do you do such a thing? To display the following is very easy
state StateCount
----- -----------
CA 15
UT 2
You just do a count and group by
SELECT state,COUNT(*) AS StateCount
FROM authors
WHERE state in ('CA', 'UT')
GROUP BY state
How do we display them side by side? We can do this 3 different ways
1 stick the count in variables
2 run the count twice a s a separate query
3 sum with case
USE PUBS
GO
--stick the count in variables
DECLARE @UtCount INT
DECLARE @CaCount INT
SELECT @UtCount = COUNT(*) FROM authors WHERE state ='UT'
SELECT @CaCount = COUNT(*) FROM authors WHERE state ='CA'
SELECT @CaCount AS CaCount,@UtCount AS UtCount
--run the count twice as a separate query
SELECT (SELECT COUNT(*) FROM authors
WHERE state ='CA') AS CaCount,
(SELECT COUNT(*) FROM authors WHERE state ='UT') AS UtCount
--sum with case
SELECT SUM(CASE state WHEN 'CA' THEN 1 ELSE 0 END) AS CaCount,
SUM(CASE state WHEN 'UT' THEN 1 ELSE 0 END) AS UtCount
FROM authors
If you look at the execution plan then you will see that the best option is the third one (sum with case). The third option only queries the table once instead of twice like the other two solutions and will thus be twice as fast
So keep that in mind when you have to do such a query next time
[edit]
Oracle Version
jbjornson left the Oracle version in a comment; I decided to add it to the post. I don't know about the syntax coloring on Oracle/TOAD so you are getting a black and white only ;-(
--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT
--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors
[/edit]
Visual Studio 2005 Service Pack 1 (SP1) Released
Visual Studio 2005 Service Pack 1 (SP1) has been released. From the site:
Get it here
Service Pack 1 addresses issues that were found through a combination of customers and partner feedback, as well as internal testing. The issues addressed range in severity from places where the syntax coloring was incorrect to customer reported crashes in various scenarios. In some areas, more than 50% of the bugs addressed were reported by customers through the MSDN Product Feedback Center and Microsoft Connect. Overall, Service Pack 1 offers customers improvements in responsiveness, stability and performance for Visual Studio 2005.
Through further advancement and feedback, Service Pack 1 also provides over 70 improvements for common development scenarios including:
- New processor support (e.g., Core Duo) for code generation and profiling
- Performance and scale improvements in Team Foundation Server
- Team Foundation Server integration with Excel 2007 and Project 2007
- Tool support for occasionally connected devices and SQL Server Compact Edition
- Additional support for project file based Web applications
- Windows Embedded 6.0 platform and tools support
Get it here
Wednesday, December 13, 2006
Index Build Strategy In SQL Server
The Tips, Tricks, and Advice from the SQL Server Query Processing Team blog has 3 really good posts about Index Build strategy in SQL Server. You should definitely read these to really understand how to implement that. Here are the links:
Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))
Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning
Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))
Problems With ROW_NUMBER() In A WHERE Clause
This is a question that came up today. A person had a query like this
USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address
WHERE rowNum > 3;
That gave the following error
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'rowNum'.
What you have to do is use Common Table Expressions or use a subquery. Below are both methods.
--Subquery
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address) AS x
WHERE rowNum > 3;
--CTE
WITH x (rowNum,addressline1,
city) AS
(SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address)
SELECT * FROM X
WHERE rowNum > 3;
USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address
WHERE rowNum > 3;
That gave the following error
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name 'rowNum'.
What you have to do is use Common Table Expressions or use a subquery. Below are both methods.
--Subquery
SELECT * FROM ( SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address) AS x
WHERE rowNum > 3;
--CTE
WITH x (rowNum,addressline1,
city) AS
(SELECT
ROW_NUMBER() OVER (ORDER BY addressline1) AS rowNum,
addressline1,
city
FROM person.address)
SELECT * FROM X
WHERE rowNum > 3;
Tuesday, December 12, 2006
SQL Server 2005 Practical Troubleshooting: The Database Engine Is Shipping!
Amazon is shipping SQL Server 2005 Practical Troubleshooting: The Database Engine. This is a must have for all SQL developers who run into any kind of problems with SQL Server 2005For all of you who did not read my interview with the editor of this book Ken Henderson; you can find that here; Ken describes the target audience for the book the most important chapters for customers who have performance problems and much more
A sample chapter is available: Chapter 4 Procedure Cache Issues
About the book:
Paperback: 456 pages
Publisher: Addison-Wesley; 1ST edition
Language: English
ISBN: 0321447743
Contents
Preface
Chapter 1 Waiting and Blocking Issues
Chapter 2 Data Corruption and Recovery Issues
Chapter 3 Memory Issues
Chapter 4 Procedure Cache Issues
Chapter 5 Query Processor Issues
Chapter 6 Server Crashes and Other Critical Failures
Chapter 7 Service Broker Issues
Chapter 8 SQLOS and Scheduling Issues
Chapter 9 Tempdb Issues
Chapter 10 Clustering Issues
Index
Amazon Links:
Visual Studio Team Edition for Database Professionals links
By now you know that Visual Studio Team Edition for Database Professionals has been released. But do you know all the links to the newsgroup, documentation and download page? No? No problem here are the links:
Download Link
The product is available on the MSDN subscribers site, or you can download the trial version from he following URL :http://www.microsoft.com/downloads/details.aspx?FamilyId=7DE00386-893D-4142-A778-992B69D482AD&displaylang=en
For the trial version, you still need an installed copy of Visual Studio 2005 Team Suite which can be either a trial or a licensed version
Documentation
Team Edition for Database Professionals
Forum
Visual Studio Team System - Database Professionals
Visual Studio 2005 Team Edition for Database Professionals Site
Data Dude
Download Link
The product is available on the MSDN subscribers site, or you can download the trial version from he following URL :http://www.microsoft.com/downloads/details.aspx?FamilyId=7DE00386-893D-4142-A778-992B69D482AD&displaylang=en
For the trial version, you still need an installed copy of Visual Studio 2005 Team Suite which can be either a trial or a licensed version
Documentation
Team Edition for Database Professionals
Forum
Visual Studio Team System - Database Professionals
Visual Studio 2005 Team Edition for Database Professionals Site
Data Dude
Sunday, December 10, 2006
Wedding Anniversary
So here we are 9 years later. We are still married have 3 kids and bought our second home this year. So let me give you a little background of how I met my wife etc etc. I met my wife in a bar on the upper eastside in New York City. I don’t remember the name of the bar but I do remember that we went to Elaine’s for a drink and also to a place named the Cowboy Bar. Fast forward to the wedding date; we got married twice first at City Hall on December 11th and then we did a church wedding on August 15th (the pictures are from that day) We did the December wedding separately so that we could start my Green Card process; my wife is a third generation Italian-American but I had Dutch and Croatian citizenships.
The wedding was 1 block from were we lived. My wife (to be) spent the night in a hotel with her sisters. The wedding started at 3; I was still rollerblading till 2:30 in Central Park. I took a shower got dressed and walked to the church (which was 1 block from my apartment)
5% of the wedding guests did jail time.
The reception was held at the Kitano Hotel which is located at 38th and Park Avenue. I did not have a huge wedding only 65 or so family members and some of our closest friends were invited. Out of those people there were 3 who did some jail time. One of them is Brendan who was the head of the NYC Hells Angels chapter at that time.
Another was brought by a friend and the last one was a friend of mine who did jail time because he beat up a cop. The Hells Angel did not dress up of course, if you look at the picture you can see me next to my wife. The other picture is me and my wife on the roof. The reception had 2 huge balconies; one was facing the Empire State Building and the other one was facing the Chrysler Building. The good thing when having your wedding at a hotel is that you can just walk to your room.
Honeymoon in Hawaii
So after the reception we had two days to get ready for the honeymoon. We did our shopping and on Monday night we went to China Club with out friend from Europe. China Club is the place to go to on Mondays; you can always spot some model or one of the Yankees or Knicks there. The next day we went to Maui and Kuai. I have said many times to my wife that I want to go back to Hawaii once before I die…. Maui and Kuai were beautiful places to spend your honeymoon; if you are into playing golf, surfing, hiking, snorkeling then there are lots of things to do here. Jurassic Park was shot in Kuai and once you arrive in Kuai you will recognize the green scenery right away
After the honeymoon
Once you are married don’t have kids right away; your live will change and you won’t be able to do anything for a while. So that is what we did; we waited 4 years to have kids. We visited Paris and Amsterdam and bought a house near Princeton before having our first son Christian. Then my wife became pregnant with twins and we had to sell our house and buy a bigger house. If owing you house is the American Dream then selling and buying your next house has to be the American Nightmare; what a stressful situation. After that ordeal I said that the next time I move will be in one of those 55+ community houses (I still have about 20 years to go). So today we live in Princeton and we have Catherine and Nicholas to keep us awake at night.
Traditions
My wife and I usually buy our Christmas tree on our anniversary. In the city we would pick up a Charlie Brown on 2nd avenue and 79th street; out here we go to the farm and chop down our own. Somehow my wife thinks it is also a tradition to watch Serendipity; if I have to watch a movie with Kate Beckingsale I prefer Underworld but hey you have to be nice on your anniversary….
BTW I still fit in my wedding suit, the tailor was wrong ;-(
Friday, December 08, 2006
Plan For The Day…Gone Wrong In a Big Way
It is my 9th year wedding anniversary this weekend so I took the day off today and below is a list of things I planned to do today
[ ] Get a Christmas tree at the farm
[ ] Go for breakfast with the whole family
[ ] Drop of a package at the post office
[X] Interview a person for a position at work
[ ] Enhance a Stored Procedure and close the item in BugTrax
[ ] Watch Serendipity (my wife insists)
[ ] Get haircuts
[X] Cover the furniture in the garden
As you can see I managed to accomplish two things only so far. What happened? My son got sick and had a high fever and threw up this morning; that eliminated the first 3 items right away. I managed to do the phone interview because it was a reversed phone interview (the candidate was at the office and I interviewed him from home over the phone)
Forget about the haircuts (I rescheduled for tomorrow); where do I leave our 3 kids?
So that leaves me with watching Serendipity tonight ;-(
Anyway I will post something about my wedding this weekend. Believe it or not 5% of the people that came to my wedding did jail time; one of them was the head of the NYC Hells Angels chapter( I will also post some pictures this weekend)
Oh you wanted some SQL material? No problem this will keep you busy for a while ;-)
Log Buffer #21: A Carnival of the Vanities for DBAs
Log Buffer #22: A Carnival of the Vanities for DBAs
[ ] Get a Christmas tree at the farm
[ ] Go for breakfast with the whole family
[ ] Drop of a package at the post office
[X] Interview a person for a position at work
[ ] Enhance a Stored Procedure and close the item in BugTrax
[ ] Watch Serendipity (my wife insists)
[ ] Get haircuts
[X] Cover the furniture in the garden
As you can see I managed to accomplish two things only so far. What happened? My son got sick and had a high fever and threw up this morning; that eliminated the first 3 items right away. I managed to do the phone interview because it was a reversed phone interview (the candidate was at the office and I interviewed him from home over the phone)
Forget about the haircuts (I rescheduled for tomorrow); where do I leave our 3 kids?
So that leaves me with watching Serendipity tonight ;-(
Anyway I will post something about my wedding this weekend. Believe it or not 5% of the people that came to my wedding did jail time; one of them was the head of the NYC Hells Angels chapter( I will also post some pictures this weekend)
Oh you wanted some SQL material? No problem this will keep you busy for a while ;-)
Log Buffer #21: A Carnival of the Vanities for DBAs
Log Buffer #22: A Carnival of the Vanities for DBAs
Thursday, December 07, 2006
Get The Domain Name Of Your SQL Server Machine
How do you get the domain name that your SQL Server machine belongs to with T-SQL code?
You can use the undocumented procedure xp_regread; I have tested this on SQL Server 2005 and on SQL Server 2000.
Be advised that since this is an undocumented procedure that it might not be available in the future or it might be changed either because of a service pack or a hot fix. Don't use it as production code!
Here is how to do this
DECLARE @chvDomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon',
N'CachePrimaryDomain',@chvDomainName OUTPUT
SELECT @chvDomainName AS DomainName
You can use the undocumented procedure xp_regread; I have tested this on SQL Server 2005 and on SQL Server 2000.
Be advised that since this is an undocumented procedure that it might not be available in the future or it might be changed either because of a service pack or a hot fix. Don't use it as production code!
Here is how to do this
DECLARE @chvDomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon',
N'CachePrimaryDomain',@chvDomainName OUTPUT
SELECT @chvDomainName AS DomainName
Monday, December 04, 2006
Visual Studio 2005 Team Edition for Database Professionals Released to Manufacturing
On Friday 1st December 2006 the development team officially announced that Data Dude was done and was being released to manufacturing. It should be available for download to MSDN subscribers on December 7th 2006 and generally available for purchase on January 1st 2007.
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
Sunday, December 03, 2006
Top 5 Posts For November 2006
Below are the top 5 posts according to Google Analytics for the month of November 2006 in order by pageviews descending
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
Top SQL Server Google Searches For November 2006
These are the top SQL Searches on this site for the month of November. this time I left some of the searches that have nothing to do with SQL Server or programming (Like breeds of cats that produce less FEL D1s)
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Tuesday, November 28, 2006
Use SCHEMABINDING In Views To Protect Against Alteration Or Dropping Of Tables
My post from last night triggered something in me to write a little bit about SCHEMABINDING.
If you create a view and someone drops one of the tables that the view is dependent on then the view will fail. How can you protect yourself against that? You can use WITH SCHEMABINDING. Here is how you use it:
Let's first create some tables and the view
CREATE TABLE dbo.Test1 (ID INT, SomeName VARCHAR(49))
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test1 VALUES 1,'Name1')
INSERT dbo.Test1 VALUES( 2,'Name2')
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
CREATE VIEW dbo.TestTables
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--SELECT from the view and everything is fine
SELECT *
FROM dbo.TestTables
-- Drop table test2
DROP TABLE dbo.Test2
--Let's do the select again
SELECT * FROM dbo.TestTables
--And this is the error message that we get back
Server: Msg 208, Level 16, State 1, Procedure TestTables, Line 3
Invalid object name 'Test2'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'TestTables' because of binding errors.
--Let's create the Test2 table again
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
--Now let's alter the view and we will use SCHEMABINDING
ALTER VIEW dbo.TestTables WITH SCHEMABINDING
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--Try dropping the Test2 table again
DROP TABLE dbo.Test2
--And here is the message
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Test2' because it is being referenced by object 'TestTables'.
--Let's try altering the table by adding a column
ALTER TABLE dbo.Test2 ADD Col3 INT
-- That seems to work
SELECT * FROM dbo.Test2
SELECT * FROM dbo.TestTables
--Let's try altering the table by dropping a column
ALTER TABLE dbo.Test2 DROP COLUMN SomeOtherName
--Can't do that
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN SomeOtherName failed because one or more objects access this column.
--Let's try altering the table by changing a column size
ALTER TABLE dbo.Test2 ALTER COLUMN SomeOtherName VARCHAR(48)
--Same here; this is not allowed
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN SomeOtherName failed because one or more objects access this column.
--Let's drop the column that is not used by the view
ALTER TABLE dbo.Test2 DROP COLUMN Col3
--No problem here either, if a column is not used by the view it can be dropped
--Clean up the mess
DROP VIEW dbo.TestTables
DROP TABLE dbo.Test2, dbo.Test1
So there you have it; SCHEMABINDING is a nice way to protect your views from dropped tables, modified columns or dropped columns on tables that are used in views.
--You can add columns without a problem to the table used by a view.
--You can modify a column that is not used in a view.
--You can also drop a column that is not used in the view
If you create a view and someone drops one of the tables that the view is dependent on then the view will fail. How can you protect yourself against that? You can use WITH SCHEMABINDING. Here is how you use it:
Let's first create some tables and the view
CREATE TABLE dbo.Test1 (ID INT, SomeName VARCHAR(49))
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test1 VALUES 1,'Name1')
INSERT dbo.Test1 VALUES( 2,'Name2')
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
CREATE VIEW dbo.TestTables
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--SELECT from the view and everything is fine
SELECT *
FROM dbo.TestTables
-- Drop table test2
DROP TABLE dbo.Test2
--Let's do the select again
SELECT * FROM dbo.TestTables
--And this is the error message that we get back
Server: Msg 208, Level 16, State 1, Procedure TestTables, Line 3
Invalid object name 'Test2'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'TestTables' because of binding errors.
--Let's create the Test2 table again
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
--Now let's alter the view and we will use SCHEMABINDING
ALTER VIEW dbo.TestTables WITH SCHEMABINDING
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--Try dropping the Test2 table again
DROP TABLE dbo.Test2
--And here is the message
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Test2' because it is being referenced by object 'TestTables'.
--Let's try altering the table by adding a column
ALTER TABLE dbo.Test2 ADD Col3 INT
-- That seems to work
SELECT * FROM dbo.Test2
SELECT * FROM dbo.TestTables
--Let's try altering the table by dropping a column
ALTER TABLE dbo.Test2 DROP COLUMN SomeOtherName
--Can't do that
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN SomeOtherName failed because one or more objects access this column.
--Let's try altering the table by changing a column size
ALTER TABLE dbo.Test2 ALTER COLUMN SomeOtherName VARCHAR(48)
--Same here; this is not allowed
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN SomeOtherName failed because one or more objects access this column.
--Let's drop the column that is not used by the view
ALTER TABLE dbo.Test2 DROP COLUMN Col3
--No problem here either, if a column is not used by the view it can be dropped
--Clean up the mess
DROP VIEW dbo.TestTables
DROP TABLE dbo.Test2, dbo.Test1
So there you have it; SCHEMABINDING is a nice way to protect your views from dropped tables, modified columns or dropped columns on tables that are used in views.
--You can add columns without a problem to the table used by a view.
--You can modify a column that is not used in a view.
--You can also drop a column that is not used in the view
Monday, November 27, 2006
Do You Use Partitioned Views Or Indexed Views In SQL Server 2000
I am in the process of interviewing people for a position at work. The people I have interviewed so far have between 8 and 10 years SQL experience. I noticed one thing; none of these people knew what partition views or horizontal partitioning is or what is involved with creating an index view (the SET options, SCHEMABINDING etc etc). I must say that I was surprised that people who worked with SQL Server since 1998 didn’t know about isolation levels, audit trails, difference between a unique constraint and primary key and the list goes on
So here is my question to you: Do you use partitioned views or indexed views?
I must admit that I have created indexed views only twice on a production box. Partitioned views I am using a lot more since the table that we had grew to 180 million rows and you can query it on a combination of columns. Creating partitioned views is not a big deal but it involves some maintenance if you add years or whatever your partitioned column is. Then you have to create does tables and add them to the view. Luckily SQL Server 2005 has partitioned functions and this makes it a lot easier
So is this an unreasonable question? It’s not like I asked the difference between a conversion and a cycle deadlock is it?
So here is my question to you: Do you use partitioned views or indexed views?
I must admit that I have created indexed views only twice on a production box. Partitioned views I am using a lot more since the table that we had grew to 180 million rows and you can query it on a combination of columns. Creating partitioned views is not a big deal but it involves some maintenance if you add years or whatever your partitioned column is. Then you have to create does tables and add them to the view. Luckily SQL Server 2005 has partitioned functions and this makes it a lot easier
So is this an unreasonable question? It’s not like I asked the difference between a conversion and a cycle deadlock is it?
Saturday, November 25, 2006
SQL Server - Best Practices Site Available On TechNet
Microsoft TechNet has a page named SQL Server - Best Practices; this site has white papers, scrips a toolbox and top 10 lists
From the site:
Here is a preview of some of the material that is available right now:
Technical White Papers
Deep level technical papers on specific SQL Server topics that were tested and validated by SQL Development
• SQL Server 2005 Performance Tuning using Waits and Queues
• TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
• Loading Bulk Data into a Partitioned Table
• DBCC SHOWCONTIG Improvements and Comparison between SQL Server 2000 and SQL Server 2005
• Database Mirroring Best Practices and Performance Considerations
Best Practices ToolBox
• Scripts and tools for performance tuning and troubleshooting SQL Server 2005
Top 10 Lists
Summary lists (usually consisting of 10 items) of recommendations, best practices and common issues for specific customer scenarios by the SQL Server Customer Advisory Team.
• Storage Top 10 Best Practices
• OLTP Top 6 Performance Issues for OLTP Applications
Best Practices in SQL Server Books Online• Best Practices for Replication Administration
• Replication Security Best Practices
A must for every SQL Server developer: SQL Server - Best Practices
From the site:
Get the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. Drawing on the extensive experience and expertise from respected developers and engineers at Microsoft as they walk you through the specifics on solving particularly difficult issues.
Here is a preview of some of the material that is available right now:
Technical White Papers
Deep level technical papers on specific SQL Server topics that were tested and validated by SQL Development
• SQL Server 2005 Performance Tuning using Waits and Queues
• TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
• Loading Bulk Data into a Partitioned Table
• DBCC SHOWCONTIG Improvements and Comparison between SQL Server 2000 and SQL Server 2005
• Database Mirroring Best Practices and Performance Considerations
Best Practices ToolBox
• Scripts and tools for performance tuning and troubleshooting SQL Server 2005
Top 10 Lists
Summary lists (usually consisting of 10 items) of recommendations, best practices and common issues for specific customer scenarios by the SQL Server Customer Advisory Team.
• Storage Top 10 Best Practices
• OLTP Top 6 Performance Issues for OLTP Applications
Best Practices in SQL Server Books Online• Best Practices for Replication Administration
• Replication Security Best Practices
A must for every SQL Server developer: SQL Server - Best Practices
Friday, November 24, 2006
Auto Update Statistics Asynchronously.
In SQL Server 2000 if the engine determined that the statistics are out of date it would update the statistics and then run the query. If you had a very long table this could take a long time. In SQL server 2005 by turning Auto Update Statistics Asynchronously On the query will run with the old statistics which is not optimal but will probably still be faster than updating the statistics first on a large table and then running the query. The statistics will be updated after the query and the next queries can take advantage of the updated statistics.
Usage:
AUTO_UPDATE_STATISTICS_ASYNC { ON OFF }
ON
Queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
OFF
Queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
So how do you enable this? Here is an example for the AdventureWorks database however setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON.
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
What does Books On Line have to say about this?
Asynchronous Statistics Updates
A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.
In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version.
The AUTO_UPDATE_STATISTICS_ASYNC option is set at the database level and determines the update method for all statistics in the database. It is applicable only to statistics updating and cannot be used to asynchronously create statistics. Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON. By default, the AUTO_UPDATE_STATISTICS_ASYNC option is OFF
Usage:
AUTO_UPDATE_STATISTICS_ASYNC { ON OFF }
ON
Queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
OFF
Queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
So how do you enable this? Here is an example for the AdventureWorks database however setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON.
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
What does Books On Line have to say about this?
Asynchronous Statistics Updates
A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.
In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version.
The AUTO_UPDATE_STATISTICS_ASYNC option is set at the database level and determines the update method for all statistics in the database. It is applicable only to statistics updating and cannot be used to asynchronously create statistics. Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON. By default, the AUTO_UPDATE_STATISTICS_ASYNC option is OFF
Thursday, November 23, 2006
Happy Thanksgiving
This is not going to be a SQL post (again) So to have at least some SQL I am going to link to a post I did last year: Calculating Thanksgiving By Using SQL in SQL Server
So that’s that. I have some people coming over today and still have to cook all the stuff. The best part about thanksgiving besides the food and seeing the family is that we pick our secret Santa. Up until last year we would exchange with every family member and have a limit of $25. No we do it differently we pick one person from a hat and spend $125. This is much better because you only have to buy a gift for one person and you can get a nicer gift instead of several smaller gifts. So I usually give my list of Amazon links to the person and they can choose what to get me. Yes it’s not really a ‘secret’ Santa. I will post my list on Saturday so that you can see if I have any taste or not
Now another topic: weight gain. Yes the time between Thanksgiving and New Years day is the time when most people start putting on the pounds. A friend of mine has a blogpost named Fat America but I must warn you if you are a heavier person you might get offended with some of the language at the end of the post (the elevator part)
Anyway enjoy the food and drinks and have a good time
So that’s that. I have some people coming over today and still have to cook all the stuff. The best part about thanksgiving besides the food and seeing the family is that we pick our secret Santa. Up until last year we would exchange with every family member and have a limit of $25. No we do it differently we pick one person from a hat and spend $125. This is much better because you only have to buy a gift for one person and you can get a nicer gift instead of several smaller gifts. So I usually give my list of Amazon links to the person and they can choose what to get me. Yes it’s not really a ‘secret’ Santa. I will post my list on Saturday so that you can see if I have any taste or not
Now another topic: weight gain. Yes the time between Thanksgiving and New Years day is the time when most people start putting on the pounds. A friend of mine has a blogpost named Fat America but I must warn you if you are a heavier person you might get offended with some of the language at the end of the post (the elevator part)
Anyway enjoy the food and drinks and have a good time
Subscribe to:
Posts (Atom)