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'.
Monday, June 26, 2006
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)
Subscribe to:
Posts (Atom)