How do you get the domain name that your SQL Server machine belongs to with T-SQL code?
You can use the undocumented procedure xp_regread; I have tested this on SQL Server 2005 and on SQL Server 2000.
Be advised that since this is an undocumented procedure that it might not be available in the future or it might be changed either because of a service pack or a hot fix. Don't use it as production code!
Here is how to do this
DECLARE @chvDomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon',
N'CachePrimaryDomain',@chvDomainName OUTPUT
SELECT @chvDomainName AS DomainName
Thursday, December 07, 2006
Monday, December 04, 2006
Visual Studio 2005 Team Edition for Database Professionals Released to Manufacturing
On Friday 1st December 2006 the development team officially announced that Data Dude was done and was being released to manufacturing. It should be available for download to MSDN subscribers on December 7th 2006 and generally available for purchase on January 1st 2007.
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
This release marks the end of the first leg in a very exciting journey to revolutionize the ALM space and finally bring the database into the application lifecycle.
You can find more info about Data Dude here: Visual Studio Team Edition for Database Professionals
Sunday, December 03, 2006
Top 5 Posts For November 2006
Below are the top 5 posts according to Google Analytics for the month of November 2006 in order by pageviews descending
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
How Are Dates Stored In SQL server?
SQL Query Optimizations
Check If Temporary Table Exists
SQL Server 2005 Script Repository
Five Ways To Return Values From Stored Procedures
Top SQL Server Google Searches For November 2006
These are the top SQL Searches on this site for the month of November. this time I left some of the searches that have nothing to do with SQL Server or programming (Like breeds of cats that produce less FEL D1s)
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Here is the list:
apprentice sample narrative report in bank
generate table script
breeds of cats that produce less FEL D1
CSV import MS SQL ASP
gravelines
case in sql server 2005
if condition in sql server 2005
search string in a string sql server
sql 2000 olap range
TOP DOWNLOAD SOFTWARE
169.254.x.x
DATE CONVERSIONS
sp_send_dbmail
As you can see if and case are frequent searches. Just yesterday I answered a case question; the poster had this code
set t7 =
case
when (t4 is null and t5 is null) then cast(0 as varchar)
when t4 is null then cast(t5 as varchar) else cast(t4 as varchar)
end
Which of course can be replaced with
set t7 =coalesce(t4,t5,0)
This will return the first non non value, since t7 is a varchar column there is no reason to convert to varchar either
Tuesday, November 28, 2006
Use SCHEMABINDING In Views To Protect Against Alteration Or Dropping Of Tables
My post from last night triggered something in me to write a little bit about SCHEMABINDING.
If you create a view and someone drops one of the tables that the view is dependent on then the view will fail. How can you protect yourself against that? You can use WITH SCHEMABINDING. Here is how you use it:
Let's first create some tables and the view
CREATE TABLE dbo.Test1 (ID INT, SomeName VARCHAR(49))
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test1 VALUES 1,'Name1')
INSERT dbo.Test1 VALUES( 2,'Name2')
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
CREATE VIEW dbo.TestTables
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--SELECT from the view and everything is fine
SELECT *
FROM dbo.TestTables
-- Drop table test2
DROP TABLE dbo.Test2
--Let's do the select again
SELECT * FROM dbo.TestTables
--And this is the error message that we get back
Server: Msg 208, Level 16, State 1, Procedure TestTables, Line 3
Invalid object name 'Test2'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'TestTables' because of binding errors.
--Let's create the Test2 table again
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
--Now let's alter the view and we will use SCHEMABINDING
ALTER VIEW dbo.TestTables WITH SCHEMABINDING
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--Try dropping the Test2 table again
DROP TABLE dbo.Test2
--And here is the message
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Test2' because it is being referenced by object 'TestTables'.
--Let's try altering the table by adding a column
ALTER TABLE dbo.Test2 ADD Col3 INT
-- That seems to work
SELECT * FROM dbo.Test2
SELECT * FROM dbo.TestTables
--Let's try altering the table by dropping a column
ALTER TABLE dbo.Test2 DROP COLUMN SomeOtherName
--Can't do that
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN SomeOtherName failed because one or more objects access this column.
--Let's try altering the table by changing a column size
ALTER TABLE dbo.Test2 ALTER COLUMN SomeOtherName VARCHAR(48)
--Same here; this is not allowed
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN SomeOtherName failed because one or more objects access this column.
--Let's drop the column that is not used by the view
ALTER TABLE dbo.Test2 DROP COLUMN Col3
--No problem here either, if a column is not used by the view it can be dropped
--Clean up the mess
DROP VIEW dbo.TestTables
DROP TABLE dbo.Test2, dbo.Test1
So there you have it; SCHEMABINDING is a nice way to protect your views from dropped tables, modified columns or dropped columns on tables that are used in views.
--You can add columns without a problem to the table used by a view.
--You can modify a column that is not used in a view.
--You can also drop a column that is not used in the view
If you create a view and someone drops one of the tables that the view is dependent on then the view will fail. How can you protect yourself against that? You can use WITH SCHEMABINDING. Here is how you use it:
Let's first create some tables and the view
CREATE TABLE dbo.Test1 (ID INT, SomeName VARCHAR(49))
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test1 VALUES 1,'Name1')
INSERT dbo.Test1 VALUES( 2,'Name2')
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
CREATE VIEW dbo.TestTables
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--SELECT from the view and everything is fine
SELECT *
FROM dbo.TestTables
-- Drop table test2
DROP TABLE dbo.Test2
--Let's do the select again
SELECT * FROM dbo.TestTables
--And this is the error message that we get back
Server: Msg 208, Level 16, State 1, Procedure TestTables, Line 3
Invalid object name 'Test2'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'TestTables' because of binding errors.
--Let's create the Test2 table again
CREATE TABLE dbo.Test2 (ID2 INT, SomeOtherName VARCHAR(49))
INSERT dbo.Test2 VALUES( 1,'OtherName1')
INSERT dbo.Test2 VALUES( 2,'OtherName2')
--Now let's alter the view and we will use SCHEMABINDING
ALTER VIEW dbo.TestTables WITH SCHEMABINDING
AS
SELECT ID,SomeName, ID2,SomeOtherName
FROM dbo.Test1 t1
JOIN dbo.Test2 t2 on t1.ID =t2.ID2
--Try dropping the Test2 table again
DROP TABLE dbo.Test2
--And here is the message
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.Test2' because it is being referenced by object 'TestTables'.
--Let's try altering the table by adding a column
ALTER TABLE dbo.Test2 ADD Col3 INT
-- That seems to work
SELECT * FROM dbo.Test2
SELECT * FROM dbo.TestTables
--Let's try altering the table by dropping a column
ALTER TABLE dbo.Test2 DROP COLUMN SomeOtherName
--Can't do that
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN SomeOtherName failed because one or more objects access this column.
--Let's try altering the table by changing a column size
ALTER TABLE dbo.Test2 ALTER COLUMN SomeOtherName VARCHAR(48)
--Same here; this is not allowed
Server: Msg 5074, Level 16, State 3, Line 1
The object 'TestTables' is dependent on column 'SomeOtherName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN SomeOtherName failed because one or more objects access this column.
--Let's drop the column that is not used by the view
ALTER TABLE dbo.Test2 DROP COLUMN Col3
--No problem here either, if a column is not used by the view it can be dropped
--Clean up the mess
DROP VIEW dbo.TestTables
DROP TABLE dbo.Test2, dbo.Test1
So there you have it; SCHEMABINDING is a nice way to protect your views from dropped tables, modified columns or dropped columns on tables that are used in views.
--You can add columns without a problem to the table used by a view.
--You can modify a column that is not used in a view.
--You can also drop a column that is not used in the view
Monday, November 27, 2006
Do You Use Partitioned Views Or Indexed Views In SQL Server 2000
I am in the process of interviewing people for a position at work. The people I have interviewed so far have between 8 and 10 years SQL experience. I noticed one thing; none of these people knew what partition views or horizontal partitioning is or what is involved with creating an index view (the SET options, SCHEMABINDING etc etc). I must say that I was surprised that people who worked with SQL Server since 1998 didn’t know about isolation levels, audit trails, difference between a unique constraint and primary key and the list goes on
So here is my question to you: Do you use partitioned views or indexed views?
I must admit that I have created indexed views only twice on a production box. Partitioned views I am using a lot more since the table that we had grew to 180 million rows and you can query it on a combination of columns. Creating partitioned views is not a big deal but it involves some maintenance if you add years or whatever your partitioned column is. Then you have to create does tables and add them to the view. Luckily SQL Server 2005 has partitioned functions and this makes it a lot easier
So is this an unreasonable question? It’s not like I asked the difference between a conversion and a cycle deadlock is it?
So here is my question to you: Do you use partitioned views or indexed views?
I must admit that I have created indexed views only twice on a production box. Partitioned views I am using a lot more since the table that we had grew to 180 million rows and you can query it on a combination of columns. Creating partitioned views is not a big deal but it involves some maintenance if you add years or whatever your partitioned column is. Then you have to create does tables and add them to the view. Luckily SQL Server 2005 has partitioned functions and this makes it a lot easier
So is this an unreasonable question? It’s not like I asked the difference between a conversion and a cycle deadlock is it?
Saturday, November 25, 2006
SQL Server - Best Practices Site Available On TechNet
Microsoft TechNet has a page named SQL Server - Best Practices; this site has white papers, scrips a toolbox and top 10 lists
From the site:
Here is a preview of some of the material that is available right now:
Technical White Papers
Deep level technical papers on specific SQL Server topics that were tested and validated by SQL Development
• SQL Server 2005 Performance Tuning using Waits and Queues
• TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
• Loading Bulk Data into a Partitioned Table
• DBCC SHOWCONTIG Improvements and Comparison between SQL Server 2000 and SQL Server 2005
• Database Mirroring Best Practices and Performance Considerations
Best Practices ToolBox
• Scripts and tools for performance tuning and troubleshooting SQL Server 2005
Top 10 Lists
Summary lists (usually consisting of 10 items) of recommendations, best practices and common issues for specific customer scenarios by the SQL Server Customer Advisory Team.
• Storage Top 10 Best Practices
• OLTP Top 6 Performance Issues for OLTP Applications
Best Practices in SQL Server Books Online• Best Practices for Replication Administration
• Replication Security Best Practices
A must for every SQL Server developer: SQL Server - Best Practices
From the site:
Get the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. Drawing on the extensive experience and expertise from respected developers and engineers at Microsoft as they walk you through the specifics on solving particularly difficult issues.
Here is a preview of some of the material that is available right now:
Technical White Papers
Deep level technical papers on specific SQL Server topics that were tested and validated by SQL Development
• SQL Server 2005 Performance Tuning using Waits and Queues
• TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
• Loading Bulk Data into a Partitioned Table
• DBCC SHOWCONTIG Improvements and Comparison between SQL Server 2000 and SQL Server 2005
• Database Mirroring Best Practices and Performance Considerations
Best Practices ToolBox
• Scripts and tools for performance tuning and troubleshooting SQL Server 2005
Top 10 Lists
Summary lists (usually consisting of 10 items) of recommendations, best practices and common issues for specific customer scenarios by the SQL Server Customer Advisory Team.
• Storage Top 10 Best Practices
• OLTP Top 6 Performance Issues for OLTP Applications
Best Practices in SQL Server Books Online• Best Practices for Replication Administration
• Replication Security Best Practices
A must for every SQL Server developer: SQL Server - Best Practices
Friday, November 24, 2006
Auto Update Statistics Asynchronously.
In SQL Server 2000 if the engine determined that the statistics are out of date it would update the statistics and then run the query. If you had a very long table this could take a long time. In SQL server 2005 by turning Auto Update Statistics Asynchronously On the query will run with the old statistics which is not optimal but will probably still be faster than updating the statistics first on a large table and then running the query. The statistics will be updated after the query and the next queries can take advantage of the updated statistics.
Usage:
AUTO_UPDATE_STATISTICS_ASYNC { ON OFF }
ON
Queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
OFF
Queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
So how do you enable this? Here is an example for the AdventureWorks database however setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON.
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
What does Books On Line have to say about this?
Asynchronous Statistics Updates
A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.
In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version.
The AUTO_UPDATE_STATISTICS_ASYNC option is set at the database level and determines the update method for all statistics in the database. It is applicable only to statistics updating and cannot be used to asynchronously create statistics. Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON. By default, the AUTO_UPDATE_STATISTICS_ASYNC option is OFF
Usage:
AUTO_UPDATE_STATISTICS_ASYNC { ON OFF }
ON
Queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
OFF
Queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
So how do you enable this? Here is an example for the AdventureWorks database however setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON.
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
What does Books On Line have to say about this?
Asynchronous Statistics Updates
A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.
In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version.
The AUTO_UPDATE_STATISTICS_ASYNC option is set at the database level and determines the update method for all statistics in the database. It is applicable only to statistics updating and cannot be used to asynchronously create statistics. Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is also set to ON. By default, the AUTO_UPDATE_STATISTICS_ASYNC option is OFF
Thursday, November 23, 2006
Happy Thanksgiving
This is not going to be a SQL post (again) So to have at least some SQL I am going to link to a post I did last year: Calculating Thanksgiving By Using SQL in SQL Server
So that’s that. I have some people coming over today and still have to cook all the stuff. The best part about thanksgiving besides the food and seeing the family is that we pick our secret Santa. Up until last year we would exchange with every family member and have a limit of $25. No we do it differently we pick one person from a hat and spend $125. This is much better because you only have to buy a gift for one person and you can get a nicer gift instead of several smaller gifts. So I usually give my list of Amazon links to the person and they can choose what to get me. Yes it’s not really a ‘secret’ Santa. I will post my list on Saturday so that you can see if I have any taste or not
Now another topic: weight gain. Yes the time between Thanksgiving and New Years day is the time when most people start putting on the pounds. A friend of mine has a blogpost named Fat America but I must warn you if you are a heavier person you might get offended with some of the language at the end of the post (the elevator part)
Anyway enjoy the food and drinks and have a good time
So that’s that. I have some people coming over today and still have to cook all the stuff. The best part about thanksgiving besides the food and seeing the family is that we pick our secret Santa. Up until last year we would exchange with every family member and have a limit of $25. No we do it differently we pick one person from a hat and spend $125. This is much better because you only have to buy a gift for one person and you can get a nicer gift instead of several smaller gifts. So I usually give my list of Amazon links to the person and they can choose what to get me. Yes it’s not really a ‘secret’ Santa. I will post my list on Saturday so that you can see if I have any taste or not
Now another topic: weight gain. Yes the time between Thanksgiving and New Years day is the time when most people start putting on the pounds. A friend of mine has a blogpost named Fat America but I must warn you if you are a heavier person you might get offended with some of the language at the end of the post (the elevator part)
Anyway enjoy the food and drinks and have a good time
Windows Vista Consumer Launch Countdown Gadget
There is a Windows Vista Consumer Launch Countdown Gadget available. There are 3 versions of this gadget (widget for you Mac fans) available:
one for Live.com pages, another for Windows Live Spaces, and a third for installation to the Windows Vista Sidebar.
If you need more info about this gadget then visit this link: http://www.livegadgets.net/#blog
Wednesday, November 22, 2006
Upgrading My Skills To SQL Server 2005
At work we are required to get training at least once a year. So I decided to take the Upgrading Your Database Development Skills To Microsoft SQL server 2005
At the end of this post you will see what I should learn. The things that I am most excited about are: Service Broker and Using SQL Management Objects. The new Transact SQL enhancements I know already; I have covered several of them in this blog.
I doubt that I will use the .NET CLR in SQL Server 2005; but I must say that I was amazed at how easy it is to set it up. Notification services could be something I could use but right now I have build my own.
The Native HTTP Support was really cool; it was strange to see WSDL generated from SQL Server. It is something that can be deployed internally since I know that we will never open up our SQL boxes at work to the outside world.
So today is the last day of the class and we have a break now. I must say going to training beats a book anytime. it just saves you so much time. This was a 3 day course. If I had to do it by myself with a book it would take me at least 2 weeks.
Talking about books I am also in the middle of reading Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
and Pro SQL Server 2005 Database Design and Optimization
and both are excellent books
So here is what was covered in this course:
Module 1: SQL Server 2005 Overview
The information in this module introduces the main new features and enhancements in SQL Server 2005 and describes the new development tools.
Lessons
SQL Server 2005 Components and Architecture
SQL Server Developer Tools
SQL Server 2005 Security Implementation
Lab 1: Exploring SQL Server 2005
Using SQL Server Management Studio
Using Schemas
Module 2: Transact-SQL Enhancements in SQL Server 2005
In this module, students learn about the improvements in Transact-SQL in SQL Server 2005.
Lessons
Data Definition Language Enhancements
Data Manipulation Language Enhancements
Structured Exception Handling
Lab 2: Programming with Transact-SQL
Partitioning a Table
Using Common Table Expressions
Using Relational Operators
Module 3: Using XML in SQL Server 2005
In this module, students learn about the XML-related functionality in the SQL Server 2005 database engine will be described.
Lessons
XML Enhancements in SQL Server 2005
The xml Data Type
Using XQuery
Lab 3: Working with XML
Retrieving XML from Relational Data
Storing XML Natively in the Databases
Using XQuery with xml Methods
Module 4: Using Service Broker
The information in this module introduces Microsoftr SQL ServerT 2005 Service Broker-a message-based platform for building service-oriented database solutions.
Lessons
Service Broker Architecture
Using the Service Broker
Lab 4: Using Service Broker
Creating Service Broker Objects
Implementing the Customer Service
Implementing the E-mail Service
Module 5: Using Native HTTP Support
In this module, students learn how to create HTTP endpoints that make database services available to Web services clients.
Lessons
Native HTTP Support in SQL Server 2005
Configuring Native HTTP Support
Lab 5: Implementing a Web Service with HTTP Endpoints
Create an HTTP Endpoint
Test the HTTP Endpoint
Secure the HTTP Endpoint
Module 6: Using Notification Services
The information in this module introduces Notification Services and how to develop Notification Services applications.
Lessons
Notification Services Architecture
Building Notification Services Solutions
Lab 6: Using Notification Services
Creating a Notification Services Application
Creating a Subscription Management Application
Creating an Event Provider
Module 7: Using the .NET CLR in SQL Server 2005
In this module, students learn how to implement managed code in a SQL Server 2005 database.
Lessons
SQL Server and the .NET CLR
Implementing Managed Code in SQL Server 2005
Lab 7: Implementing Managed Code in the Database
Creating a Managed Stored Procedure
Creating a Managed Function
Creating a Managed User-Defined Type
Module 8: Developing Client Applications
The information in this module introduces database developers to client application development techniques that use the data access classes provided in the .NET Framework.
Lessons
Developing Client Applications with ADO.NET
Data Binding in the .NET Framework 2.0
Lab 8: Developing Client Applications
Creating a Data Bound Windows Application
Creating a Data bound ASP.NET Application
Module 9: Using SQL Management Objects
The information in this module introduces the SQL Management Objects (SMO) Application Programming Interface (API) in Microsoftr SQL ServerT 2005, which provides classes that you can use to manage SQL Server. SQL Server 2005 also introduces Replication Management Objects (RMO) to allow automation of replication.
Lessons
Introduction to SQL Management Objects
Replication Management Objects
Lab 9: Using SQL Management Objects
Using SMO to Retrieve Server Information
Using SMO to Create Database Objects
Using SMO to Modify Database Objects
At the end of this post you will see what I should learn. The things that I am most excited about are: Service Broker and Using SQL Management Objects. The new Transact SQL enhancements I know already; I have covered several of them in this blog.
I doubt that I will use the .NET CLR in SQL Server 2005; but I must say that I was amazed at how easy it is to set it up. Notification services could be something I could use but right now I have build my own.
The Native HTTP Support was really cool; it was strange to see WSDL generated from SQL Server. It is something that can be deployed internally since I know that we will never open up our SQL boxes at work to the outside world.
So today is the last day of the class and we have a break now. I must say going to training beats a book anytime. it just saves you so much time. This was a 3 day course. If I had to do it by myself with a book it would take me at least 2 weeks.
Talking about books I am also in the middle of reading Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
and Pro SQL Server 2005 Database Design and Optimization and both are excellent booksSo here is what was covered in this course:
Module 1: SQL Server 2005 Overview
The information in this module introduces the main new features and enhancements in SQL Server 2005 and describes the new development tools.
Lessons
SQL Server 2005 Components and Architecture
SQL Server Developer Tools
SQL Server 2005 Security Implementation
Lab 1: Exploring SQL Server 2005
Using SQL Server Management Studio
Using Schemas
Module 2: Transact-SQL Enhancements in SQL Server 2005
In this module, students learn about the improvements in Transact-SQL in SQL Server 2005.
Lessons
Data Definition Language Enhancements
Data Manipulation Language Enhancements
Structured Exception Handling
Lab 2: Programming with Transact-SQL
Partitioning a Table
Using Common Table Expressions
Using Relational Operators
Module 3: Using XML in SQL Server 2005
In this module, students learn about the XML-related functionality in the SQL Server 2005 database engine will be described.
Lessons
XML Enhancements in SQL Server 2005
The xml Data Type
Using XQuery
Lab 3: Working with XML
Retrieving XML from Relational Data
Storing XML Natively in the Databases
Using XQuery with xml Methods
Module 4: Using Service Broker
The information in this module introduces Microsoftr SQL ServerT 2005 Service Broker-a message-based platform for building service-oriented database solutions.
Lessons
Service Broker Architecture
Using the Service Broker
Lab 4: Using Service Broker
Creating Service Broker Objects
Implementing the Customer Service
Implementing the E-mail Service
Module 5: Using Native HTTP Support
In this module, students learn how to create HTTP endpoints that make database services available to Web services clients.
Lessons
Native HTTP Support in SQL Server 2005
Configuring Native HTTP Support
Lab 5: Implementing a Web Service with HTTP Endpoints
Create an HTTP Endpoint
Test the HTTP Endpoint
Secure the HTTP Endpoint
Module 6: Using Notification Services
The information in this module introduces Notification Services and how to develop Notification Services applications.
Lessons
Notification Services Architecture
Building Notification Services Solutions
Lab 6: Using Notification Services
Creating a Notification Services Application
Creating a Subscription Management Application
Creating an Event Provider
Module 7: Using the .NET CLR in SQL Server 2005
In this module, students learn how to implement managed code in a SQL Server 2005 database.
Lessons
SQL Server and the .NET CLR
Implementing Managed Code in SQL Server 2005
Lab 7: Implementing Managed Code in the Database
Creating a Managed Stored Procedure
Creating a Managed Function
Creating a Managed User-Defined Type
Module 8: Developing Client Applications
The information in this module introduces database developers to client application development techniques that use the data access classes provided in the .NET Framework.
Lessons
Developing Client Applications with ADO.NET
Data Binding in the .NET Framework 2.0
Lab 8: Developing Client Applications
Creating a Data Bound Windows Application
Creating a Data bound ASP.NET Application
Module 9: Using SQL Management Objects
The information in this module introduces the SQL Management Objects (SMO) Application Programming Interface (API) in Microsoftr SQL ServerT 2005, which provides classes that you can use to manage SQL Server. SQL Server 2005 also introduces Replication Management Objects (RMO) to allow automation of replication.
Lessons
Introduction to SQL Management Objects
Replication Management Objects
Lab 9: Using SQL Management Objects
Using SMO to Retrieve Server Information
Using SMO to Create Database Objects
Using SMO to Modify Database Objects
SQL Hacks
O'Reilly came out with their latest SQL book: SQL Hacks
From their site:
Whether you're running Access, MySQL, SQL Server, Oracle, or PostgreSQL, this book will help you push the limits of traditional SQL to squeeze data effectively from your database. The book offers 100 hacks -- unique tips and tools -- that bring you the knowledge of experts who apply what they know in the real world to help you take full advantage of the expressive power of SQL. You'll find practical techniques to address complex data manipulation problems. Learn how to:
--Wrangle data in the most efficient way possible
--Aggregate and organize your data for meaningful and accurate reporting
--Make the most of subqueries, joins, and unions
--Stay on top of the performance of your queries and the server that runs them
--Avoid common SQL security pitfalls, including the dreaded SQL injection attack
Let SQL Hacks serve as your toolbox for digging up and manipulating data. If you love to tinker and optimize, SQL is the perfect technology and SQL Hacks is the must-have book for you.
Sample Chapter
A sample chapter is available: Hack 4: Date Handling (PDF Format)
Table Of Contents:
Chapter 1. SQL Fundamentals
1. Run SQL from the Command Line
2. Connect to SQL from a Program
3. Perform Conditional INSERTs
4. UPDATE the Database
5. Solve a Crossword Puzzle Using SQL
6. Don't Perform the Same Calculation Over and Over
Chapter 2. Joins, Unions, and Views
7. Modify a Schema Without Breaking Existing Queries
8. Filter Rows and Columns
9. Filter on Indexed Columns
10. Convert Subqueries to JOINs
11. Convert Aggregate Subqueries to JOINs
12. Simplify Complicated Updates
13. Choose the Right Join Style for Your Relationships
14. Generate Combinations
Chapter 3. Text Handling
15. Search for Keywords Without LIKE
16. Search for a String Across Columns
17. Solve Anagrams
18. Sort Your Email
Chapter 4. Date Handling
19. Convert Strings to Dates
20. Uncover Trends in Your Data
21. Report on Any Date Criteria
22. Generate Quarterly Reports
23. Second Tuesday of the Month
Chapter 5. Number Crunching
24. Multiply Across a Result Set
25. Keep a Running Total
26. Include the Rows Your JOIN Forgot
27. Identify Overlapping Ranges
28. Avoid Dividing by Zero
29. Other Ways to COUNT
30. Calculate the Maximum of Two Fields
31. Disaggregate a COUNT
32. Cope with Rounding Errors
33. Get Values and Subtotals in One Shot
34. Calculate the Median
35. Tally Results into a Chart
36. Calculate the Distance Between GPS Locations
37. Reconcile Invoices and Remittances
38. Find Transposition Errors
39. Apply a Progressive Tax
40. Calculate Rank
Chapter 6. Online Applications
41. Copy Web Pages into a Table
42. Present Data Graphically Using SVG
43. Add Navigation Features to Web Applications
44. Tunnel into MySQL from Microsoft Access
45. Process Web Server Logs
46. Store Images in a Database
47. Exploit an SQL Injection Vulnerability
48. Prevent an SQL Injection Attack
Chapter 7. Organizing Data
49. Keep Track of Infrequently Changing Values
50. Combine Tables Containing Different Data
51. Display Rows As Columns
52. Display Columns As Rows
53. Clean Inconsistent Records
54. Denormalize Your Tables
55. Import Someone Else's Data
56. Play Matchmaker
57. Generate Unique Sequential Numbers
Chapter 8. Storing Small Amounts of Data
58. Store Parameters in the Database
59. Define Personalized Parameters
60. Create a List of Personalized Parameters
61. Set Security Based on Rows
62. Issue Queries Without Using a Table
63. Generate Rows Without Tables
Chapter 9. Locking and Performance
64. Determine Your Isolation Level
65. Use Pessimistic Locking
66. Use Optimistic Locking
67. Lock Implicitly Within Transactions
68. Cope with Unexpected Redo
69. Execute Functions in the Database
70. Combine Your Queries
71. Extract Lots of Rows
72. Extract a Subset of the Results
73. Mix File and Database Storage
74. Compare and Synchronize Tables
75. Minimize Bandwidth in One-to-Many Joins
76. Compress to Avoid LOBs
Chapter 10. Reporting
77. Fill in Missing Values in a Pivot Table
78. Break It Down by Range
79. Identify Updates Uniquely
80. Play Six Degrees of Kevin Bacon
81. Build Decision Tables
82. Generate Sequential or Missing Data
83. Find the Top n in Each Group
84. Store Comma-Delimited Lists in a Column
85. Traverse a Simple Tree
86. Set Up Queuing in the Database
87. Generate a Calendar
88. Test Two Values from a Subquery
89. Choose Any Three of Five
Chapter 11. Users and Administration
90. Implement Application-Level Accounts
91. Export and Import Table Definitions
92. Deploy Applications
93. Auto-Create Database Users
94. Create Users and Administrators
95. Issue Automatic Updates
96. Create an Audit Trail
Chapter 12. Wider Access
97. Allow an Anonymous Account
98. Find and Stop Long-Running Queries
99. Don't Run Out of Disk Space
100. Run SQL from a Web Page
And for $19.79 on Amazon this is a great gift
Tuesday, November 21, 2006
SQL Server 2005 - Service Pack 2 - Web Chat Today At 9AM PST
The SQL Server Product Team will have a web chat later today
From their site:
If you have any questions regarding Service Pack 2, please join us in a public web chat tomorrow between 9:00am - 10:00am Pacific Time.
Here are a couple of links where you might have already stumbled on this:
http://www.microsoft.com/technet/community/chats/default.mspx
http://www.microsoft.com/communities/chats/default.mspx
Event: Service Pack 2 Web Chat
Date: Tuesday, November 21, 2006
Time: 9:00am - 10:00am (Pacific)
Thanks,
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
From their site:
If you have any questions regarding Service Pack 2, please join us in a public web chat tomorrow between 9:00am - 10:00am Pacific Time.
Here are a couple of links where you might have already stumbled on this:
http://www.microsoft.com/technet/community/chats/default.mspx
http://www.microsoft.com/communities/chats/default.mspx
Event: Service Pack 2 Web Chat
Date: Tuesday, November 21, 2006
Time: 9:00am - 10:00am (Pacific)
Thanks,
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
Sunday, November 19, 2006
Twisted Sister: Twisted Christmas
At work I overheard two people getting excited about the Twisted Sister Christmas album. What?
How in the world can you get excited about that? So I decided to check it out on Amazon. There are 7 reviews and the average is 4 stars. so if you are stuck in the 80's go check it out here
The CD has 10 songs:
1. Have Yourself A Merry Little Christmas
2. Oh Come All Ye Faithful
3. White Christmas
4. I'll Be Home For Christmas
5. Silver Bells
6. I Saw Mommy Kissing Santa Claus
7. Let It Snow, Let It Snow, Let It Snow
8. Deck The Halls
9. The Christmas Song (Chestnuts Roasting On An Open Fire)
10. Heavy Metal Christmas (The Twelve Days Of Christmas)
The biggest surpise is the song Heavy Metal Christmas" which is the Twisted Sister version of "The Twelve Days of Christmas"
Instead of this:
twelve drummers drumming
eleven pipers piping
ten lords a leaping
nine ladies dancing
eight maids a milking
seven swans a swimming
six geese a laying
five gold rings
four calling birds
three french hens
two turtle doves
and a partridge in a pear tree.
You get this gem:
On my heavy metal Christmas my true love gave to me,
12 Silver crosses
11 Black mascaras
10 Pairs of platforms
9 Tattered t-shirts
8 Pentagrams
7 Leather jackets
6 Cans of hairspray
5 Skull earrings
4 Quarts of Jack
3 Studded belts
2 Pairs of spandex pants
And a tattoo of Ozzy!
Fun stuff
Wednesday, November 15, 2006
Visual Studio Team Edition for Database Professionals RTM has been set for Nov. 30, 2006
Microsoft has made a couple of announcements at PASS today; one of them is that the release to manufacture date for Visual Studio Team Edition for Database Professionals has been set for Nov. 30, 2006. Here is what has been announced:
The availability of the community technology preview (CTP) of SP2 for SQL Server 2005 delivers key updates, including data compression, increased business intelligence functionality, security updates relating to Common Criteria, manageability enhancements, support for Windows Vista™ and optimization for the 2007 Microsoft Office system environment. More details about SP2 are available. The SP2 November CTP can be downloaded immediately.
• The RC of SQL Server 2005 Compact Edition is a new offering for essential relational database functionality in a compact footprint. By sharing a familiar SQL syntax and common ADO.NET programming model with other SQL Server editions, SQL Server Compact Edition allows developers and administrators to apply their existing skills and be immediately productive while building and supporting the next generation of applications. The RC is available via Web download.
• The release to manufacture date for Visual Studio Team Edition for Database Professionals has been set for Nov. 30, 2006. Visual Studio Team Edition for Database Professionals provides a foundation for change management, development, testing and deployment of databases through integrated functionality that enables database developers and administrators to be more productive, reduce risk and drive quality. More information can be found on MSDN.
• Plans to provide process guidance for database professionals are included in the Microsoft Solutions Framework. This process guidance is the first of its kind in the industry and demonstrates Microsoft’s commitment to making the database professional a full-fledged participant in the application life cycle.
The availability of the community technology preview (CTP) of SP2 for SQL Server 2005 delivers key updates, including data compression, increased business intelligence functionality, security updates relating to Common Criteria, manageability enhancements, support for Windows Vista™ and optimization for the 2007 Microsoft Office system environment. More details about SP2 are available. The SP2 November CTP can be downloaded immediately.
• The RC of SQL Server 2005 Compact Edition is a new offering for essential relational database functionality in a compact footprint. By sharing a familiar SQL syntax and common ADO.NET programming model with other SQL Server editions, SQL Server Compact Edition allows developers and administrators to apply their existing skills and be immediately productive while building and supporting the next generation of applications. The RC is available via Web download.
• The release to manufacture date for Visual Studio Team Edition for Database Professionals has been set for Nov. 30, 2006. Visual Studio Team Edition for Database Professionals provides a foundation for change management, development, testing and deployment of databases through integrated functionality that enables database developers and administrators to be more productive, reduce risk and drive quality. More information can be found on MSDN.
• Plans to provide process guidance for database professionals are included in the Microsoft Solutions Framework. This process guidance is the first of its kind in the industry and demonstrates Microsoft’s commitment to making the database professional a full-fledged participant in the application life cycle.
Going Mobile with SQLServer Compact Edition Podcast On Channel 9
Channel 9 has a podcast about SQL Server Compact Edition. From the site:
"Do you remember when having a mobile phone was a big deal? I can still recall the time when I bought my first “Car Phone” as we called it. It came with a big box that you put under the seat and worked only in the car. It was pretty cool.
Then later I got a big phone that I carried with me on trains and planes and when I made a call people would stare and comment on how important I must be to have such a device. Today even my 12 year old has a mobile phone and my 8 year old wants one. No doubt about it; mobile devices are becoming more and more important every day and I believe that they will become the most pervasive computing platform over time.
The question I have is, what do architects need to know about designing solutions for this platform? Well on this episode I’m joined by Nick Randolph .Net Compact Framework MVP who has been building solutions and has some thoughts for us. "
Download the mp3 or wma version of the podcast here
"Do you remember when having a mobile phone was a big deal? I can still recall the time when I bought my first “Car Phone” as we called it. It came with a big box that you put under the seat and worked only in the car. It was pretty cool.
Then later I got a big phone that I carried with me on trains and planes and when I made a call people would stare and comment on how important I must be to have such a device. Today even my 12 year old has a mobile phone and my 8 year old wants one. No doubt about it; mobile devices are becoming more and more important every day and I believe that they will become the most pervasive computing platform over time.
The question I have is, what do architects need to know about designing solutions for this platform? Well on this episode I’m joined by Nick Randolph .Net Compact Framework MVP who has been building solutions and has some thoughts for us. "
Download the mp3 or wma version of the podcast here
Tuesday, November 14, 2006
Use SESSIONPROPERTY To Check If NUMERIC_ROUNDABORT Is On Or Off
You want to create an indexed view; however creating an indexed view is not as straight forward as creating an index on a table. The view must be created with the SCHEMABINDING option.
The following SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.
The view must not reference any other views, only base tables. There are more restrictions, you can look them up by reading Creating an Indexed View in Books On Line.
So how do you find out if NUMERIC_ROUNDABORT is set to ON or OFF? Since we are using SQL server 2005 we can use the sys.dm_exec_sessions view
SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
But wait there is no column named numeric_roundabort. Okay then let's use the old SQL server 2000 DBCC USEROPTIONS
After executing DBCC USEROPTIONS this is the output for me
DBCC USEROPTIONS
-----------------------------
textsize 64512
language us_english
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
Still no luck. But there is hope because SQL Server 2005 offers the SESSIONPROPERTY function
Let's do a quick test
SET NUMERIC_ROUNDABORT ON
SELECT SESSIONPROPERTY('numeric_roundabort') --1
SET NUMERIC_ROUNDABORT OFF
SELECT SESSIONPROPERTY('numeric_roundabort') --0
Now let's create our own view with all the set options needed for creating indexed views included
CREATE VIEW UserOptionsExpanded
AS
SELECT session_id,concat_null_yields_null,
quoted_identifier,
ansi_nulls,
ansi_padding,
ansi_warnings,
arithabort,SESSIONPROPERTY('numeric_roundabort') AS numeric_roundabort
FROM sys.dm_exec_sessions
Let's select from the view
SELECT *
FROM UserOptionsExpanded
WHERE session_id = @@spid
And yes all the columns are there
Of course we could have just executed the following and be done ;-)
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
Let's see what else we can pass into the SESSIONPROPERTY function
ANSI_NULLS
Specifies whether the SQL-92 compliant behavior of equals (=) and not equal to (<>) against null values is applied.
1 = ON
0 = OFF
ANSI_PADDING
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in character and binary data.
1 = ON
0 = OFF
ANSI_WARNINGS
Specifies whether the SQL-92 standard behavior of raising error messages or warnings for certain conditions, including divide-by-zero and arithmetic overflow, is applied.
1 = ON
0 = OFF
ARITHABORT
Determines whether a query is ended when an overflow or a divide-by-zero error occurs during query execution.
1 = ON
0 = OFF
CONCAT_NULL_YIELDS_ NULL
Controls whether concatenation results are treated as null or empty string values.
1 = ON
0 = OFF
NUMERIC_ROUNDABORT
Specifies whether error messages and warnings are generated when rounding in an expression causes a loss of precision.
1 = ON
0 = OFF
QUOTED_IDENTIFIER
Specifies whether SQL-92 rules about how to use quotation marks to delimit identifiers and literal strings are to be followed.
1 = ON
0 = OFF
[Any other string]
NULL = Input is not valid.
So it looks like SESSIONPROPERTY takes only the SET options required to create an indexed view. Interesting indeed.
The following SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.
The view must not reference any other views, only base tables. There are more restrictions, you can look them up by reading Creating an Indexed View in Books On Line.
So how do you find out if NUMERIC_ROUNDABORT is set to ON or OFF? Since we are using SQL server 2005 we can use the sys.dm_exec_sessions view
SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
But wait there is no column named numeric_roundabort. Okay then let's use the old SQL server 2000 DBCC USEROPTIONS
After executing DBCC USEROPTIONS this is the output for me
DBCC USEROPTIONS
-----------------------------
textsize 64512
language us_english
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
Still no luck. But there is hope because SQL Server 2005 offers the SESSIONPROPERTY function
Let's do a quick test
SET NUMERIC_ROUNDABORT ON
SELECT SESSIONPROPERTY('numeric_roundabort') --1
SET NUMERIC_ROUNDABORT OFF
SELECT SESSIONPROPERTY('numeric_roundabort') --0
Now let's create our own view with all the set options needed for creating indexed views included
CREATE VIEW UserOptionsExpanded
AS
SELECT session_id,concat_null_yields_null,
quoted_identifier,
ansi_nulls,
ansi_padding,
ansi_warnings,
arithabort,SESSIONPROPERTY('numeric_roundabort') AS numeric_roundabort
FROM sys.dm_exec_sessions
Let's select from the view
SELECT *
FROM UserOptionsExpanded
WHERE session_id = @@spid
And yes all the columns are there
Of course we could have just executed the following and be done ;-)
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
Let's see what else we can pass into the SESSIONPROPERTY function
ANSI_NULLS
Specifies whether the SQL-92 compliant behavior of equals (=) and not equal to (<>) against null values is applied.
1 = ON
0 = OFF
ANSI_PADDING
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in character and binary data.
1 = ON
0 = OFF
ANSI_WARNINGS
Specifies whether the SQL-92 standard behavior of raising error messages or warnings for certain conditions, including divide-by-zero and arithmetic overflow, is applied.
1 = ON
0 = OFF
ARITHABORT
Determines whether a query is ended when an overflow or a divide-by-zero error occurs during query execution.
1 = ON
0 = OFF
CONCAT_NULL_YIELDS_ NULL
Controls whether concatenation results are treated as null or empty string values.
1 = ON
0 = OFF
NUMERIC_ROUNDABORT
Specifies whether error messages and warnings are generated when rounding in an expression causes a loss of precision.
1 = ON
0 = OFF
QUOTED_IDENTIFIER
Specifies whether SQL-92 rules about how to use quotation marks to delimit identifiers and literal strings are to be followed.
1 = ON
0 = OFF
[Any other string]
NULL = Input is not valid.
So it looks like SESSIONPROPERTY takes only the SET options required to create an indexed view. Interesting indeed.
Monday, November 13, 2006
sys.sp_estimated_rowsize reduction_for_vardecimal
By now you probably know that Service Pack 2 has added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits.
This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
So what is the name of this new stored procedure? The name is sys.sp_estimated_rowsize_reduction_for_vardecimal . The proc returns the following columns: avg_rowlen_fixed_format, avg_rowlen_vardecimal_format and row_count
How do you call this proc?
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'TableName'
If your table is named OrderDetails you would call it like this
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'OrderDetails'
Read this post from the SQL Server Storage Engine team for more info
This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
So what is the name of this new stored procedure? The name is sys.sp_estimated_rowsize_reduction_for_vardecimal . The proc returns the following columns: avg_rowlen_fixed_format, avg_rowlen_vardecimal_format and row_count
How do you call this proc?
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'TableName'
If your table is named OrderDetails you would call it like this
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'OrderDetails'
Read this post from the SQL Server Storage Engine team for more info
Thursday, November 09, 2006
SQL Server 2005 Service Pack 2 CTP (November 2006) Download Links
The other day I wrote that SQL Server 2005 Service Pack 2 CTP would be available; here are the download links:
SQL Server 2005 Service Pack 2 CTP (November 2006)
SQL Server 2005 Express Edition and Tools Service Pack 2 CTP (November 2006)
SQL Server 2005 Books Online Service Pack 2 CTP (November 2006)
Feature Pack for SQL Server 2005 Service Pack 2 CTP (November 2006)
Additional Sources of SQL Server 2005 Information
Enjoy
SQL Server 2005 Service Pack 2 CTP (November 2006)
SQL Server 2005 Express Edition and Tools Service Pack 2 CTP (November 2006)
SQL Server 2005 Books Online Service Pack 2 CTP (November 2006)
Feature Pack for SQL Server 2005 Service Pack 2 CTP (November 2006)
Additional Sources of SQL Server 2005 Information
Enjoy
Wednesday, November 08, 2006
Windows Vista RTM
Vista has been released to manufacturing!
Windows Vista Blog
http://windowsvistablog.com/blogs/windowsvista/archive/2006/11/08/it-s-time.aspx
Media Alert: Microsoft Announces Conference Call With Jim Allchin
http://www.microsoft.com/presspass/press/2006/nov06/11-08AllchinMA.mspx
Q&A: Microsoft Windows Vista Released to Manufacturing
http://www.microsoft.com/presspass/features/2006/nov06/11-08VistaRTM.mspx
Channel 9 also has a video on their site: Jim Allchin: It's time - Windows Vista RTM
Windows Vista Blog
http://windowsvistablog.com/blogs/windowsvista/archive/2006/11/08/it-s-time.aspx
Media Alert: Microsoft Announces Conference Call With Jim Allchin
http://www.microsoft.com/presspass/press/2006/nov06/11-08AllchinMA.mspx
Q&A: Microsoft Windows Vista Released to Manufacturing
http://www.microsoft.com/presspass/features/2006/nov06/11-08VistaRTM.mspx
Channel 9 also has a video on their site: Jim Allchin: It's time - Windows Vista RTM
Tuesday, November 07, 2006
SQL Server 2005 Service Pack 2 CTP
Microsoft is releasing the SQL Server 2005 Service Pack 2 Community Technology Preview Tuesday, November 7th.
Here are some of the changes:
Database Engine
To see all the changes go here
Here are some of the changes:
Database Engine
- Maintenance plans are now supported by the SQL Server Database Services installation. Before SP2, you were required to install SQL Server 2005 Integration Services (SSIS) to run maintenance plans on a server-only installation.
- Maintenance plans now support multiserver environments, logging to remote servers, and multiple schedules. For more information, see How to: Create Multiserver Maintenance Plans How to: Create a Maintenance Plan and How to: Add or Modify Maintenance Plan Subplan Schedules.
- Added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
- Added logon triggers and a common criteria compliance enabled Option for sp_configure to support common criteria in the SQL Server 2005 Enterprise Edition.
- The sqllogship application is now supported. This application performs a backup, copy, or restore operation and associated clean-up tasks for a log shipping configuration.
- Plan cache improvements that provide improved system performance, better use of the available physical memory for database pages, and the ability to return text XML query plans that contain an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_text_query_plan table-valued function.
- SMO for Relational Engine Features
- Table.CheckIdentityValue() correctly generates the schema name for the fully qualified object name.
- Column.AddDefaultConstraint() works against table columns for SQL Server 2000 database instances.
- Table.CheckIdentityValue() correctly generates the schema name for the fully qualified object name.
To see all the changes go here
SQL Server 2005 Script Repository
Found this on the TechNet site; sample scripts and stored procedures for managing and working with SQL Server 2005. However on the TechNet site the code is spread ove several pages. I decided to make a user-friendly list instead
Buffer Cache
Sample scripts and stored procedures for managing and monitoring the SQL Server buffer cache.
Retrieve Buffer Counts by Object and Index
CPU and Optimization
Sample scripts and stored procedures for optimizing CPU performance in SQL Server.
Determine CPU Resources Required for Optimization
Retrieve Parallel Statements With the Highest Worker Time
Retrieve Statements with the Highest Plan Re-Use Counts
Retrieve Statements with the Lowest Plan Re-Use Counts
Indexes and Indexing
Sample scripts and stored procedures for working with SQL Server indexes.
Analyze Index Statistics
Create/Truncate an Indexstats Table
Determine Index Cost Benefits
Identify Missing Indexes
List Indexes With the Most Contention
Retrieve Index Statistics
Retrieve Index Usage Statistics
Retrieve Indexes Not Used Since the Last Recycle Time
Retrieve Object and Index Fragmentation Information
Retrieve Tables, Indexes, Files, and File Groups Information
Input/Output
Sample scripts and stored procedures for managing and monitoring input and output in SQL Server.
Calculate Average Stalls
List Queries That Could Benefit From an Index
List Rarely-Used Indexes
List Statements By Input/Output Usage
Performance (General)
Sample scripts and stored procedures for managing and monitoring general performance issue in SQL Server.
List Cached Plans Where Worker Time Exceeds Elapsed Time
List Currently-Executing Parallel Plans
List Recompiled Statements
List Runnable Queues
List Statements With the Highest Average CPU Time
List Statements with the Highest Execution Counts
List Top Wait Types for a Workload
Processor Cache
Sample scripts and stored procedures for managing the SQL Server processor cache.
Compare Single-Use and Re-Used Plans
List Statements By Plan Re-Use Count
SQL Text
Sample scripts and stored procedures for working with SQL text.
Retrieve a SQL Statement with a Specified .SQL_Handle
Retrieve SQL Text and XML Plans
SQLOS
Sample scripts and stored procedures for working with the new SQL Server Operating System Layer.
Compare Signal Waits and Resource Waits
List Currently-Executing Statements
List Scheduler Wait List Information
List Schedulers, Workers, and Runnable Queues
List Session and Scheduler ID Information
List SQLOS Execution Model Information
List Statements from a Specified Waiter List
Tempdb
Sample scripts and stored procedures for working with the tempdb system database.
List Real Time Tempdb Task Usage
List Real-Time Tempdb Statements
Transactions and Logging
Sample scripts for working with SQL Server transactions and locks.
Compare Locking and Repeatable Reads
Configure a Block Condition
Handle a Block Condition
List Lock and Serializable Information
List Locks and Reads
List Locks and Repeatable Reads
List Real-Time Blocker and Waiter Statements
Report Blocker and Waiter SQL Statements
Waitstats
Sample scripts and stored procedures for working with SQL Server waitstats.
Retrieve Waiter List Information
Retrieve Waitstat Snapshots
Buffer Cache
Sample scripts and stored procedures for managing and monitoring the SQL Server buffer cache.
Retrieve Buffer Counts by Object and Index
CPU and Optimization
Sample scripts and stored procedures for optimizing CPU performance in SQL Server.
Determine CPU Resources Required for Optimization
Retrieve Parallel Statements With the Highest Worker Time
Retrieve Statements with the Highest Plan Re-Use Counts
Retrieve Statements with the Lowest Plan Re-Use Counts
Indexes and Indexing
Sample scripts and stored procedures for working with SQL Server indexes.
Analyze Index Statistics
Create/Truncate an Indexstats Table
Determine Index Cost Benefits
Identify Missing Indexes
List Indexes With the Most Contention
Retrieve Index Statistics
Retrieve Index Usage Statistics
Retrieve Indexes Not Used Since the Last Recycle Time
Retrieve Object and Index Fragmentation Information
Retrieve Tables, Indexes, Files, and File Groups Information
Input/Output
Sample scripts and stored procedures for managing and monitoring input and output in SQL Server.
Calculate Average Stalls
List Queries That Could Benefit From an Index
List Rarely-Used Indexes
List Statements By Input/Output Usage
Performance (General)
Sample scripts and stored procedures for managing and monitoring general performance issue in SQL Server.
List Cached Plans Where Worker Time Exceeds Elapsed Time
List Currently-Executing Parallel Plans
List Recompiled Statements
List Runnable Queues
List Statements With the Highest Average CPU Time
List Statements with the Highest Execution Counts
List Top Wait Types for a Workload
Processor Cache
Sample scripts and stored procedures for managing the SQL Server processor cache.
Compare Single-Use and Re-Used Plans
List Statements By Plan Re-Use Count
SQL Text
Sample scripts and stored procedures for working with SQL text.
Retrieve a SQL Statement with a Specified .SQL_Handle
Retrieve SQL Text and XML Plans
SQLOS
Sample scripts and stored procedures for working with the new SQL Server Operating System Layer.
Compare Signal Waits and Resource Waits
List Currently-Executing Statements
List Scheduler Wait List Information
List Schedulers, Workers, and Runnable Queues
List Session and Scheduler ID Information
List SQLOS Execution Model Information
List Statements from a Specified Waiter List
Tempdb
Sample scripts and stored procedures for working with the tempdb system database.
List Real Time Tempdb Task Usage
List Real-Time Tempdb Statements
Transactions and Logging
Sample scripts for working with SQL Server transactions and locks.
Compare Locking and Repeatable Reads
Configure a Block Condition
Handle a Block Condition
List Lock and Serializable Information
List Locks and Reads
List Locks and Repeatable Reads
List Real-Time Blocker and Waiter Statements
Report Blocker and Waiter SQL Statements
Waitstats
Sample scripts and stored procedures for working with SQL Server waitstats.
Retrieve Waiter List Information
Retrieve Waitstat Snapshots
Saturday, November 04, 2006
How Are Dates Stored In SQL Server?
Internally dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.
So if we run the following code for the base date (1900/01/01)
DECLARE @d DATETIME
SELECT @d = '1900-01-01 00:00:00.000'
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go
The results are
DateInt DateBinary
----------- ----------
0 0x00000000
TimeInt TimeBinary
----------- ----------
0 0x00000000
If we use the max date 9999/12/31
DECLARE @d DATETIME
SELECT @d = '9999-12-31 23:59:59.997'
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go
we get the following result
DateInt DateBinary
----------- ----------
2958463 0x002D247F
TimeInt TimeBinary
----------- ----------
25919999 0x018B81FF
If you take binary values and convert to datetime you get the following results
SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second
------------------------------------------------------
--1900-01-01 00:00:00.003
SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks
------------------------------------------------------
--1900-01-01 00:00:01.000
SELECT CONVERT(INT,0x12C) --= 300
SELECT CONVERT(VARBINARY(3),300) --= 0x00012C
SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day
------------------------------------------------------
--1900-01-02 00:00:00.000
For smalldatetime the time is stored as the number of minutes after midnight
Now here is some fun stuff
DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000
DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:24:00.000
DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:52:48.000
DECLARE @d DATETIME
SELECT @d = '0'
SELECT @d
GO
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.
DECLARE @d DATETIME
SELECT @d = 0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000
So there is no implicit conversion, o is fine 'o' is not
DECLARE @d DATETIME
SELECT @d = 20061030
SELECT @d
GO
Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
DECLARE @d DATETIME
SELECT @d = '20061030'
SELECT @d
GO
------------------------------------------------------
--2006-10-30 00:00:00.000
Here we have the reverse, the varchar value is fine but the int is not.
This happens because the max integer value that a datetime can take is 36523
If we run the following we are okay
DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO
------------------------------------------------------
--9999-12-31 00:00:00.000
So if we run the following code for the base date (1900/01/01)
DECLARE @d DATETIME
SELECT @d = '1900-01-01 00:00:00.000'
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go
The results are
DateInt DateBinary
----------- ----------
0 0x00000000
TimeInt TimeBinary
----------- ----------
0 0x00000000
If we use the max date 9999/12/31
DECLARE @d DATETIME
SELECT @d = '9999-12-31 23:59:59.997'
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go
we get the following result
DateInt DateBinary
----------- ----------
2958463 0x002D247F
TimeInt TimeBinary
----------- ----------
25919999 0x018B81FF
If you take binary values and convert to datetime you get the following results
SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second
------------------------------------------------------
--1900-01-01 00:00:00.003
SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks
------------------------------------------------------
--1900-01-01 00:00:01.000
SELECT CONVERT(INT,0x12C) --= 300
SELECT CONVERT(VARBINARY(3),300) --= 0x00012C
SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day
------------------------------------------------------
--1900-01-02 00:00:00.000
For smalldatetime the time is stored as the number of minutes after midnight
Now here is some fun stuff
DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000
DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:24:00.000
DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:52:48.000
DECLARE @d DATETIME
SELECT @d = '0'
SELECT @d
GO
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.
DECLARE @d DATETIME
SELECT @d = 0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000
So there is no implicit conversion, o is fine 'o' is not
DECLARE @d DATETIME
SELECT @d = 20061030
SELECT @d
GO
Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
DECLARE @d DATETIME
SELECT @d = '20061030'
SELECT @d
GO
------------------------------------------------------
--2006-10-30 00:00:00.000
Here we have the reverse, the varchar value is fine but the int is not.
This happens because the max integer value that a datetime can take is 36523
If we run the following we are okay
DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO
------------------------------------------------------
--9999-12-31 00:00:00.000
Friday, November 03, 2006
ISO-11179 Naming Conventions
Straight from the man himself comes this statement posted in the microsoft.public.sqlserver.programming forum: "You need to read ISO-11179 so you use proper data element names. You
actually had "tbl-"on the table names! Sometimes "id" id a
prefix and sometimes it is a postfix."
Of course you know who I am talking about? No? Joe Celko of course. So what is ISO-11179?
The 11179 standard is a multipart standard that includes the following parts:
11179-1
Part 1: Framework, introduces and discusses fundamental ideas of data elements, value domains, data element concepts, conceptual domains, and classification schemes essential to the understanding of this set of standards and provides the context for associating the individual parts of ISO/IEC 11179.
11179-2
Part 2: Classification, provides a conceptual model for managing classification schemes. There are many structures used to organize classification schemes and there are many subject matter areas that classification schemes describe. So, this Part also provides a two-faceted classification for classification schemes themselves.
11179-3
Part 3: Registry Metamodel and Basic Attributes, specifies a conceptual model for a metadata registry. It is limited to a set of basic attributes for data elements, data element concepts, value domains, conceptual domains, classification schemes, and other related classes, called administered items. The basic attributes specified for data elements in ISO/IEC 11179-3:1994 are provided in this revision.
11179-4
Part 4: Formulation of Data Definitions, provides guidance on how to develop unambiguous data definitions. A number of specific rules and guidelines are presented in ISO/IEC 11179-4 that specify exactly how a data definition should be formed. A precise, well-formed definition is one of the most critical requirements for shared understanding of an administered item; well-formed definitions are imperative for the exchange of information. Only if every user has a common and exact understanding of the data item can it be exchanged trouble-free.
11179-5
Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item's definition and value domain.
11179-6
Part 6: Registration, provides instruction on how a registration applicant may register a data item with a central Registration Authority and the allocation of unique identifiers for each data item. Maintenance of administered items already registered is also specified in this document.
The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below
Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages
So check it out and hopefully you and your team can adapt a common naming conventions instead of having things named like employee_address, EmployeeAddress, employeeAddress and tblEmployeeAddress.
actually had "tbl-"on the table names! Sometimes "id" id a
prefix and sometimes it is a postfix."
Of course you know who I am talking about? No? Joe Celko of course. So what is ISO-11179?
The 11179 standard is a multipart standard that includes the following parts:
11179-1
Part 1: Framework, introduces and discusses fundamental ideas of data elements, value domains, data element concepts, conceptual domains, and classification schemes essential to the understanding of this set of standards and provides the context for associating the individual parts of ISO/IEC 11179.
11179-2
Part 2: Classification, provides a conceptual model for managing classification schemes. There are many structures used to organize classification schemes and there are many subject matter areas that classification schemes describe. So, this Part also provides a two-faceted classification for classification schemes themselves.
11179-3
Part 3: Registry Metamodel and Basic Attributes, specifies a conceptual model for a metadata registry. It is limited to a set of basic attributes for data elements, data element concepts, value domains, conceptual domains, classification schemes, and other related classes, called administered items. The basic attributes specified for data elements in ISO/IEC 11179-3:1994 are provided in this revision.
11179-4
Part 4: Formulation of Data Definitions, provides guidance on how to develop unambiguous data definitions. A number of specific rules and guidelines are presented in ISO/IEC 11179-4 that specify exactly how a data definition should be formed. A precise, well-formed definition is one of the most critical requirements for shared understanding of an administered item; well-formed definitions are imperative for the exchange of information. Only if every user has a common and exact understanding of the data item can it be exchanged trouble-free.
11179-5
Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item's definition and value domain.
11179-6
Part 6: Registration, provides instruction on how a registration applicant may register a data item with a central Registration Authority and the allocation of unique identifiers for each data item. Maintenance of administered items already registered is also specified in this document.
The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below
Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages
So check it out and hopefully you and your team can adapt a common naming conventions instead of having things named like employee_address, EmployeeAddress, employeeAddress and tblEmployeeAddress.
Thursday, November 02, 2006
SQL Teaser
Try to guess what the output will be of the following SQL Select statement (don't worry, you can't get it right ;-)
First guess then run this
SELECT 123.654, 123d6, 123e4, '123'e4
Surprise!!!!!!
I lifted/stole/borrowed the select statement from Mladen Prajdic's blog
First guess then run this
SELECT 123.654, 123d6, 123e4, '123'e4
Surprise!!!!!!
I lifted/stole/borrowed the select statement from Mladen Prajdic's blog
Top 5 Posts For October 2006
Below are the top 5 posts according to Google Analytics for the month of October 2006 in order by pageviews descending
Five Ways To Return Values From Stored Procedures
SQL Query Optimizations
Do You Know How Between Works With Dates?
NULL Trouble In SQL Server Land
SQL Server 2000 Undocumented Procedures For Files, Drives and Directories
The reason Five Ways To Return Values From Stored Procedures was number one is because it was posted on digg. Once something makes it to digg and people seemed to like it then you can expect thousands of hits or even more (The Digg Effect) So far 33 people have dugg this story. The link to the digg URL is below
http://digg.com/programming/Five_Ways_To_Return_Values_From_Stored_Procedures
After that 23 people saved it on del.icio.us that link is below
http://del.icio.us/url/ed6cbc71d79ae5f56ac75fe4122ee772
I will update the top 10 of all time (below the masthead) later today
[edit]Why wait? I have updated the top 10, you can see it here: http://sqlservercode.blogspot.com/2006/05/top-10-articles-of-all-time.html[/edit]
Five Ways To Return Values From Stored Procedures
SQL Query Optimizations
Do You Know How Between Works With Dates?
NULL Trouble In SQL Server Land
SQL Server 2000 Undocumented Procedures For Files, Drives and Directories
The reason Five Ways To Return Values From Stored Procedures was number one is because it was posted on digg. Once something makes it to digg and people seemed to like it then you can expect thousands of hits or even more (The Digg Effect) So far 33 people have dugg this story. The link to the digg URL is below
http://digg.com/programming/Five_Ways_To_Return_Values_From_Stored_Procedures
After that 23 people saved it on del.icio.us that link is below
http://del.icio.us/url/ed6cbc71d79ae5f56ac75fe4122ee772
I will update the top 10 of all time (below the masthead) later today
[edit]Why wait? I have updated the top 10, you can see it here: http://sqlservercode.blogspot.com/2006/05/top-10-articles-of-all-time.html[/edit]
Wednesday, November 01, 2006
Top SQL Server Google Searches For October 2006
These are the top SQL Searches on this site for the month of October. I have left out searches that have nothing to do with SQL Server or programming (Like Jessica Alba Playboy Pics)
Here is the list:
Line 1: Incorrect syntax near 'TRY'.
pivot
execute specific dts steps
"The maximum allowed length of the install path is 36"
ole The provider ran out of memory.
execute dts step active x
El proveedor OLE DB 'SQLOLEDB' indica que el objeto no tiene columnas.
locks
The provider ran out of memory
SQL ADD 1 DAY TO CURRENT DATE
dts activex
xp_sendmail attachments
I always find it interesting to see what people are searching for and it also gives me ideas for things to write about
So I will cover PIVOT and UNPIVOT and SQL ADD 1 DAY TO CURRENT DATE by the end of this week
Later today I will post the top 5 post of October and I will also update the top 10 posts of all time
Here is the list:
Line 1: Incorrect syntax near 'TRY'.
pivot
execute specific dts steps
"The maximum allowed length of the install path is 36"
ole The provider ran out of memory.
execute dts step active x
El proveedor OLE DB 'SQLOLEDB' indica que el objeto no tiene columnas.
locks
The provider ran out of memory
SQL ADD 1 DAY TO CURRENT DATE
dts activex
xp_sendmail attachments
I always find it interesting to see what people are searching for and it also gives me ideas for things to write about
So I will cover PIVOT and UNPIVOT and SQL ADD 1 DAY TO CURRENT DATE by the end of this week
Later today I will post the top 5 post of October and I will also update the top 10 posts of all time
Tuesday, October 31, 2006
Download A FREE Trial Of Spotlight On SQL Server And Win A Xbox 360
I just received an email from Quest Software. Quest is giving away 15 Xbox 360™ game systems in 15 days! Download a free trial of Spotlight® on SQL Server Enterprise and you'll be automatically entered into the Quest "Xbox-a-Day Giveaway" drawing.Now you have a chance to win the Xbox 360™ and detect, diagnose and resolve database performance issues with ease—just by trying Spotlight on SQL Server Enterprise.
What is Spotlight on SQL Server Enterprise?
Spotlight on SQL Server Enterprise is an award-winning database diagnostics tool that can help you ensure data availability and prevent problems before they occur.
Discover, diagnose and resolve SQL Server performance issues
Spotlight on SQL Server Enterprise provides an agent-less easy-to-use database issue discovery solution that enables you to identify and resolve SQL Server performance problems within your SQL Server environment. This powerful tool pinpoints the underlying SQL server contention issues and processes for fast and efficient database administration.
With Spotlight, DBAs can drill down to locate in-depth information about the source of thousands of SQL Server performance problems such as: a specific user, a resource-intensive SQL transaction, an I/O bottleneck, a lock or wait. Spotlight for SQL Server Enterprise sets a baseline for normal activity for each instance, and can set thresholds, notify users and display alerts when it detects performance bottlenecks of any kind.
Spotlight not only monitors the SQL Server Environment, but the underlying operating system on which it resides. From an overview screen, DBAs can view the most active SQL Server sessions, SQL statements, Replication information , blocks, deadlocks, waits, and disk activityto pinpoint and alleviate problems before they occur before and seriously impact end users.
Through one single interface, Spotlight on SQL Server Enterprise answers many questions related to performance:
How is each SQL Server instance performing?
How is the SQL Server Environment as a whole performing?
Where and when are the performance bottlenecks occurring?
How do I catch the problems before they arise?
Who or what is causing the problem and how do I resolve it?
Go HERE for more details
Monday, October 30, 2006
Working Four Days A week Until Next Year
I will be working four days a week only until next year. I did not take a vacation this year since my wife was pregnant with twins. We couldn't go anywhere because the due date was the first week of August; twins usually arrive a couple of weeks earlier. Since we didn't know the exact due date and had to go for monitoring every week we decided not to go anywhere. the end result is that I have 8 (out of 10) vacation days left. So I will be taking wednesdays off until 2007.
I used to live in the Netherlands where everyone has five weeks vacation. So when I came to the United States I was surprised that there was not a law like that here. Besides the five weeks of vacation in Holland you also get 8% of your salary as vacation money. So when you go on vacation you can use that as spending money.
I saved the 'best' for last: if you are unemployed in Holland then you have the same rights. that's right five weeks vacation, 8% vacation money. In Holland you can be unemplyed for years and years, in the US you get 6 months unemployment and the cap is $405 a week. After 6 months you are on your own. I think they should do exactly the same in Holland (no wonder the top tax bracket was 72% in the late nineties; it is I believe 52% now). I knew a lot of people who would be unemployed and work off the books...........
I used to live in the Netherlands where everyone has five weeks vacation. So when I came to the United States I was surprised that there was not a law like that here. Besides the five weeks of vacation in Holland you also get 8% of your salary as vacation money. So when you go on vacation you can use that as spending money.
I saved the 'best' for last: if you are unemployed in Holland then you have the same rights. that's right five weeks vacation, 8% vacation money. In Holland you can be unemplyed for years and years, in the US you get 6 months unemployment and the cap is $405 a week. After 6 months you are on your own. I think they should do exactly the same in Holland (no wonder the top tax bracket was 72% in the late nineties; it is I believe 52% now). I knew a lot of people who would be unemployed and work off the books...........
Friday, October 27, 2006
Halloween SQL Teaser
Try to guess without running the code which of the following 3 queries will return a value for maxvalue that is not null
Is it A, B or C?
--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO
--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO
--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO
Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT
So what does this have to do with Halloween? Nothing really.
Is it A, B or C?
--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO
--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO
--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO
Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT
So what does this have to do with Halloween? Nothing really.
One Of The Reasons I Participate In Forums And Newsgroups
This is one of the reasons I participate in SQL Server newsgroups and forums
A person had the following update statement
On the table that got updated (Object_data) the following trigger was created
I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows
I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway
This is the response I got
"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:
20,784 records in the test.
1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.
That's really something to me! Thanks again!"
You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer
A person had the following update statement
Update Object_data
set External_Claim_Number = BWCClaimNum
From yson_Claims_eDocs_10_27_2006
where LastName = INJ_lname
and FirstName = INJ_fname
and SSN = inj_ssn
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
and convert(varchar,pkclaim) = internal_claim_Number
and BWCClaimNum is not null
and BWCClaimNum <> External_Claim_Number
On the table that got updated (Object_data) the following trigger was created
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
declare @rows int
declare @object_Data_ID int
set @rows = @@ROWCOUNT
if @rows = 0
return
if @rows = 1
begin
select @object_Data_Id = object_data_id
from inserted
goto singlerecord
end
declare curObject insensitive cursor for
select object_data_id
from inserted
open curObject
fetch next from curObject into @object_data_id
while (@@fetch_status <> -1)
begin
singlerecord:
insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
YEAR_NUM,DateChanged,ChangedBy,Change
,PROV_FAX_NUM)
select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
getdate(),user_name(),'U'
,PROV_FAX_NUM
from inserted where object_data_id = @object_data_id
if @rows = 1
return
fetch next from curObject into @object_data_id
end
close curObject
deallocate curObject
I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
IF @@ROWCOUNT =0
RETURN
insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
YEAR_NUM,DateChanged,ChangedBy,Change
,PROV_FAX_NUM)
select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
getdate(),user_name(),'U'
,PROV_FAX_NUM
from inserted
I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway
This is the response I got
"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:
20,784 records in the test.
1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.
That's really something to me! Thanks again!"
You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer
What Are You Watching On Halloween?
I have difficulty picking a movie to watch on Halloween.
Did any of you (my readers) see any of these movies below? Which movie would you pick?
Omen (2006 Edition)
The Gingerdead Man
Saw I
Saw II
Exorcist (Begining)
Seed of Chucky
Ringu
Texas Chainsaw
Silent hill
Hills have eyes
Underworld (evolutions)
Leave me a comment or send me an email by using the Contact Me link.
Did any of you (my readers) see any of these movies below? Which movie would you pick?
Omen (2006 Edition)
The Gingerdead Man
Saw I
Saw II
Exorcist (Begining)
Seed of Chucky
Ringu
Texas Chainsaw
Silent hill
Hills have eyes
Underworld (evolutions)
Leave me a comment or send me an email by using the Contact Me link.
Thursday, October 26, 2006
SQL Server experienced revenue growth of over 30%
Microsoft Corp. today announced first quarter revenue of $10.81 billion for the period ended September 30, 2006, an 11% increase over the same quarter of the prior year. Operating income for the quarter was $4.47 billion, an 11% increase compared with $4.05 billion in the prior year period. Net income and diluted earnings per share for the first quarter were $3.48 billion and $0.35 per share. For the same quarter of the previous year, net income and diluted earnings per share were $3.14 billion and $0.29, including a $0.02 per share charge for certain legal charges.
Server & Tools revenue increased 17% over the comparable quarter in the prior year, reflecting healthy performance for offerings such as SQL Server™ 2005, Windows Server®, Visual Studio® 2005 and BizTalk® Server. SQL Server experienced revenue growth of over 30%, as customers are increasingly deploying SQL Server for their mission critical, transaction-oriented databases.
Good news if you are a SQL Server developer
Server & Tools revenue increased 17% over the comparable quarter in the prior year, reflecting healthy performance for offerings such as SQL Server™ 2005, Windows Server®, Visual Studio® 2005 and BizTalk® Server. SQL Server experienced revenue growth of over 30%, as customers are increasingly deploying SQL Server for their mission critical, transaction-oriented databases.
Good news if you are a SQL Server developer
Wednesday, October 25, 2006
Enable xp_cmdshell In SQL Server 2005 With The Surface Configuration Tool Or With A Script
This question popped up in a newsgroup today. How do I enable xp_cmdshell in SQL Server 2005? Well you can do it two ways; one is with a script and the other with the Surface Configuration Tool
Let's start with the Surface Configuration Tool
Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool
Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply
SQL Script
To do it with a script use the one below
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Let's start with the Surface Configuration Tool
Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool
Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply
SQL Script
To do it with a script use the one below
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
http://sqlservercode.blogspot.com In Top 100000 On Technorati
This blog is currently ranked 90,981 on Technorati
Rank: 90,981 (65 links from 30 blogs)
Not bad I guess, I wonder how long it will take to reach 50000?
The rank was 150000 just a couple of months ago so it's going towards the top (but will never reach it ;-( )
You can check the ranking here http://www.technorati.com/search/sqlservercode.blogspot.com and see who is linking here
Rank: 90,981 (65 links from 30 blogs)
Not bad I guess, I wonder how long it will take to reach 50000?
The rank was 150000 just a couple of months ago so it's going towards the top (but will never reach it ;-( )
You can check the ranking here http://www.technorati.com/search/sqlservercode.blogspot.com and see who is linking here
Microsoft Changing Live Search Back To MSN Search
Just saw The Island the other day and I must say I liked it a lot. I don't know why the movie didn't do as well as expected. The stars of the movie are Scarlett Johansson (Lost in Translation, Girl with a Pearl Earring) and Ewan McGregor ( Star Wars: Episode III - Revenge of the Sith , Trainspotting , Moulin Rouge!) other prominent roles are for Sean Bean, Steve Buscemi and Michael Clarke Duncan.. The movie is directed by Michael Bay who did Pearl Harbor and Armageddon. Michael Bay is currently working on The Hitcher and Transformers The links are all to IMDB in case you want more details about any of the movies.
The best part of the movie: the car chase.
The car chase is probably the best ever; it even beats the one from the second Matrix movie.
The movie takes place in the year 2050 and people have clones made as insurance; if their liver gives up then they just take the liver from the clone. These clones live isolated underground and have no idea what's going on; they think that they survived some contamination. Every now and then a lottery takes place and the winner goes to the Island. In reality the winner goes to the operating table. I won't give away the whole story you watch that yourself
There is some major product placement in this movie. The Xbox is featured in the movie alongside MSN Search; that's right not Windows Live Search. I guess they can always release a director's cut and 'fix' that. Cadillac has some top of the line car that costs half a million dollars; How much is half a million in the year 2050? To me it seems that a Porsche Carrera would cost about $500000 in about 50 years. Anyway the 'real' Ewan in the movie, who is very rich complains when his 'very' expensive car gets trashed. Would he really get that upset over the car; his clone is more expensive and he tries to kill the clone.
If you can catch the movie on HBO/On Demand then watch this movie. If you don’t want to watch the whole movie then at least watch the car scene; you won’t regret it
The best part of the movie: the car chase.
The car chase is probably the best ever; it even beats the one from the second Matrix movie.
The movie takes place in the year 2050 and people have clones made as insurance; if their liver gives up then they just take the liver from the clone. These clones live isolated underground and have no idea what's going on; they think that they survived some contamination. Every now and then a lottery takes place and the winner goes to the Island. In reality the winner goes to the operating table. I won't give away the whole story you watch that yourself
There is some major product placement in this movie. The Xbox is featured in the movie alongside MSN Search; that's right not Windows Live Search. I guess they can always release a director's cut and 'fix' that. Cadillac has some top of the line car that costs half a million dollars; How much is half a million in the year 2050? To me it seems that a Porsche Carrera would cost about $500000 in about 50 years. Anyway the 'real' Ewan in the movie, who is very rich complains when his 'very' expensive car gets trashed. Would he really get that upset over the car; his clone is more expensive and he tries to kill the clone.
If you can catch the movie on HBO/On Demand then watch this movie. If you don’t want to watch the whole movie then at least watch the car scene; you won’t regret it
Monday, October 23, 2006
Do You Know How Between Works With Dates?
Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned.
Let's test this out, first let's create this table
CREATE TABLE SomeDates (DateColumn DATETIME)
Insert 2 values
INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
This works without a problem
Let's add some more dates including the time portion
INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored
Now if we change 2006-10-02 to 2006-10-03 we get what we want
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn
Now insert a value for 2006-10-03 (midnight)
INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')
Run the query again
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn
We get back 2006-10-03 00:00:00.000; between will return the date if it is exactly midnight
If you use >= and < then you get exactly what you need
SELECT *
FROM SomeDates
WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
ORDER BY DateColumn
--Clean up
DROP TABLE SomeDates
So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums
Let's test this out, first let's create this table
CREATE TABLE SomeDates (DateColumn DATETIME)
Insert 2 values
INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
This works without a problem
Let's add some more dates including the time portion
INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored
Now if we change 2006-10-02 to 2006-10-03 we get what we want
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn
Now insert a value for 2006-10-03 (midnight)
INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')
Run the query again
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn
We get back 2006-10-03 00:00:00.000; between will return the date if it is exactly midnight
If you use >= and < then you get exactly what you need
SELECT *
FROM SomeDates
WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
ORDER BY DateColumn
--Clean up
DROP TABLE SomeDates
So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums
Sunday, October 22, 2006
SQL Server 2005 Practical Troubleshooting: The Database Engine Sample Chapter Available
Ken Henderson has posted a sample chapter from the forthcoming book SQL Server 2005 Practical Troubleshooting: The Database Engine on his blog. The sample chapter is: Chapter 4 Procedure Cache Issues. This is a must read for anyone who is troubleshooting SQL Server queries and procedures. Some of the things covered are: Cached Object Leaks, Cursor Leaks, Parameter Sniffing, Excessive Compilation, Poor Plan Reuse and How Cache Lookups Work. I think that this is the first time I have seen parameter sniffing described in a book. Check it out and let me know what you think.
SQLQueryStress Beta 1 Available For Download
Adam Machanic has made available beta 1 of the SQLQueryStress tool. It provides some support for query parameterization and options for collection of I/O and CPU metrics. It is not intended to replace tools such as Visual Studio Team System's load tests, but rather to be a simple and easy-to-use tool in the DBA or database developer's kit.
To download the tool and read the documentation go here: http://www.datamanipulation.net/SQLQueryStress/
To download the tool and read the documentation go here: http://www.datamanipulation.net/SQLQueryStress/
Subscribe to:
Posts (Atom)