Below are some pictures, click on the pictures to get a bigger picture
Susan, Christian, Catherine and Nicholas
Nicholas
Catherine
Wednesday, July 19, 2006
A Father Again
I am a father again; my wife gave birth to 2 beautiful babies on Monday night. The boy is named Nicholas and the girl is name Catherine (yes just like Catherine the Great and Czar Nicholas II) I was working Monday and knew we were launching some new products this Wednesday. So I did all the work on staging on Monday and said to myself I will put it on the production box on Tuesday just in case my wife goes into labor. Well my wife didn’t feel that good on Monday and went in for a checkup and they decided to deliver the babies the same day. My son was with my wife so the person who I report to drove me to the hospital (I did not bring my keys to work) after that he drove me home so I could get some stuff and then he drove me back again (who else has a boss like that? ) A friend of ours came to the hospital to watch our son because my wife insisted that I had to be in the delivery room. My wife’s sisters were on vacation in Ocean City, they decided to visit that night and drove to the hospital. They arrived during the time that the babies were born. After the babies were born the sister took our son to our home and stayed there and I slept on the most comfortable (yeah right) hospital pull out chair. The sisters left on Tuesday and I went back home at 9PM. After putting my son to bed I logged in to my computer at work and worked for about an hour to move all the code over and to test that I didn’t mess up anything. My wife is still in the hospital but she will be coming home with the babies Friday morning. That’s it for now, next post will be SQL related again, probably sometime next week.
Below are some pictures, click on the pictures to get a bigger picture
Susan, Christian, Catherine and Nicholas
Nicholas
Catherine
Below are some pictures, click on the pictures to get a bigger picture
Susan, Christian, Catherine and Nicholas
Nicholas
Catherine
Monday, July 17, 2006
Flat File Bulk Import Speed Comparison In SQL Server 2005
Mladen has compared the following 4 methods for importing a flat file in SQl Server 2005
1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
Which do you think is the fastest? If you answered 1 or 2 you might be in for a surprise. Visit Mladen's blog to find out the answer
1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
Which do you think is the fastest? If you answered 1 or 2 you might be in for a surprise. Visit Mladen's blog to find out the answer
Three Ways To Get The Time Portion Of A Datetime Value
This popped up in a newsgroup today so I decided to do a little post about it. How do you get the time information only from a datetime value
These are at least 3 ways to do this
1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108
So here we go
--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))
--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)
--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)
These are at least 3 ways to do this
1) use the 3 different datepart functions and concatenate them
2) convert and grab the 8 right most characters
3) convert to varchar and use style 108
So here we go
--1 use the 3 different datepart functions and concatenate them
SELECT CONVERT(VARCHAR(2),DATEPART(HH,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(MI,GETDATE())) + ':' +
CONVERT(VARCHAR(2),DATEPART(S,GETDATE()))
--2 convert and grab the 8 right most characters
SELECT RIGHT(CONVERT(VARCHAR(22),GETDATE(),120),8)
--3 convert to varchar and use style 108
SELECT CONVERT(VARCHAR(12),GETDATE(),108)
Thursday, July 13, 2006
Visual Studio 2005 Team Edition for Database Professionals Interview On Channel 9
Channel 9 has a cool webcast with several people talking about Visual Studio 2005 Team Edition for Database Professionals (Data Dude)
From the site: "Cameron Skinner, Gert Drapers, Robert Merriman, Thomas Murphy, and Matt Nunn sat down me with to discuss a new edition to the Visual Studio Team product family: Visual Studio 2005 Team Edition for Database Professionals. We talked about why this product was created and Cameron (the product unit manager) gave a walk through of the current CTP release. Targeted for delivery at the end of the year, you can grab the CTP bits and learn more at the team’s website: http://msdn.microsoft.com/vstudio/teamsystem/dbpro/"
You can download the webcast here (http://channel9.msdn.com/Showpost.aspx?postid=212797) or click on the picture
Visual Studio 2005 Team Edition for Database Professionals CTP 4 Available
Visual Studio 2005 Team Edition for Database Professionals (Data Dude) CTP 4 is available for download
Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.
Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.
Get it here
Brief Description
Community Technology Preview (CTP) 4
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development.
Overview
Tools for building SQL databases in a managed project environment with support for versioning, deployment, unit testing, refactoring, and off-line SQL development. This release is focused on completing a full functionality scenario for SQL 2000 and includes an updated project system with reverse engineering, a new SQL Query Editor, Schema and Data Compare, Data Generation, Unit Testing, and Rename Refactoring. This release does not have functionality changes from CTP3, only stability work and the new project UI.
Get it here
Wednesday, July 12, 2006
Tuesday, July 11, 2006
Non Updating Update Performance Improvement In SQL Server 2005
When you do a non updating update like the one below
UPDATE t
SET i = 1
WHERE i=1
In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.
In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed
Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog
UPDATE t
SET i = 1
WHERE i=1
In SQL 2000, the lack of the optimization leads to updating the nonclustered index even if the value is not changing.
In the SQL 2005 plan, it is possible to appreciate
- a “Compute Scalar” operator that compares the current value and new value of the column being modified
- a new filter operator that on a row by row basis will determine whether the nonclustered index is being affected or not
- the fact that nonclustered index maintenance is now bypassed
Read the whole article (including statistics profile output screenshot) at the Tips, Tricks, and Advice from the SQL Server Query Optimization Team blog
Monday, July 10, 2006
Setting Identity Value Back To 1 After Deleting All Rows From A Table
Sometimes you want the identity value to start from 1 again after you delete all the rows from a table
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table
Here is some code to explain what I mean
CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050
DELETE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 6 2006-07-10 12:31:29.143
TRUNCATE TABLE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:38.317
INSERT INTO TestValues VALUES(GETDATE())
DELETE TestValues
DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:52.503
DROP TABLE TestValues
There are 2 ways to accomplish that
1 Use the TRUNCATE TABLE command instead of DELETE
2 If you do use DELETE then run DBCC CHECKIDENT and reseed the table
Here is some code to explain what I mean
CREATE TABLE TestValues (Id INT identity,DateVale DATETIME)
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- This is the output
-- 1 2006-07-10 12:31:17.050
-- 2 2006-07-10 12:31:17.050
-- 3 2006-07-10 12:31:17.050
-- 4 2006-07-10 12:31:17.050
-- 5 2006-07-10 12:31:17.050
DELETE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 6 2006-07-10 12:31:29.143
TRUNCATE TABLE TestValues
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:38.317
INSERT INTO TestValues VALUES(GETDATE())
DELETE TestValues
DBCC CHECKIDENT(TestValues,RESEED,0)
-- Message
-- Checking identity information: current identity value '2', current column value '0'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO TestValues VALUES(GETDATE())
SELECT *
FROM TestValues
-- Output
-- 1 2006-07-10 12:31:52.503
DROP TABLE TestValues
Building SQL Server Integration Services Packages
Learn how easy it is to build and debug your first SQL Server Integration Services (SSIS) Package. Donald Farmer introduces the development environment and the basic concepts of packages and walks through the creation and testing of a simple data flow. See why users describe SSIS as the most productive environment for data integration.
Watch the video at MSDN TV
Get the files here
Watch the video at MSDN TV
Get the files here
Wednesday, July 05, 2006
Designing Effective Aggregations In Analysis Services 2005
If you're into Analysis Services, Integration Services, Reporting Services and BI in general - you'll want to check out Elizabeth Vitt's new blog I found the link to her blog on Kimberly Tripps blog
In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:
Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage
Read the article here: Influencing Aggregation Candidates
In her first entry, Elizabeth Vitt highlights the design components that determine which attributes are considered for aggregation:
Aggregation Definition
Attribute Relationships
Aggregation Candidates
Aggregation Usage
Read the article here: Influencing Aggregation Candidates
Tuesday, July 04, 2006
Free Quest LiteSpeed For SQL Server Developer Edition
Here is an Independence Day gift for you. You can get the fast backup and recovery solution LiteSpeed for free (to be used with the developer edition only)From the Quest site:
"The fast backup and recovery solution for Microsoft SQL Server Developer Edition is here - LiteSpeed™ for SQL Server Developer Edition. LiteSpeed's low-impact, high-performance compression technology allows you to dramatically reduce storage costs and backup/recovery windows.
With LiteSpeed, you'll achieve:
Fast backup and recovery. LiteSpeed's technology achieves 50 percent faster backup and recovery times, allowing you to cut your restore time in half.
Reduced file sizes and storage costs. LiteSpeed's compression technology compresses data up to 95 percent, saving disk space.
Optimized backup and recovery. You'll maintain complete control, while backup and recovery performance is improved right out of the box.
Reduce the time and costs associated with backup and recovery projects today with this full-version download. LiteSpeed for SQL Server Developer Edition - a $45 value - is completely FREE to SQL Server Central readers for a limited time"
Get it here
Monday, July 03, 2006
DDL Triggers And Events
I promised I would write a 'real' SQl subject so here it is: DDL triggers and DDL events for use with DDL triggers
Let's start by creating 2 simple triggers
USE AdventureWorks
GO
CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO
CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO
Let's try creating a table
CREATE TABLE wasabi(id INT)
GO
And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO
Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO
Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc
The ouput is the same for both triggers
name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23
So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?
Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc
I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)
DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
And like I said I will edit this after I run my tests
Let's start by creating 2 simple triggers
USE AdventureWorks
GO
CREATE TRIGGER ddlTestEvents1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!'
ROLLBACK;
GO
CREATE TRIGGER ddlTestEvents2
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!'
ROLLBACK;
GO
Let's try creating a table
CREATE TABLE wasabi(id INT)
GO
And here is the error message, the first trigger fired
You must disable Trigger "ddlTestEvents1" to drop,create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Let's disable the ddlTestEvents1 trigger and see what happens
DISABLE TRIGGER [ddlTestEvents1] ON DATABASE
GO
Let's try creating a table again
CREATE TABLE wasabi(id INT)
GO
Aha, same error message(almost) different trigger
You must disable Trigger "ddlTestEvents2" to drop, create or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
so basically the event DDL_TABLE_EVENTS is the same as the DROP_TABLE, ALTER_TABLE, CREATE_TABLE events. To check that run the following code
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc
The ouput is the same for both triggers
name...........,type_desc,type
ddlTestEvents1,ALTER_TABLE,22
ddlTestEvents1, CREATE_TABLE,21
ddlTestEvents1, DROP_TABLE, 23
ddlTestEvents2, ALTER_TABLE, 22
ddlTestEvents2, CREATE_TABLE, 21
ddlTestEvents2, DROP_TABLE, 23
So when you use DDL_TABLE_EVENTS in the trigger, then 3 events will be in the sys.trigger_events catalog view. This is all fine but how do you find out these things? I can't find DDL_TABLE_EVENTS anywhere. Also I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?
Script out the ddlDatabaseTriggerLog trigger (this is the only trigger that is in the AdventureWorks database when you install SQL server), you will see a DDL_DATABASE_LEVEL_EVENTS event
when you run the following query (same as before, different trigger name) you will see that it returns 76 rows (basically all events)
SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name ='ddlDatabaseTriggerLog'
ORDER BY name,te.type_desc
I am sure I will edit this (many times)
But first I will list all the events
You will see the events followed by a grouped event in red for example
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
I will have to go through all these events and use the sys.trigger_events to find this info out before I post this (unless someone points to in the right direction where to find this)
DDL Statements with Database Scope:
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
-- should be DDL_ASSEMBLY_EVENTS but who knows?
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
DDL_TABLE_EVENTS
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
DDL Statements with Server Scope:
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
DDL_LOGIN_EVENTS
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
And like I said I will edit this after I run my tests
Top 5 Posts For June 2006
Below are the top 5 posts according to Google Analytics for the month of June
COALESCE And ISNULL Differences is number one, the big reason for this is that SQL Server Magazine linked to it
Here are the posts in order by pageviews descending
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Split a comma delimited string fast!
SQL Query Optimizations
And I promise I will have some real SQL code later today
COALESCE And ISNULL Differences is number one, the big reason for this is that SQL Server Magazine linked to it
Here are the posts in order by pageviews descending
COALESCE And ISNULL Differences
OPENROWSET And Excel Problems
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Split a comma delimited string fast!
SQL Query Optimizations
And I promise I will have some real SQL code later today
Sunday, July 02, 2006
Top SQL Server Google Searches For June 2006
These are the top SQL Searches on this site for the month of June. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
dbreindex microsoft sqlserver forum
mysql grant privileges column level
site:sqlservercode.blogspot.com union
-310 sql error
"SQL Server Everywhere"
Collation
replace last two characters
grant update mysql syntax
"The provider ran out of memory"
money
-310 sql
how to sql to excel
Msg 2627, Level 14, State 1
ADD_DAYS oracle syntax
query for SQL bit checking
dbreindex microsoft sqlserver forum
mysql grant privileges column level
site:sqlservercode.blogspot.com union
-310 sql error
"SQL Server Everywhere"
Collation
replace last two characters
grant update mysql syntax
"The provider ran out of memory"
money
-310 sql
how to sql to excel
Msg 2627, Level 14, State 1
ADD_DAYS oracle syntax
query for SQL bit checking
Thursday, June 29, 2006
List DDL Triggers By Using The sys.triggers Catalog View
How do you list DDL triggers in SQL Server 2005? It's very easy you just query the sys.triggers catalog view. Let's try an example
USE adventureworks
GO
--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO
--Create the table
CREATE TABLE abc(id INT)
GO
--Let's drop the table
DROP TABLE abc
GO
Our trigger works because the following message is displayed after we tried to drop the table
You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0
In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0
And if you want to list all triggers you can just ommit the WHERE clause
USE adventureworks
GO
--Create our trigger
CREATE TRIGGER trDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You can not drop tables'
ROLLBACK
GO
--Create the table
CREATE TABLE abc(id INT)
GO
--Let's drop the table
DROP TABLE abc
GO
Our trigger works because the following message is displayed after we tried to drop the table
You can not drop tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
In order to list DDL triggers you need to use parent_id = 0
SELECT * FROM sys.triggers
WHERE parent_id = 0
In order to list DML triggers you need to use parent_id <> 0
SELECT * FROM sys.triggers
WHERE parent_id <> 0
And if you want to list all triggers you can just ommit the WHERE clause
Wednesday, June 28, 2006
Murphy's Law And SQL Server
Why do things break the moment that the person who handles something is on vacation?
Here is the full story. A co-worker goes on vacation, he has his work on a specific server, this server is in our lab and not on the MCN (Mission Critical Network). First let me tell you that we are considered ‘renegades’ and that we don’t get operation support for some of our machines because we don’t use corporate standards and we need full control over our machines.
Anyway the server worked fine for 2 years, co-worker goes on vacation to Japan and the very next day I am getting crazy errors like can not use ad-hoc queries use linked server instead???? I click on the linked server and get OLEDB FoxPro driver missing errors? Fine let’s restart, simple enough right? Wrong! After hitting stop from Service Manager the status is ‘stopping’, after 20 minutes it is still ‘stopping’
Mmm I don’t want to to a hard reboot because I can corrupt all the databases and I don’t even know where half of the backups are (I don’t use this server). Okay let’s try NET STOP MSSQLSERVER. Nope application is in transition and can not be stopped error. Then it occurred to me that maybe we got hit by the slammer worm. Since it’s stopping I can not execute serverproperty so I right click on the sqlserver.exe file and look up the version number. It’s 2039, good that’s SP4. Then I hit restart from the start button and it restarted and everything was fine.
So what is Murphy's Law? Here is the Wikipedia link in case you want to know
http://en.wikipedia.org/wiki/Murphy's_law
Here is the full story. A co-worker goes on vacation, he has his work on a specific server, this server is in our lab and not on the MCN (Mission Critical Network). First let me tell you that we are considered ‘renegades’ and that we don’t get operation support for some of our machines because we don’t use corporate standards and we need full control over our machines.
Anyway the server worked fine for 2 years, co-worker goes on vacation to Japan and the very next day I am getting crazy errors like can not use ad-hoc queries use linked server instead???? I click on the linked server and get OLEDB FoxPro driver missing errors? Fine let’s restart, simple enough right? Wrong! After hitting stop from Service Manager the status is ‘stopping’, after 20 minutes it is still ‘stopping’
Mmm I don’t want to to a hard reboot because I can corrupt all the databases and I don’t even know where half of the backups are (I don’t use this server). Okay let’s try NET STOP MSSQLSERVER. Nope application is in transition and can not be stopped error. Then it occurred to me that maybe we got hit by the slammer worm. Since it’s stopping I can not execute serverproperty so I right click on the sqlserver.exe file and look up the version number. It’s 2039, good that’s SP4. Then I hit restart from the start button and it restarted and everything was fine.
So what is Murphy's Law? Here is the Wikipedia link in case you want to know
http://en.wikipedia.org/wiki/Murphy's_law
Monday, June 26, 2006
DELETE and Subquery (IN) Problem
When you have a DELETE used with an IN (subquery) all rows will be deleted if your subquery is wrong
What do I mean by this? Let's say you have a table named Table1 with 1 column, the name of that column is Col1
When you do SELECT ID FROM Table1 you get an error.
However when you do
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM Table1)
all rows are deleted
Let's start with our test
--Create tables and data
CREATE TABLE TEST1 (ID INT)
GO
CREATE TABLE TEST2 (BLA INT)
GO
INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(2)
INSERT INTO TEST1 VALUES(3)
INSERT INTO TEST2 VALUES(1)
GO
-- The following will raise an error
SELECT ID FROM TEST2 WHERE BLA = 1
GO
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
-- The following statement will delete the entire table
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM TEST2 WHERE BLA = 1)
GO
--(3 row(s) affected)
--One way of doing it
DELETE FROM TEST1 WHERE ID IN
(SELECT B.ID FROM TEST2 B WHERE BLA = 1)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
--Another way
DELETE FROM TEST1 WHERE exists
(SELECT * FROM TEST2 B WHERE BLA = 1 AND b.ID =TEST1.ID)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
What do I mean by this? Let's say you have a table named Table1 with 1 column, the name of that column is Col1
When you do SELECT ID FROM Table1 you get an error.
However when you do
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM Table1)
all rows are deleted
Let's start with our test
--Create tables and data
CREATE TABLE TEST1 (ID INT)
GO
CREATE TABLE TEST2 (BLA INT)
GO
INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(2)
INSERT INTO TEST1 VALUES(3)
INSERT INTO TEST2 VALUES(1)
GO
-- The following will raise an error
SELECT ID FROM TEST2 WHERE BLA = 1
GO
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
-- The following statement will delete the entire table
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM TEST2 WHERE BLA = 1)
GO
--(3 row(s) affected)
--One way of doing it
DELETE FROM TEST1 WHERE ID IN
(SELECT B.ID FROM TEST2 B WHERE BLA = 1)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
--Another way
DELETE FROM TEST1 WHERE exists
(SELECT * FROM TEST2 B WHERE BLA = 1 AND b.ID =TEST1.ID)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.
Sunday, June 25, 2006
The Difference Between Unique Constraint And Unique Index
Do you know the the difference between an unique constraint, and an unique index?
If you do not or if you are not sure then make sure you read the following article:
Unique constraint, or unique index
That article also has a link on how to do selective uniqueness handling
If you do not or if you are not sure then make sure you read the following article:
Unique constraint, or unique index
That article also has a link on how to do selective uniqueness handling
Thursday, June 22, 2006
SQL Server Datetime Datatypes
Since I am kind of busy (Work, House, FIFA World Cup Soccer/Football) I said that I would post links to great SQL Server stuff by other people, so here is a gem that you should definitely read if you have any questions about dates in SQL Server. The name of this page is "The ultimate guide to the datetime datatypes" and it is written by SQL Server MVP Tibor Karaszi and it covers the following things.
Date and time datatypes in SQL Server
Date and time formats
Date and time formats for input
Recommendations for input
Warnings and common misconceptions
Output of datetime values
Searching for datetime values
Getting rid of the time portion
Tip: Always set the time to the same value
Other tips, from SQL Server MVP Steve Kass
Why is 1753 the earliest date for datetime?
References and reading tips
So what are you waiting for, click on the link below?
http://www.karaszi.com/SQLServer/info_datetime.asp
Date and time datatypes in SQL Server
Date and time formats
Date and time formats for input
Recommendations for input
Warnings and common misconceptions
Output of datetime values
Searching for datetime values
Getting rid of the time portion
Tip: Always set the time to the same value
Other tips, from SQL Server MVP Steve Kass
Why is 1753 the earliest date for datetime?
References and reading tips
So what are you waiting for, click on the link below?
http://www.karaszi.com/SQLServer/info_datetime.asp
Wednesday, June 21, 2006
3.5 Ways To Show Stored Procedure Code In SQL Server 2005
There are four (more on that later) ways in SQL Server 2005 to get the create procedure script. Why does the title say 3.5, well that is because the INFORMATION_SCHEMA.ROUTINES view only returns the first 4000 characters. So if your proc is 5000 characters then you are out of luck (not really since you can use the other 3 methods)
So let’s start, we will be using the uspGetBillOfMaterials stored procedure in the AdventureWorks database
sp_helptext
This is the same as in SQL server 2000 nothing new here
sp_helptext 'dbo.uspGetBillOfMaterials'
sys.sql_modules
So this is a new view in SQL Server 2005, what we need is the definition column
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')
OBJECT_DEFINITION()
This is a new function in SQL Server 2005, just combine it with OBJECT_ID to get the proc code back
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.uspGetBillOfMaterials'))
INFORMATION_SCHEMA.ROUTINES
This is the problematic one, if the ROUTINE_DEFINITION is greater than 4000 characters then it will be truncated
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME ='uspGetBillOfMaterials'
AND SPECIFIC_SCHEMA= 'dbo'
So let’s start, we will be using the uspGetBillOfMaterials stored procedure in the AdventureWorks database
sp_helptext
This is the same as in SQL server 2000 nothing new here
sp_helptext 'dbo.uspGetBillOfMaterials'
sys.sql_modules
So this is a new view in SQL Server 2005, what we need is the definition column
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')
OBJECT_DEFINITION()
This is a new function in SQL Server 2005, just combine it with OBJECT_ID to get the proc code back
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.uspGetBillOfMaterials'))
INFORMATION_SCHEMA.ROUTINES
This is the problematic one, if the ROUTINE_DEFINITION is greater than 4000 characters then it will be truncated
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME ='uspGetBillOfMaterials'
AND SPECIFIC_SCHEMA= 'dbo'
Tuesday, June 20, 2006
5 Reasons I Will Blog A Little Less The Next 6 Weeks
Because of these 5 reasons
A) I just moved into a new house and I still have a lot of things to do
B) My wife is 33 weeks pregnant with twins and I have to help her a lot, we also have a son who is 2 (sometimes he acts as if he is in his terrible two’s but mostly not)
C) I still have a lot of things to do to prepare for the babies (mostly buying stuff)
D) I also have a ‘real’ job and we are launching some new products soon
E) It’s the FIFA 2006 World Cup (soccer), I have 2 passports (Dutch and Croatian, but I live in the US) so I have to watch at least all the games when one of my countries is playing
I will be posting a little less of my own content but I will link to other great posts
So today I would like to point out to you a great series of posts about query plans and query executions. I found this blog (http://blogs.msdn.com/craigfr/) after reading Louis Davidson’s blog (http://drsql.spaces.msn.com/)
Here are the posts and they are in FIFO order
The Building Blocks of Query Execution
Viewing Query Plans
Properties of Iterators
A) I just moved into a new house and I still have a lot of things to do
B) My wife is 33 weeks pregnant with twins and I have to help her a lot, we also have a son who is 2 (sometimes he acts as if he is in his terrible two’s but mostly not)
C) I still have a lot of things to do to prepare for the babies (mostly buying stuff)
D) I also have a ‘real’ job and we are launching some new products soon
E) It’s the FIFA 2006 World Cup (soccer), I have 2 passports (Dutch and Croatian, but I live in the US) so I have to watch at least all the games when one of my countries is playing
I will be posting a little less of my own content but I will link to other great posts
So today I would like to point out to you a great series of posts about query plans and query executions. I found this blog (http://blogs.msdn.com/craigfr/) after reading Louis Davidson’s blog (http://drsql.spaces.msn.com/)
Here are the posts and they are in FIFO order
The Building Blocks of Query Execution
Viewing Query Plans
Properties of Iterators
Monday, June 19, 2006
Use OBJECTPROPERTY To Generate A List Of Object Types
How do you query the sysobjects system table and get the object type back for every single object
You can use the type and xtype columns, these contain the following data
xtype
Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
type
Object type. Can be one of these values:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure
Or you can use OBJECTPROPERTY. OBJECTPROPERTY is better in my opinion because you can see right away what you are looking for
For example OBJECTPROPERTY ( id , 'IsUserTable' ) is much easier to understand than type = 'u'
Bu using CASE with OBJECTPROPERTY we can generate a nice report
SELECT name,CASE
WHEN OBJECTPROPERTY ( id , 'IsSystemTable' ) =1 THEN 'System Table'
WHEN OBJECTPROPERTY ( id , 'IsProcedure' ) =1 THEN 'Procedure'
WHEN OBJECTPROPERTY ( id , 'IsPrimaryKey' ) =1 THEN 'Primary Key'
WHEN OBJECTPROPERTY ( id , 'IsDefault' ) =1 THEN 'Default'
WHEN OBJECTPROPERTY ( id , 'IsForeignKey' ) =1 THEN 'Foreign Key'
WHEN OBJECTPROPERTY ( id , 'IsCheckCnst' ) =1 THEN 'Check Constraint'
WHEN OBJECTPROPERTY ( id , 'IsView' ) =1 THEN 'View'
WHEN OBJECTPROPERTY ( id , 'IsConstraint' ) =1 THEN 'Constraint'
WHEN OBJECTPROPERTY ( id , 'IsTrigger' ) =1 THEN 'Trigger'
WHEN OBJECTPROPERTY ( id , 'IsScalarFunction' ) =1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY ( id , 'IsTableFunction' ) =1 THEN 'Table Valued Function'
WHEN OBJECTPROPERTY ( id , 'IsRule' ) =1 THEN 'Rule'
WHEN OBJECTPROPERTY ( id , 'IsExtendedProc' ) =1 THEN 'Extended Stored Procedure'
WHEN OBJECTPROPERTY ( id , 'IsUserTable' ) =1 THEN 'User Table'
END ObjectType, *
FROM sysobjects
And of course there are a bunch of INFORMATION_SCHEMA views that you can use to get some of the same information back
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
You can use the type and xtype columns, these contain the following data
xtype
Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
type
Object type. Can be one of these values:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure
Or you can use OBJECTPROPERTY. OBJECTPROPERTY is better in my opinion because you can see right away what you are looking for
For example OBJECTPROPERTY ( id , 'IsUserTable' ) is much easier to understand than type = 'u'
Bu using CASE with OBJECTPROPERTY we can generate a nice report
SELECT name,CASE
WHEN OBJECTPROPERTY ( id , 'IsSystemTable' ) =1 THEN 'System Table'
WHEN OBJECTPROPERTY ( id , 'IsProcedure' ) =1 THEN 'Procedure'
WHEN OBJECTPROPERTY ( id , 'IsPrimaryKey' ) =1 THEN 'Primary Key'
WHEN OBJECTPROPERTY ( id , 'IsDefault' ) =1 THEN 'Default'
WHEN OBJECTPROPERTY ( id , 'IsForeignKey' ) =1 THEN 'Foreign Key'
WHEN OBJECTPROPERTY ( id , 'IsCheckCnst' ) =1 THEN 'Check Constraint'
WHEN OBJECTPROPERTY ( id , 'IsView' ) =1 THEN 'View'
WHEN OBJECTPROPERTY ( id , 'IsConstraint' ) =1 THEN 'Constraint'
WHEN OBJECTPROPERTY ( id , 'IsTrigger' ) =1 THEN 'Trigger'
WHEN OBJECTPROPERTY ( id , 'IsScalarFunction' ) =1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY ( id , 'IsTableFunction' ) =1 THEN 'Table Valued Function'
WHEN OBJECTPROPERTY ( id , 'IsRule' ) =1 THEN 'Rule'
WHEN OBJECTPROPERTY ( id , 'IsExtendedProc' ) =1 THEN 'Extended Stored Procedure'
WHEN OBJECTPROPERTY ( id , 'IsUserTable' ) =1 THEN 'User Table'
END ObjectType, *
FROM sysobjects
And of course there are a bunch of INFORMATION_SCHEMA views that you can use to get some of the same information back
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Scriptio 0.5.5 Released
Bill Graziano has released the latest version of his Scriptio tool. What is scriptio?
From Bill's site: "After working with SQL Server 2005 I've discovered that I can't script out one object per file and include both the DROP and CREATE in the same file. Which is driving me absolutely crazy! So I wrote a little utility and thought I'd share it."
You can get the lates version here
From Bill's site: "After working with SQL Server 2005 I've discovered that I can't script out one object per file and include both the DROP and CREATE in the same file. Which is driving me absolutely crazy! So I wrote a little utility and thought I'd share it."
You can get the lates version here
Tuesday, June 13, 2006
Check For Valid SMALLDATETIME
Sometime you get data from different sources and the dates are stored in a varchar columnYou need to store that data in your database. The column has a smalldatetime data typeHow do you make sure that the data is correct? You have to use ISDATE and also check that the dates are between 1/1/1900 and 6/6/2079
Here is some code to test that out
CREATE TABLE TestDates(VarCharDate VARCHAR(30))
-- Good smalldatetime
INSERT TestDates
VALUES ('19000101')
--Bad smalldatetime, less than 19000101
INSERT TestDates
VALUES ('18990101')
--Bad smalldatetime, greater than 20790606
INSERT TestDates
VALUES ('20790607')
-- Good smalldatetime
INSERT TestDates
VALUES ('20790606')
--Bad date
INSERT TestDates
VALUES ('abababa')
--This will Fail
SELECT CASE
WHEN ISDATE(VarCharDate) =1 THEN
CONVERT(SMALLDATETIME,VarCharDate)
ELSE
NULL
END
FROM TestDates
--This will run fine
SELECT VarCharDate,
CASE
WHEN ISDATE(VarCharDate) =1
THEN CASE WHEN VarCharDate BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,VarCharDate)
ELSE NULL
END
END
FROM TestDates
Flo has left a comment, he/she has suggested to convert to datetime in the second case in the case that the date is not stored in the YYYYMMDD format
Here is the suggestion
DECLARE @VarCharDate VARCHAR(100)
SET @VarCharDate = '12/30/2070'
-- Modification:
-- Use CONVERT(DATETIME, @VarCharDate) to check the varchar in any possible format
SELECT @VarCharDate,
CASE
WHEN ISDATE(@VarCharDate) =1
THEN CASE WHEN CONVERT(DATETIME,@VarCharDate) BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,@VarCharDate)
ELSE NULL
END
END
Here is some code to test that out
CREATE TABLE TestDates(VarCharDate VARCHAR(30))
-- Good smalldatetime
INSERT TestDates
VALUES ('19000101')
--Bad smalldatetime, less than 19000101
INSERT TestDates
VALUES ('18990101')
--Bad smalldatetime, greater than 20790606
INSERT TestDates
VALUES ('20790607')
-- Good smalldatetime
INSERT TestDates
VALUES ('20790606')
--Bad date
INSERT TestDates
VALUES ('abababa')
--This will Fail
SELECT CASE
WHEN ISDATE(VarCharDate) =1 THEN
CONVERT(SMALLDATETIME,VarCharDate)
ELSE
NULL
END
FROM TestDates
--This will run fine
SELECT VarCharDate,
CASE
WHEN ISDATE(VarCharDate) =1
THEN CASE WHEN VarCharDate BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,VarCharDate)
ELSE NULL
END
END
FROM TestDates
Flo has left a comment, he/she has suggested to convert to datetime in the second case in the case that the date is not stored in the YYYYMMDD format
Here is the suggestion
DECLARE @VarCharDate VARCHAR(100)
SET @VarCharDate = '12/30/2070'
-- Modification:
-- Use CONVERT(DATETIME, @VarCharDate) to check the varchar in any possible format
SELECT @VarCharDate,
CASE
WHEN ISDATE(@VarCharDate) =1
THEN CASE WHEN CONVERT(DATETIME,@VarCharDate) BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,@VarCharDate)
ELSE NULL
END
END
Monday, June 12, 2006
COALESCE And ISNULL Differences
I decided to do a quick post about two differences between COALESCE and ISNULL
Run the following block of code
-- The result is 7, integer math
SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)
--The result is 7.5, which is correct
SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)
You will see that the result is not the same ISNULL does integer math while COALESCE does not
COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal
Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null
DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)
SELECT @Var4 = 'ABC'
--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)
[Edit] Roji. P. Thomas has an excellent article with even more detailed examples. The links is below.
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
[/Edit]
Run the following block of code
-- The result is 7, integer math
SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)
--The result is 7.5, which is correct
SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)
You will see that the result is not the same ISNULL does integer math while COALESCE does not
COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal
Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null
DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)
SELECT @Var4 = 'ABC'
--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)
[Edit] Roji. P. Thomas has an excellent article with even more detailed examples. The links is below.
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
[/Edit]
Friday, June 09, 2006
Retrieve Column Info Of Table Valued Functions By Using INFORMATION_SCHEMA.ROUTINE_COLUMNS
How do you retrieve the columns of Table Valued Functions?
This question was posted to day in the microsoft.public.sqlserver.programming
forum. Although I answered the question I must admit that I have never used the INFORMATION_SCHEMA.ROUTINE_COLUMNS before
So let's see how it works
First create a User-Defined Functions that returns a table data type
USE pubs
GO
This question was posted to day in the microsoft.public.sqlserver.programming
forum. Although I answered the question I must admit that I have never used the INFORMATION_SCHEMA.ROUTINE_COLUMNS before
So let's see how it works
First create a User-Defined Functions that returns a table data type
USE pubs
GO
CREATE FUNCTION LargeOrder ( @FreightParm
style="color:#3333ff;">money )
RETURNS @OrderShipperTab TABLE
(
OrderPrice MONEY,
OrderDate DATETIME
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT OrderPrice, OrderDate
FROM Orders
WHERE OrderPrice > @FreightParm
RETURN
END
--Let's test the function
-- This will return all orders with an order price greater than $40
SELECT *
FROM LargeOrder( 40 )
--Now let's get the column information
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
WHERE TABLE_NAME ='LargeOrder'
ORDER BY ORDINAL_POSITION
Rename A column In A Table With sp_rename
How do you rename a column in a SQL Server table without using Enterprise Manager/SSMS of course, T_SQL only.
This is a frequent question in the newsgroups and it's causing some confusion for people because you would assume that
you would use the ALTER TABLE syntax, instead of that you have to use sp_rename to rename the column
--Create the table
CREATE TABLE TestRename (id INT,[Some[[Col] INT)
--Do a select to check the column name
SELECT * FROM TestRename
-- This is how you change the dataype
ALTER TABLE TestRename
ALTER COLUMN [Some[[Col] VARCHAR(50)
--this is how you rename the column
EXEC sp_rename 'TestRename.[Some[[Col]', 'SomeCol', 'COLUMN'
--Let’s check again, you will see that the column name has changed
SELECT * FROM TestRename
After changing the column name with sp_rename you will get the following warning
Caution: Changing any part of an object name could break scripts and stored procedures.
The COLUMN was renamed to 'SomeCol'.
This is a frequent question in the newsgroups and it's causing some confusion for people because you would assume that
you would use the ALTER TABLE syntax, instead of that you have to use sp_rename to rename the column
--Create the table
CREATE TABLE TestRename (id INT,[Some[[Col] INT)
--Do a select to check the column name
SELECT * FROM TestRename
-- This is how you change the dataype
ALTER TABLE TestRename
ALTER COLUMN [Some[[Col] VARCHAR(50)
--this is how you rename the column
EXEC sp_rename 'TestRename.[Some[[Col]', 'SomeCol', 'COLUMN'
--Let’s check again, you will see that the column name has changed
SELECT * FROM TestRename
After changing the column name with sp_rename you will get the following warning
Caution: Changing any part of an object name could break scripts and stored procedures.
The COLUMN was renamed to 'SomeCol'.
Data Dude
Last week I told you about Visual Studio Team Edition for Database Professionals (Data Dude)
Kimberly L. Tripp has a post on her blog with 17 links to websites and 5 links to blogs
Here are the Data Dude team blogs:
Gert Drapers' Blog
Cameron Skinner's Blog
Richard Waymire's Blog
Thomas Murphy's Blog
Database Professional's Team Blog
Vist Kimberly's blog for the whole post
Kimberly L. Tripp has a post on her blog with 17 links to websites and 5 links to blogs
Here are the Data Dude team blogs:
Gert Drapers' Blog
Cameron Skinner's Blog
Richard Waymire's Blog
Thomas Murphy's Blog
Database Professional's Team Blog
Vist Kimberly's blog for the whole post
Thursday, June 08, 2006
INFORMATION_SCHEMA.SCHEMATA SQL Server 2005 Change
Everyone keeps saying;” Don’t use the system tables directly but use the ANSI views instead”
So instead of sysobjects use INFORMATION_SCHEMA.TABLES. In SQL Server 2000 instead of using the sysdatabases system table I always used the INFORMATION_SCHEMA.SCHEMATA view to query for the databases. It turns out that that view was no really ANSI compliant to begin with (since SQL server 200 didn’t have schemas of course)
If you run the following query in SQL Server 2000
SELECT CATALOG_NAME, SCHEMA_OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
You will get a result like this
If you run the query in SQL Server 2005 the CATALOG_NAME
will be the same
for every row (it will be the current database name)
However if you run
SELECT SCHEMA _NAME, SCHEMA_OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
You will get the following result set
What can you do to query the database name?
Well you can do this
SELECT name FROM master.sys.databases ORDER BY name
Or
SELECT name FROM master..sysdatabases ORDER BY name
According to books on line:
In earlier versions of SQL Server, the INFORMATION_SCHEMA.SCHEMATA view returned all databases in an instance of SQL Server. In SQL Server 2005, the view returns all schemas in a database. This behavior complies with the SQL Standard. For more information, see SCHEMATA (Transact-SQL).
So instead of sysobjects use INFORMATION_SCHEMA.TABLES. In SQL Server 2000 instead of using the sysdatabases system table I always used the INFORMATION_SCHEMA.SCHEMATA view to query for the databases. It turns out that that view was no really ANSI compliant to begin with (since SQL server 200 didn’t have schemas of course)
If you run the following query in SQL Server 2000
SELECT CATALOG_NAME, SCHEMA_OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
You will get a result like this
CATALOG_NAME, SCHEMA_OWNER
-------------------------
master..............dbo
tempdb..............dbo
model...............dbo
msdb................dbo
pubs................dbo
Northwind...........dbo
If you run the query in SQL Server 2005 the CATALOG_NAME
will be the same
for every row (it will be the current database name)
However if you run
SELECT SCHEMA _NAME, SCHEMA_OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
You will get the following result set
SCHEMA _NAME, SCHEMA_OWNER
--------------------------------------
dbo.................dbo
guest...............guest
INFORMATION_SCHEMA..INFORMATION_SCHEMA
sys.................sys
HumanResources......dbo
Person..............dbo
Production..........dbo
Purchasing..........dbo
Sales...............dbo
SalesData...........dbo
db_owner............db_owner
db_accessadmin......db_accessadmin
db_securityadmin....db_securityadmin
db_ddladmin.........db_ddladmin
db_backupoperator...db_backupoperator
db_datareader.......db_datareader
db_datawriter.......db_datawriter
db_denydatareader...db_denydatareader
db_denydatawriter...db_denydatawriter
What can you do to query the database name?
Well you can do this
SELECT name FROM master.sys.databases ORDER BY name
Or
SELECT name FROM master..sysdatabases ORDER BY name
According to books on line:
In earlier versions of SQL Server, the INFORMATION_SCHEMA.SCHEMATA view returned all databases in an instance of SQL Server. In SQL Server 2005, the view returns all schemas in a database. This behavior complies with the SQL Standard. For more information, see SCHEMATA (Transact-SQL).
Thursday, June 01, 2006
Use DATEADD And DATEDIFF To Get The Start And End Date For A Quarter
In the microsoft.public.sqlserver.programming forum a person asked how to get the first day of the current quarter. My answer was this: SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
So I decided to expand on that here
--Start date of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
--start and end dates of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, -1) AS LastDayOfQuarter
--start day of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0)
--start and end dates of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060501')+1, -1) AS LastDayOfQuarter
--start and end dates of the quarter for 20060201
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060201')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060201')+1, -1) AS LastDayOfQuarter
So I decided to expand on that here
--Start date of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
--start and end dates of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, -1) AS LastDayOfQuarter
--start day of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0)
--start and end dates of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060501')+1, -1) AS LastDayOfQuarter
--start and end dates of the quarter for 20060201
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060201')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060201')+1, -1) AS LastDayOfQuarter
Top 5 Posts For May 2006
Below are the top 5 posts according to Google Analytics for the month of May
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
Fun With SQL Server Update Triggers
SQL Query Optimizations
Split a comma delimited string fast!
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
Fun With SQL Server Update Triggers
SQL Query Optimizations
Split a comma delimited string fast!
Top SQL Server Google Searches For May 2006
These are the top SQL Searches on this site for the month of May. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
microsoft access
charindex city state zip
point into polygon
enterprise manager
cast example - sql
alter identity increment
Syntax Error Converting The Varchar Value To A Column Of Data Type Int hibernate
server updates varchar column to null
lock select
distinct
exists
password case sensitive site:sqlservercode.blogspot.com
insert
dynamic scroll
microsoft access
charindex city state zip
point into polygon
enterprise manager
cast example - sql
alter identity increment
Syntax Error Converting The Varchar Value To A Column Of Data Type Int hibernate
server updates varchar column to null
lock select
distinct
exists
password case sensitive site:sqlservercode.blogspot.com
insert
dynamic scroll
Wednesday, May 31, 2006
Unit Testing For The DB, Visual Studio Team Edition for Database Professionals Is Here
Microsoft Corp. today announced Visual Studio® Team Edition for Database Professionals, expanding Visual Studio 2005 Team System to include tools that enable software development organizations to collaborate more effectively when creating reliable, data-driven applications. The new product will be available as a Community Technology Preview (CTP) at Tech•Ed 2006 in Boston and is expected to ship by the end of 2006. This addition to Microsoft® Visual Studio 2005 Team System is the next milestone en route to the release of Visual Studio code-named “Orcas,” which will provide capabilities for building compelling applications that target Microsoft SQL Server™ 2005, Windows Vista™, the 2007 Microsoft Office system, and the next generation of Web technologies
Bringing Database Professionals Into the Application Life Cycle
Visual Studio Team Edition for Database Professionals delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams.
“All too frequently, schisms exist between database teams and development staff, leading to ineffective collaboration, inadequate project and code management, poor quality, and cost increases,” said Melinda Ballou, program director at IDC’s Application Life Cycle Management service. “Avoidable problems occur due to these gaps in communication. Users on both sides need tools and processes to facilitate coordination and management across groups and across skill sets, to increase efficiency, and to improve software quality earlier in the life cycle.”
This new member of the Team System family fulfills increasing demand in the market for more advanced tools for managing database changes by providing a foundation to reduce risk, ensure quality and speed deployment. Database architects, developers, administrators and other database professionals can now employ integrated change-management functionality to streamline changes to their databases and reduce the risk of catastrophic failure related to the alteration of database schemas. In addition, database professionals may now drive better quality earlier in the development process through integrated database testing, including support for database unit tests, complex test authoring and automatic generation of meaningful test data.
read the press-release: Microsoft Enhances Collaboration for Database Professionals With New Addition to Visual Studio Product Line
Bringing Database Professionals Into the Application Life Cycle
Visual Studio Team Edition for Database Professionals delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams.
“All too frequently, schisms exist between database teams and development staff, leading to ineffective collaboration, inadequate project and code management, poor quality, and cost increases,” said Melinda Ballou, program director at IDC’s Application Life Cycle Management service. “Avoidable problems occur due to these gaps in communication. Users on both sides need tools and processes to facilitate coordination and management across groups and across skill sets, to increase efficiency, and to improve software quality earlier in the life cycle.”
This new member of the Team System family fulfills increasing demand in the market for more advanced tools for managing database changes by providing a foundation to reduce risk, ensure quality and speed deployment. Database architects, developers, administrators and other database professionals can now employ integrated change-management functionality to streamline changes to their databases and reduce the risk of catastrophic failure related to the alteration of database schemas. In addition, database professionals may now drive better quality earlier in the development process through integrated database testing, including support for database unit tests, complex test authoring and automatic generation of meaningful test data.
read the press-release: Microsoft Enhances Collaboration for Database Professionals With New Addition to Visual Studio Product Line
Import Dates, Skip Bad Data By Using ISDATE() And CASE
You have a table with a 'date' column and it's stored as varchar, the problem is that you also have bad data in there
You want to import this data into another table, but if the data can not be converted into a date you want to make it NULL
The way to handle this is by using the ISDATE() function together with CASE
--Let's create our table with bad and good data
CREATE TABLE SomeFakeDateTable (FakeDate VARCHAR(23))
INSERT SomeFakeDateTable VALUES ('ababababa')
INSERT SomeFakeDateTable VALUES ('20060101')
INSERT SomeFakeDateTable VALUES ('20060299')
INSERT SomeFakeDateTable VALUES (NULL)
INSERT SomeFakeDateTable VALUES ('20060401')
INSERT SomeFakeDateTable VALUES ('20050331')
--Here is the query
SELECT FakeDate,
CASE
WHEN ISDATE(FakeDate) = 1 THEN CONVERT(DATETIME,FakeDate)
ELSE NULL
END TheRealDate
FROM SomeFakeDateTable
You want to import this data into another table, but if the data can not be converted into a date you want to make it NULL
The way to handle this is by using the ISDATE() function together with CASE
--Let's create our table with bad and good data
CREATE TABLE SomeFakeDateTable (FakeDate VARCHAR(23))
INSERT SomeFakeDateTable VALUES ('ababababa')
INSERT SomeFakeDateTable VALUES ('20060101')
INSERT SomeFakeDateTable VALUES ('20060299')
INSERT SomeFakeDateTable VALUES (NULL)
INSERT SomeFakeDateTable VALUES ('20060401')
INSERT SomeFakeDateTable VALUES ('20050331')
--Here is the query
SELECT FakeDate,
CASE
WHEN ISDATE(FakeDate) = 1 THEN CONVERT(DATETIME,FakeDate)
ELSE NULL
END TheRealDate
FROM SomeFakeDateTable
Tuesday, May 30, 2006
red-gate SQL Prompt: Intellisense for SQL Server
SQL Prompt™Intellisense for SQL Server, plus other features
There was a question on the SQL Server programming forum about intellisense and SQL server. it turns out that red-gate has such a product and better yet it's free until September 2006. So test it out (I will) and maybe it will be usefull for your development.
What does SQL Prompt offer (this info is from their site)?
Code completion for fast, accurate script building
Discoverability in SQL Server query creation
Keyword formatting, code snippet integration other extended features
FREE until 1st September 2006
No time-bombs, no restrictions
SQL Prompt provides Intellisense® style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements. SQL Prompt improves the productivity of all SQL script creation. SQL Prompt can be downloaded free until 1st September 2006 and we are providing forum support on the SQL Prompt support forum. A web help file is also available.
SQL Prompt simply sits behind the scenes and provides unobtrusive help when you press Ctrl-Space or when you type "." after a table/view/alias name.
Features include :
Table/View name completion
Column name completion
Stored procedure name completion
USE completion
JOIN/JOIN ON completion
Auto-uppercasing of keywords
Auto-popup after keywords
SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32
Download it here
Monday, May 29, 2006
Chaos Isolation Level In SQL Server
The other day I posted SQL Server Teaser: Isolation Level
So either no one reads this blog or no one knew he answer, or maybe you are too shy to make a comment
Anyway the answer is Chaos and you can find it in DTS, when you right click in the DTS designer then select Package Properties and after that click on the Advanced tab.
you can select it from the Transaction Isolation dropdown (see pic)
So either no one reads this blog or no one knew he answer, or maybe you are too shy to make a comment
Anyway the answer is Chaos and you can find it in DTS, when you right click in the DTS designer then select Package Properties and after that click on the Advanced tab.
you can select it from the Transaction Isolation dropdown (see pic)
Thursday, May 25, 2006
Populating A Dropdown With Weekly Dates
Let's assume you have a website and the user can pick from a date range, You want to be a nice guy/girl (* reason for the asterisk is at the end) so instead of having a dropdown that looks like
Week1
Week2
Week3
You want it to have start and end dates for the week
The start date should be a Sunday and the end date should be a Saturday
The output should be something like this
2006-05-07 - 2006-05-13
2006-05-14 - 2006-05-20
2006-05-21 - 2006-05-27
What do you do? Do you store all these dates in a table, you can but you will have to maintain this.
Another way is to use a number table
You would create the number table only once. Then you can use DATEADD and DATEPART with the number table to return the desired results
-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=1000 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
DECLARE @StartDate DATETIME
SELECT @StartDate ='05/07/2006'
--How many rows to return
DECLARE @RowsToReturn INT
SELECT @RowsToReturn = 50
SELECT DATEADD(wk,numberID,@StartDate) AS WeekStart,
DATEADD(dd,-1,(DATEADD(wk,numberID+1,@StartDate))) AS WeekEnd
FROM dbo.NumberPivot
WHERE NumberID <= @RowsToReturn -1 -- 1 since the number table starts from 0
AND DATEADD(wk,numberID,@StartDate) >=@StartDate
ORDER BY 1
* At my previous job we were doing file exchanges with some state agencies. One of the fields in the file was Sex and the values could be either F or M,. fair enough right? Well guess what, there was one person who worked there and had a sex-operation done and did not want to be classified as male or female So we then added other ( O ) We had no choice this was specifically written for the state and they were the major user of the program…oh well, nothing is black or white is it?
Week1
Week2
Week3
You want it to have start and end dates for the week
The start date should be a Sunday and the end date should be a Saturday
The output should be something like this
2006-05-07 - 2006-05-13
2006-05-14 - 2006-05-20
2006-05-21 - 2006-05-27
What do you do? Do you store all these dates in a table, you can but you will have to maintain this.
Another way is to use a number table
You would create the number table only once. Then you can use DATEADD and DATEPART with the number table to return the desired results
-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=1000 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
--And here is the query
--I started with a SundayDECLARE @StartDate DATETIME
SELECT @StartDate ='05/07/2006'
--How many rows to return
DECLARE @RowsToReturn INT
SELECT @RowsToReturn = 50
SELECT DATEADD(wk,numberID,@StartDate) AS WeekStart,
DATEADD(dd,-1,(DATEADD(wk,numberID+1,@StartDate))) AS WeekEnd
FROM dbo.NumberPivot
WHERE NumberID <= @RowsToReturn -1 -- 1 since the number table starts from 0
AND DATEADD(wk,numberID,@StartDate) >=@StartDate
ORDER BY 1
* At my previous job we were doing file exchanges with some state agencies. One of the fields in the file was Sex and the values could be either F or M,. fair enough right? Well guess what, there was one person who worked there and had a sex-operation done and did not want to be classified as male or female So we then added other ( O ) We had no choice this was specifically written for the state and they were the major user of the program…oh well, nothing is black or white is it?
Wednesday, May 24, 2006
Spatial Indexes In SQL Server 2005
Maybe you work with Oracle as well as SQL server 2005 and you use Oracle Spatial and Oracle Locator. Well there is a way to implement this in SQL Server 2005.Read the article "Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library" on MSDN.
This article explains how to add spatial search functions (point-near-point and point in polygon) to Microsoft SQL Server 2005 using C# and table-valued functions. It is possible to use this library to add spatial search to your application without writing any special code. The library implements the public-domain C# Hierarchical Triangular Mesh (HTM) algorithms from Johns Hopkins University. That C# library is connected to SQL Server 2005 using a set of scalar-valued and table-valued functions. These functions act as a spatial index.
SQL Server Pics From Version 1.0 On
Euan Garden has posted pictures of the SQL server product boxes since version 1.0
Did you know version 1.0 came out on Floppy's for OS/2?
Check out the pictures here
http://blogs.msdn.com/euanga/archive/2006/05/23/597677.aspx
Did you know version 1.0 came out on Floppy's for OS/2?
Check out the pictures here
http://blogs.msdn.com/euanga/archive/2006/05/23/597677.aspx
Tuesday, May 23, 2006
SQL Server Teaser: Isolation Level
In SQL server 2000 there are 4 isolation levels
Read uncommitted which can produce a dirty read, a repeatable read and a phantom read
Read committed which can produce a repeatable read and a phantom read
Repeatable read which can produce a phantom read
Serializable
What if I tell you that there is a 5th one, no it’s not snapshot isolation we are dealing with SQL Server 2000 here
The question is how do you set it and what is the (appropriate) name of this isolation level
I will post the answer on Memorial Day if no nobody answers by then
Read uncommitted which can produce a dirty read, a repeatable read and a phantom read
Read committed which can produce a repeatable read and a phantom read
Repeatable read which can produce a phantom read
Serializable
What if I tell you that there is a 5th one, no it’s not snapshot isolation we are dealing with SQL Server 2000 here
The question is how do you set it and what is the (appropriate) name of this isolation level
I will post the answer on Memorial Day if no nobody answers by then
Monday, May 22, 2006
Cumulative Hotfix Package (build 2153) For SQL Server 2005 Available
So just after sp1 we already have a hotfix
The following has been fixed
SQL Server 2005 Post-SP1 hotfixes that are included in SQL Server 2005 build 9.0.2153
SQL Bug number Description
410 Dimension security does not support visual totals on a parent attribute that is in a parent-child dimension.
433 If you execute an ALTER DDL statement that removes dimension hierarchies from the database without removing the hierarchies from the cubes, an access violation may occur.
447 Processing performance on multiprocessor computers is somewhat slower than expected.
459 Under very specific and rare circumstances, a deadlock can occur between two or more sessions in SQL Server 2005. In this case, the internal deadlock monitoring mechanism does not detect the deadlock between the sessions.
461 Page breaks may not be respected when a table is rendered in Microsoft Office Excel if that report contains tables with the conditions of NoRows=true and NoRowsMessage=null, and the table contains a table header or footer.
464 The leaf page of a non-clustered index may have only one index row.
491 On a SQL Server 2005 installation that uses Turkish collation, you cannot view job steps that are of SSIS type from SQL Server Management Studio. If a job has SSIS job steps only, you receive an "Index was outside the bounds of the array. (SqlManagerUI)" error message when you click the Steps tab.
499 A Multidimensional Expressions (MDX) query that involves non-trivial custom rollups when it runs complex sum-like aggregate expressions runs much slower on SQL Server 2005 than on SQL Server 2000.
531 When you run a query or a stored procedure in SQL Server 2005, certain conditions can cause the SQL Server optimizer not to be able to produce an execution plan.
541 An ADOMD.NET application that runs through an IXMLA provider is limited to 32 maximum user sessions.
554 When you query the IS_PLACEHOLDERMEMBER member property for a dimension member by using the .Properties("IS_PLACEHOLDERMEMBER") function in MDX, the value that is returned is always FALSE. This behavior occurs even for members that are placeholders in the dimension.
584 When a report contains an image that is external or is a resource in Reporting Services and the report uses the interactive sorting functionality, the image is replaced by a red x.
606 The MDSCHEMA_PROPERTIES row set returns the wrong data type for some user-defined properties.
608 If you include a subreport in a group footer and you enable the HideDuplicates property in a detail row on a grouping item, SQL Server 2005 Reporting Services raises an internal error when you try to export the report. The error also occurs when you click Print Preview on the Preview tab in Report Designer.
614 SQL Server 2005 Reporting Services may intermittently display a System.NullReferenceException exception in the ReportSnapshot.EnsureAllStreamsAreClosed procedure. This behavior generates a mini-dump.
615 The SQL Server 2005 Analysis Services service does not start when you use Japanese_Unicode_CI_AS as a collation for the instance of SQL Server 2005 Analysis Services.
617 When you try to run a linked report that uses the User!UserID variable and the execution properties are set to use Snapshots, you receive the following error message:
3wp!library!1!1/27/2006-13:10:27:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
627 In the Japanese version of SQL Server 2005, the fulltext schedule creation in SQL Server Management Studio fails, and you receive the following error message:
Specified @category_name ('フルテã‚スト'localized string) not found (Microsoft SQL Server, Error: 14262)
636 A memory leak may occur in the common language runtime (CLR) if you pass a CLR user-defined data type as a parameter by using a stored procedure or the sp_executesql procedure.
652 If Y-axis margins are enabled and the minimum Y-value of all data points is a "nice" integer value, the Y-axis minimum value will be adjusted to a lower value. For example, the chart control rounds 4.0 to 4 to create a "nice" integer value. However, the generated Y-axis labels are incorrect.
664 You run SQL Server 2005 Analysis Services builds 9.00.1520 through 9.00.1539 or SQL Server 2005 SP1. In this case, an MDX query that includes a distinct count measure may not return results. This behavior only occurs if the measure group that is queried contains multiple partitions.
708 The SQL Server Integration Services (SSIS) service does not start when you install SQL Server 2005 SP1
752 The MSMQTask.dll and dependencies are deleted when the fix that is described in the Microsoft Knowledge Base article 910070 is applied.
40000100 If the log reader agent and the DBCC DBREINDEX or ALTER INDEX ...REBUILD commands run at the same time, the log reader agent may miss some transactions to the distributor.
40000102 When you configure Log Shipping and add two or more secondary servers to Log Shipping, only the secondary server that you added last is monitored by the monitor server and the other secondary servers are not.
40000108 The SQL Server 2005 Database Engine Tuning Advisor may unexpectedly exit if indexed views are part of the workload that is tuned.
40000110 The YTD calculation in cubes that use parent-child dimensions may be incorrect.
50000104 The SqlCommandBuilder.DeriveParameters(SqlCommand) method returns an exception when you execute a stored procedure with an input parameter that is a typed XML type.
50000112 A member of the SQL Server 2005 Analysis Services database administrator role cannot add or delete perspectives. Only a member of the SQL Server 2005 Analysis Services server role can perform such tasks.
50000115 A Report Server Model may use an Analysis Services data source. MDX queries are generated from Report Builder based on the Report Server Model. Dates in the MDX queries have the month and day parts transposed.
50000116 A FETCH operation that uses a KEYSET cursor may be slow if the target table for the cursor has a clustered index and non-clustered indexes. This behavior occurs if the clustered index is the best choice for the FETCH operation and if the SELECT query for the cursor is very simple.
50000120 In SQL Server 2005 Analysis Services, calculated members that are defined in the cube MDX script are resolved before any session or query defined calculations. The only exceptions are certain scenarios with the Aggregate and VisualTotals functions. This behavior is a change from the SQL Server 2000 Analysis Services behavior. In SQL Server 2000 Analysis Services, the Solve Order could be used explicitly to insert a session or a query-defined calculation between two cube level calculations. This change in behavior may cause the query or session scope calculated members not to return the results that you want in SQL Server 2005 Analysis Services. Cumulative hotfix package for SQL Server 2005 (build 9.0.2153) introduces new syntax that allows for control over the scope in which a calculated member is evaluated.
50000151 When you run a SELECT query to return any duplicate rows in a table, incorrect results are returned if the query plan contains a hash aggregation and if one or more of the columns in the table contain a combination of empty strings and undefined characters.
50000154 You may receive a variablelock list error message when you run multiple instances of a package.
You can download the hotfix here
The following has been fixed
SQL Server 2005 Post-SP1 hotfixes that are included in SQL Server 2005 build 9.0.2153
SQL Bug number Description
410 Dimension security does not support visual totals on a parent attribute that is in a parent-child dimension.
433 If you execute an ALTER DDL statement that removes dimension hierarchies from the database without removing the hierarchies from the cubes, an access violation may occur.
447 Processing performance on multiprocessor computers is somewhat slower than expected.
459 Under very specific and rare circumstances, a deadlock can occur between two or more sessions in SQL Server 2005. In this case, the internal deadlock monitoring mechanism does not detect the deadlock between the sessions.
461 Page breaks may not be respected when a table is rendered in Microsoft Office Excel if that report contains tables with the conditions of NoRows=true and NoRowsMessage=null, and the table contains a table header or footer.
464 The leaf page of a non-clustered index may have only one index row.
491 On a SQL Server 2005 installation that uses Turkish collation, you cannot view job steps that are of SSIS type from SQL Server Management Studio. If a job has SSIS job steps only, you receive an "Index was outside the bounds of the array. (SqlManagerUI)" error message when you click the Steps tab.
499 A Multidimensional Expressions (MDX) query that involves non-trivial custom rollups when it runs complex sum-like aggregate expressions runs much slower on SQL Server 2005 than on SQL Server 2000.
531 When you run a query or a stored procedure in SQL Server 2005, certain conditions can cause the SQL Server optimizer not to be able to produce an execution plan.
541 An ADOMD.NET application that runs through an IXMLA provider is limited to 32 maximum user sessions.
554 When you query the IS_PLACEHOLDERMEMBER member property for a dimension member by using the .Properties("IS_PLACEHOLDERMEMBER") function in MDX, the value that is returned is always FALSE. This behavior occurs even for members that are placeholders in the dimension.
584 When a report contains an image that is external or is a resource in Reporting Services and the report uses the interactive sorting functionality, the image is replaced by a red x.
606 The MDSCHEMA_PROPERTIES row set returns the wrong data type for some user-defined properties.
608 If you include a subreport in a group footer and you enable the HideDuplicates property in a detail row on a grouping item, SQL Server 2005 Reporting Services raises an internal error when you try to export the report. The error also occurs when you click Print Preview on the Preview tab in Report Designer.
614 SQL Server 2005 Reporting Services may intermittently display a System.NullReferenceException exception in the ReportSnapshot.EnsureAllStreamsAreClosed procedure. This behavior generates a mini-dump.
615 The SQL Server 2005 Analysis Services service does not start when you use Japanese_Unicode_CI_AS as a collation for the instance of SQL Server 2005 Analysis Services.
617 When you try to run a linked report that uses the User!UserID variable and the execution properties are set to use Snapshots, you receive the following error message:
3wp!library!1!1/27/2006-13:10:27:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
627 In the Japanese version of SQL Server 2005, the fulltext schedule creation in SQL Server Management Studio fails, and you receive the following error message:
Specified @category_name ('フルテã‚スト'localized string) not found (Microsoft SQL Server, Error: 14262)
636 A memory leak may occur in the common language runtime (CLR) if you pass a CLR user-defined data type as a parameter by using a stored procedure or the sp_executesql procedure.
652 If Y-axis margins are enabled and the minimum Y-value of all data points is a "nice" integer value, the Y-axis minimum value will be adjusted to a lower value. For example, the chart control rounds 4.0 to 4 to create a "nice" integer value. However, the generated Y-axis labels are incorrect.
664 You run SQL Server 2005 Analysis Services builds 9.00.1520 through 9.00.1539 or SQL Server 2005 SP1. In this case, an MDX query that includes a distinct count measure may not return results. This behavior only occurs if the measure group that is queried contains multiple partitions.
708 The SQL Server Integration Services (SSIS) service does not start when you install SQL Server 2005 SP1
752 The MSMQTask.dll and dependencies are deleted when the fix that is described in the Microsoft Knowledge Base article 910070 is applied.
40000100 If the log reader agent and the DBCC DBREINDEX or ALTER INDEX ...REBUILD commands run at the same time, the log reader agent may miss some transactions to the distributor.
40000102 When you configure Log Shipping and add two or more secondary servers to Log Shipping, only the secondary server that you added last is monitored by the monitor server and the other secondary servers are not.
40000108 The SQL Server 2005 Database Engine Tuning Advisor may unexpectedly exit if indexed views are part of the workload that is tuned.
40000110 The YTD calculation in cubes that use parent-child dimensions may be incorrect.
50000104 The SqlCommandBuilder.DeriveParameters(SqlCommand) method returns an exception when you execute a stored procedure with an input parameter that is a typed XML type.
50000112 A member of the SQL Server 2005 Analysis Services database administrator role cannot add or delete perspectives. Only a member of the SQL Server 2005 Analysis Services server role can perform such tasks.
50000115 A Report Server Model may use an Analysis Services data source. MDX queries are generated from Report Builder based on the Report Server Model. Dates in the MDX queries have the month and day parts transposed.
50000116 A FETCH operation that uses a KEYSET cursor may be slow if the target table for the cursor has a clustered index and non-clustered indexes. This behavior occurs if the clustered index is the best choice for the FETCH operation and if the SELECT query for the cursor is very simple.
50000120 In SQL Server 2005 Analysis Services, calculated members that are defined in the cube MDX script are resolved before any session or query defined calculations. The only exceptions are certain scenarios with the Aggregate and VisualTotals functions. This behavior is a change from the SQL Server 2000 Analysis Services behavior. In SQL Server 2000 Analysis Services, the Solve Order could be used explicitly to insert a session or a query-defined calculation between two cube level calculations. This change in behavior may cause the query or session scope calculated members not to return the results that you want in SQL Server 2005 Analysis Services. Cumulative hotfix package for SQL Server 2005 (build 9.0.2153) introduces new syntax that allows for control over the scope in which a calculated member is evaluated.
50000151 When you run a SELECT query to return any duplicate rows in a table, incorrect results are returned if the query plan contains a hash aggregation and if one or more of the columns in the table contain a combination of empty strings and undefined characters.
50000154 You may receive a variablelock list error message when you run multiple instances of a package.
You can download the hotfix here
Friday, May 19, 2006
Customers who bought this item also bought...
Customers who bought this item also bought....by now you probably know that I am talking about Amazon.com
So let's try to implement our own (simple) Amazon system
We will call it People who bought this song also bought these songs
So basically we will create 3 tables (ArtistTable, Songtable and SongPaid)
These are very basic tables and they are missing a lot of info, I created them so that the query is nice and simple
So if person A buys song 3 then we want to return all the songs (except song 3) from customers who also bought song 3 (except customer A)
we we also list how many times a song was bought, so that we can see what the most popular songs were that people bought who also bought the same song that we bought
Let's create our tables first
CREATE TABLE ArtistTable (ID INT PRIMARY KEY, ArtistName VARCHAR(500))
INSERT INTO ArtistTable VALUES(1,'Red Hot Chilli Peppers')
INSERT INTO ArtistTable VALUES(2,'Pearl Jam')
INSERT INTO ArtistTable VALUES(3,'Astral Projection')
INSERT INTO ArtistTable VALUES(4,'Chemical Brothers')
CREATE TABLE Songtable (ID INT PRIMARY KEY , SongName VARCHAR(500),ArtistID INT)
INSERT INTO Songtable VALUES(1,'Under The Bridge',1)
INSERT INTO Songtable VALUES(2,'Black',2)
INSERT INTO Songtable VALUES(3,'Mahadeva',3)
INSERT INTO Songtable VALUES(4,'Block Rockin Beats',4)
INSERT INTO Songtable VALUES(5,'Setting Sun',4)
INSERT INTO Songtable VALUES(6,'Alive',1)
INSERT INTO Songtable VALUES(7,'Give It Away Now',2)
CREATE TABLE SongPaid (SongID INT, CustID INT)
INSERT INTO SongPaid VALUES(1, 1)
INSERT INTO SongPaid VALUES(2, 1)
INSERT INTO SongPaid VALUES(1, 2)
INSERT INTO SongPaid VALUES(3, 3) -- Current Buyer (3)
INSERT INTO SongPaid VALUES(3, 4) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(4, 4) -- Also bought by 4 ('Block Rockin Beats')
INSERT INTO SongPaid VALUES(5, 4) -- Also bought by 4 ('Setting Sun')
INSERT INTO SongPaid VALUES(4, 5)
INSERT INTO SongPaid VALUES(4, 6)
INSERT INTO SongPaid VALUES(5, 6)
INSERT INTO SongPaid VALUES(3, 7) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(7, 7) -- Also bought by 7 ('Give It Away Now')
INSERT INTO SongPaid VALUES(4, 7) -- Also bought by 7 ('Block Rockin Beats')
CREATE INDEX ix_SongPaid ON SongPaid(SongID,CustID)
/*Now let's do the query with person 3 and song 3
our result set will be
SongID,SongName,ArtisName,SongCount
4,Block Rockin Beats,Chemical Brothers,2
5,Setting Sun Chemical Brothers,1
7,Give It Away Now,Pearl Jam,1
*/
DECLARE @SongId INT, @CustId INT
SELECT @SongId = 3,@CustId =3
SELECT s1.SongID ,st.Songname,a.ArtistName, Count(*) AS SongCount
FROM SongPaid s1
JOIN Songtable st on s1.SongID = st.ID
JOIN ArtistTable a on st.ArtistID = a.ID
WHERE EXISTS (
SELECT custid FROM SongPaid
WHERE SongID =@SongId AND CustID =s1.CustID )
AND SongID <> @SongId --Don't return the song we bought
AND CustID <> @CustId --Skip other songs we bought by skipping our customerid
GROUP BY s1.SongID ,st.Songname,a.ArtistName
The songs and bands I used in the tables are real so in case you don't know them here is some additional info with an Amazon link to the CD
'Under The Bridge' and 'Give It Away Now' are from the CD Blood Sugar Sex Magik by the Red Hot Chilli Peppers
'Black' and 'Alive' are from the CD Ten by Pearl Jam
'Mahadeva' is from the CD In The Mix by Astral Projection
'Setting Sun' and 'Block Rockin Beats'is from the CD Singles 93-03 by the Chemical Brothers
So let's try to implement our own (simple) Amazon system
We will call it People who bought this song also bought these songs
So basically we will create 3 tables (ArtistTable, Songtable and SongPaid)
These are very basic tables and they are missing a lot of info, I created them so that the query is nice and simple
So if person A buys song 3 then we want to return all the songs (except song 3) from customers who also bought song 3 (except customer A)
we we also list how many times a song was bought, so that we can see what the most popular songs were that people bought who also bought the same song that we bought
Let's create our tables first
CREATE TABLE ArtistTable (ID INT PRIMARY KEY, ArtistName VARCHAR(500))
INSERT INTO ArtistTable VALUES(1,'Red Hot Chilli Peppers')
INSERT INTO ArtistTable VALUES(2,'Pearl Jam')
INSERT INTO ArtistTable VALUES(3,'Astral Projection')
INSERT INTO ArtistTable VALUES(4,'Chemical Brothers')
CREATE TABLE Songtable (ID INT PRIMARY KEY , SongName VARCHAR(500),ArtistID INT)
INSERT INTO Songtable VALUES(1,'Under The Bridge',1)
INSERT INTO Songtable VALUES(2,'Black',2)
INSERT INTO Songtable VALUES(3,'Mahadeva',3)
INSERT INTO Songtable VALUES(4,'Block Rockin Beats',4)
INSERT INTO Songtable VALUES(5,'Setting Sun',4)
INSERT INTO Songtable VALUES(6,'Alive',1)
INSERT INTO Songtable VALUES(7,'Give It Away Now',2)
CREATE TABLE SongPaid (SongID INT, CustID INT)
INSERT INTO SongPaid VALUES(1, 1)
INSERT INTO SongPaid VALUES(2, 1)
INSERT INTO SongPaid VALUES(1, 2)
INSERT INTO SongPaid VALUES(3, 3) -- Current Buyer (3)
INSERT INTO SongPaid VALUES(3, 4) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(4, 4) -- Also bought by 4 ('Block Rockin Beats')
INSERT INTO SongPaid VALUES(5, 4) -- Also bought by 4 ('Setting Sun')
INSERT INTO SongPaid VALUES(4, 5)
INSERT INTO SongPaid VALUES(4, 6)
INSERT INTO SongPaid VALUES(5, 6)
INSERT INTO SongPaid VALUES(3, 7) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(7, 7) -- Also bought by 7 ('Give It Away Now')
INSERT INTO SongPaid VALUES(4, 7) -- Also bought by 7 ('Block Rockin Beats')
CREATE INDEX ix_SongPaid ON SongPaid(SongID,CustID)
/*Now let's do the query with person 3 and song 3
our result set will be
SongID,SongName,ArtisName,SongCount
4,Block Rockin Beats,Chemical Brothers,2
5,Setting Sun Chemical Brothers,1
7,Give It Away Now,Pearl Jam,1
*/
DECLARE @SongId INT, @CustId INT
SELECT @SongId = 3,@CustId =3
SELECT s1.SongID ,st.Songname,a.ArtistName, Count(*) AS SongCount
FROM SongPaid s1
JOIN Songtable st on s1.SongID = st.ID
JOIN ArtistTable a on st.ArtistID = a.ID
WHERE EXISTS (
SELECT custid FROM SongPaid
WHERE SongID =@SongId AND CustID =s1.CustID )
AND SongID <> @SongId --Don't return the song we bought
AND CustID <> @CustId --Skip other songs we bought by skipping our customerid
GROUP BY s1.SongID ,st.Songname,a.ArtistName
The songs and bands I used in the tables are real so in case you don't know them here is some additional info with an Amazon link to the CD
'Under The Bridge' and 'Give It Away Now' are from the CD Blood Sugar Sex Magik by the Red Hot Chilli Peppers
'Black' and 'Alive' are from the CD Ten by Pearl Jam
'Mahadeva' is from the CD In The Mix by Astral Projection
'Setting Sun' and 'Block Rockin Beats'is from the CD Singles 93-03 by the Chemical Brothers
Wednesday, May 17, 2006
List All Tables Without An Index
Here are 2 quick ways to list all tables in your database without an index
The first query is using sysobjects and the second query is using the INFORMATION_SCHEMA.TABLES view
SELECT name AS TableName
FROM sysobjects s
WHERE type = 'U'
AND OBJECTPROPERTY(id , 'TableHasIndex' ) = 0
ORDER BY TableName
SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME) , 'TableHasIndex' ) = 0
ORDER BY TableName
The first query is using sysobjects and the second query is using the INFORMATION_SCHEMA.TABLES view
SELECT name AS TableName
FROM sysobjects s
WHERE type = 'U'
AND OBJECTPROPERTY(id , 'TableHasIndex' ) = 0
ORDER BY TableName
SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME) , 'TableHasIndex' ) = 0
ORDER BY TableName
Tuesday, May 16, 2006
SQL Compare Without The Price Tag
You want to know the differences between 2 tables but don’t have the money to pay for SQL Compare by red-gate software and your trial version has expired ;-(
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)
In order to see what rows are in table1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
You can run this whole script in 1 shot
Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare
I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation
[Edit]
SQL server MVP Louis Davidson has posted a comment and this was his approach
Basically a FULL OUTER JOIN instead of a UNION
So here is his query
SELECT CASE WHEN t1.au_id IS NULL
AND t2.au_id IS NOT NULL
THEN 'Does Not Exist On Production'
WHEN t1.au_id IS NOT NULL
AND t2.au_id IS NULL
THEN 'Does Not Exist In Staging'
ELSE 'Data Mismatch' END,
COALESCE(t1.au_id, t2.au_id) AS au_id
FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
OR t2.au_id IS NULL
OR t1.bc <> t2.bc
Louis is also the author of the just released Pro SQL Server 2005 Database Design and Optimization book
[/edit]
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)
In order to see what rows are in table1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
You can run this whole script in 1 shot
Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare
I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation
[Edit]
SQL server MVP Louis Davidson has posted a comment and this was his approach
Basically a FULL OUTER JOIN instead of a UNION
So here is his query
SELECT CASE WHEN t1.au_id IS NULL
AND t2.au_id IS NOT NULL
THEN 'Does Not Exist On Production'
WHEN t1.au_id IS NOT NULL
AND t2.au_id IS NULL
THEN 'Does Not Exist In Staging'
ELSE 'Data Mismatch' END,
COALESCE(t1.au_id, t2.au_id) AS au_id
FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
OR t2.au_id IS NULL
OR t1.bc <> t2.bc
Louis is also the author of the just released Pro SQL Server 2005 Database Design and Optimization book
[/edit]
Subscribe to:
Posts (Atom)