Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57
You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that
Below is an example
DECLARE @v MONEY
SELECT @v = 1322323.6666
SELECT CONVERT(VARCHAR,@v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function. The work around is to cast it to money
DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57 Formatted with commas
Thursday, December 29, 2005
Friday, December 23, 2005
How To Resolve A Deadlock
I stumbled upon this SQL Server technical bulletin and it gives one example of how to resolve a deadlock. Typical methods you can use to resolve deadlocks include:
Adding and dropping indexes.
Adding index hints.
Modifying the application to access resources in a similar pattern.
Removing activity from the transaction like triggers. By default, triggers are transactional.
Keeping transactions as short as possible.
I won't go into details here since you can read it all at the following link (SQL Server technical bulletin - How to resolve a deadlock)
Also check out the following deadlock links
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Troubleshooting Deadlocks
Lock Compatibility
And of course if you really want to know everything about locking I recommend the following book (in PDF format): Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking by Kalen Delaney (Inside SQL Server 2000)
Adding and dropping indexes.
Adding index hints.
Modifying the application to access resources in a similar pattern.
Removing activity from the transaction like triggers. By default, triggers are transactional.
Keeping transactions as short as possible.
I won't go into details here since you can read it all at the following link (SQL Server technical bulletin - How to resolve a deadlock)
Also check out the following deadlock links
Deadlocking
Handling Deadlocks
Minimizing Deadlocks
Troubleshooting Deadlocks
Lock Compatibility
And of course if you really want to know everything about locking I recommend the following book (in PDF format): Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking by Kalen Delaney (Inside SQL Server 2000)
Wednesday, December 21, 2005
SQL Server 2005 System Table Map PDF Download
A couple of days back I reported that the current issue of SQL Server Magazine includes a poster of all the SQL Server 2005 System Tables. Well Microsoft has made this poster available as a PDF download now. You can get it here (SQL Server 2005 System Table Map)
Monday, December 19, 2005
SQL Query Optimizations
Below are a couple of small SQL query optimization tips
I have include the execution plan pictures for some of the queries so that you can see the difference
Don’t use * but list the columns
SELECT pub_name,city FROM dbo.publishers instead of SELECT * FROM dbo.publishers
If you would have a covering index on the columns pub_name and city then the table would not be accessed at all and all the data would be returned from the index. This would also reduce the logical reads. You can use STATISTICS IO to find out how many logical reads you would have
SELECT pub_name,city FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
SELECT * FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Don’t use arithmetic operators on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE OrderID*3 = 33000
SELECT * FROM Orders WHERE OrderID = 33000/3
Don’t use functions on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE LEFT(CustomerID,1) ='V'
SELECT * FROM Orders WHERE CustomerID LIKE 'V%'
Don’t convert a datetime column in the where clause
SELECT * FROM Orders WHERE OrderDate = '1996-07-04'
SELECT * FROM Orders WHERE CONVERT(CHAR(10),OrderDate,120) = '1996-07-04'
As you can see the query is many time faster if you don't convert
I have include the execution plan pictures for some of the queries so that you can see the difference
Don’t use * but list the columns
SELECT pub_name,city FROM dbo.publishers instead of SELECT * FROM dbo.publishers
If you would have a covering index on the columns pub_name and city then the table would not be accessed at all and all the data would be returned from the index. This would also reduce the logical reads. You can use STATISTICS IO to find out how many logical reads you would have
SELECT pub_name,city FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
SELECT * FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Don’t use arithmetic operators on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE OrderID*3 = 33000
SELECT * FROM Orders WHERE OrderID = 33000/3
Don’t use functions on a column in the where clause
You will get an index scan instead of a seek
SELECT * FROM Orders WHERE LEFT(CustomerID,1) ='V'
SELECT * FROM Orders WHERE CustomerID LIKE 'V%'
Don’t convert a datetime column in the where clause
SELECT * FROM Orders WHERE OrderDate = '1996-07-04'
SELECT * FROM Orders WHERE CONVERT(CHAR(10),OrderDate,120) = '1996-07-04'
As you can see the query is many time faster if you don't convert
Thursday, December 15, 2005
Test SQL Server Login Permissions With SETUSER
Sometimes you get a request to create a login and you want to test the permissions before letting the user know that he can use the login. you just don't feel like login in and trying to run some SQL statements for every user.
With the SETUSER statement you can eliminate that. Here is some code that explains how to use it
--Create the user
EXEC sp_addlogin 'Albert', 'food', 'pubs'
EXEC sp_adduser 'Albert'
CREATE TABLE dbo.test(id INT identity,datefield DATETIME)
INSERT INTO test
SELECT GETDATE()
SELECT * FROM test -- as dbo
-- let's run the select as Albert
SETUSER 'Albert'
SELECT * FROM test -- as albert
Now you should get this error
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'test', database 'pubs', owner 'dbo'.
execute just the SETUSER statement and you can drop Albert since the user will be reset to the original user
SETUSER
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'
or close the query window and in another window execute the code below to drop Albert
You have to do this in another window since as Albert you don't have permissions to do this
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'
With the SETUSER statement you can eliminate that. Here is some code that explains how to use it
--Create the user
EXEC sp_addlogin 'Albert', 'food', 'pubs'
EXEC sp_adduser 'Albert'
CREATE TABLE dbo.test(id INT identity,datefield DATETIME)
INSERT INTO test
SELECT GETDATE()
SELECT * FROM test -- as dbo
-- let's run the select as Albert
SETUSER 'Albert'
SELECT * FROM test -- as albert
Now you should get this error
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'test', database 'pubs', owner 'dbo'.
execute just the SETUSER statement and you can drop Albert since the user will be reset to the original user
SETUSER
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'
or close the query window and in another window execute the code below to drop Albert
You have to do this in another window since as Albert you don't have permissions to do this
EXEC sp_dropuser 'Albert'
EXEC sp_droplogin 'Albert'
Wednesday, December 14, 2005
SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach
SQL Server 2005 T-SQL Recipes: A Problem-Solution ApproachThis book has been published it is 768 pages and published by Apress and you can order it from amazon
You can download chapter 6 here to get a feel for the book
Need to brush up on specific SQL Server tasks, procedures, or Transact-SQL commands? Not finding what you need from SQL Server books online? Or perhaps you just want to familiarize yourself with the practical application of new T-SQL–related features. SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach is an ideal book, whatever your level as a DBA or developer.
This “no-fluff” desk reference offers direct access to the information you need to get the job done. It covers basic T-SQL data manipulation, the use of stored procedures, triggers and UDFs, and advanced T-SQL techniques for database security and maintenance. It also provides hundreds of practical recipes that describe the utilities of features and functions, with a minimim of background theory.
Additionally, this book provides “how-to” answers to common SQL Server T-SQL questions, conceptual overviews, and highlights of new features introduced in SQL Server 2005. It also features concise T-SQL syntax examples, and you can use the book to prepare for a SQL Server-related job interview or certification test.
Below is the table of contents
CHAPTER 1 SELECT
CHAPTER 2 INSERT, UPDATE, DELETE
CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking
CHAPTER 4 Tables
CHAPTER 5 Indexes
CHAPTER 6 Full-Text Search
CHAPTER 7 Views
CHAPTER 8 SQL Server Functions
CHAPTER 9 Conditional Processing, Control-Of-Flow, and Cursors
CHAPTER 10 Stored Procedures
CHAPTER 11 User-Defined Functions and Types
CHAPTER 12 Triggers
CHAPTER 13 CLR Integration
CHAPTER 14 XML
CHAPTER 15 Web Services
CHAPTER 16 Error Handling
CHAPTER 17 Principals
CHAPTER 18 Securables and Permissions
CHAPTER 19 Encryption
CHAPTER 20 Service Broker
CHAPTER 21 Configuring and Viewing SQL Server Options
CHAPTER 22 Creating and Configuring Databases
CHAPTER 23 Database Integrity and Optimization
CHAPTER 24 Maintaining Database Objects and Object Dependencies
CHAPTER 25 Database Mirroring
CHAPTER 26 Database Snapshots
CHAPTER 27 Linked Servers and Distributed Queries
CHAPTER 28 Performance Tuning
CHAPTER 29 Backup and Recovery
Tuesday, December 13, 2005
SQL Server 2005 Free E-Learning
Whether you are interested in database administration, database development, or business intelligence, there is some free * E-Learning to help you get up to speed on the newest features of the software. The E-Learning courses, valued at $99 each, are an effective way to learn on your own schedule and feature hands-on virtual labs that provide an in-depth, online training experience.
Database Administrator
2936: Installing and Securing Microsoft SQL Server 2005
2937: Administering and Monitoring Microsoft SQL Server 2005
2938: Data Availability Features in Microsoft SQL Server 2005
Database Developer
2939: Programming Microsoft SQL Server 2005
2940: Building Services and Notifications Using Microsoft SQL Server 2005
2941: Creating the Data Access Tier Using Microsoft SQL Server 2005
Business Intelligence Developer
2942: New Features of Microsoft SQL Server 2005 Analysis Services
2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005 Integration Services
2944: Updating Your Reporting Skills to Microsoft SQL Server 2005 Reporting Services
* Microsoft E-Learning for SQL Server 2005 is free until November 1, 2006. Note that this is a limited time offer and Internet connection time charges may apply.
Database Administrator
2936: Installing and Securing Microsoft SQL Server 2005
2937: Administering and Monitoring Microsoft SQL Server 2005
2938: Data Availability Features in Microsoft SQL Server 2005
Database Developer
2939: Programming Microsoft SQL Server 2005
2940: Building Services and Notifications Using Microsoft SQL Server 2005
2941: Creating the Data Access Tier Using Microsoft SQL Server 2005
Business Intelligence Developer
2942: New Features of Microsoft SQL Server 2005 Analysis Services
2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005 Integration Services
2944: Updating Your Reporting Skills to Microsoft SQL Server 2005 Reporting Services
* Microsoft E-Learning for SQL Server 2005 is free until November 1, 2006. Note that this is a limited time offer and Internet connection time charges may apply.
Monday, December 12, 2005
Fun With SQL Server Update Triggers
Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (field) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @@ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows
--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row
--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows
--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it
--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1
--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300
--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @@ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows
--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row
--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows
--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it
--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1
--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300
--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows
Thursday, December 08, 2005
Keep Your Statistics Up To Date
I had a scheduled job that runs every hour and took about a minute to complete. Somehow for no good reason this job suddenly takes hours and hours to finish. There was nothing done to the server, no service packs or patches. I checked fragmentation with DBCC SHOWCONTIG and it looked fine. I checked for blocking by running sp_who2 and there was no blocking going on. Now I was puzzled, what could have caused this? The table is not huge, about 50000 rows. Then I decided to update the statistics by running UPDATE STATISTICS and the problem went away immediately. The strange part is that there was such a huge difference in excution time, if it was double or triple I would understand but this was hunderds times slower. I don't have auto update statistics enabled because I have huge tables and can not have SQL server running sp_updatestats in the middle of the day. I have added this one table to the statistics job that I currently have and will monitor if the stats are up to date
SQL Server 2005 System View Poster
If you have never bought an issue of SQL Server Magazine then this is the month to do it. The latest issue comes with a huge SQL Server 2005 System View Poster. This poster itself is worth the price, I will get myself another issue so that I can have a poster at work and one at home. The poster contains all the system views and is organized in the following sections.Objects, Types and Indexes
Trace and Eventing
Linked Servers
Common Language Runtime
Partitioning
Databases and Storage
Execution Environment
Service Broker
Security
Transaction Information
Serveriwide Configurations
Server-wide Information
SQL Server 2005 December BOL Update And Samples
Updated versions of Books On Line and Sample Databases are available from the links below
SQL Server 2005 Books Online (December 2005)
SQL Server 2005 Samples and Sample Databases (December 2005).
SQL Server 2005 Books Online (December 2005)
SQL Server 2005 Samples and Sample Databases (December 2005).
Monday, December 05, 2005
SQL Server Site Geo Map For November
These are the countries where the bulk of the visitors for this blog are coming from. As you can see most off the visitors are from the US and Europe, Asia is in third place. When I look at the maps day by day I also see some visitors from Africa and the Middle East but you need more than one visitor per city in order for it to show up in the monthly map. I only started Google Analytics at the end of November so it will be interesting to see if December will be much different
SQL Server 2005 Master Database System Views
Below is a list of the SQL Server 2000 system tables and the corresponding SQL Server 2005 system views in the master database
SQL Server 2000 System Table SQL Server 2005 System View
sysaltfiles....................sys.master_files
syscacheobjects................sys.dm_exec_cached_plans
syscharsets....................sys.charsets
sysconfigures..................sys.configurations
syscurconfigs..................sys.configurations
sysdatabases...................sys.databases
sysdevices.....................sys.backup_devices
syslanguages...................sys.languages
syslockinfo....................sys.dm_tran_locks
syslocks.......................sys.dm_tran_locks
syslogins......................sys.server_principals
sysmessages....................sys.messages
sysoledbusers..................sys.linked_logins
sysopentapes...................sys.dm_io_backup_tapes
sysperfinfo....................sys.dm_os_performance_counters
sysprocesses...................sys.dm_exec_connections
...............................sys.dm_exec_sessions
...............................sys.dm_exec_requests
sysremotelogins................sys.remote_logins
sysservers.....................sys.servers
Saturday, December 03, 2005
November SQL Google Searches
These are the top SQL Searches on this site for the month of November. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
SQL SERVER BEGIN MONTH
xp_cmdshell
sql server" AND aggregate AND uniqueidentifier
northwind db
free sql code help
backup log files
Date formatting in SQL Server
union in sql
sql commands
show all tables sql statment
JDBC WITH SQL SERVER
determining server requirements
update row number sql
SQL functions in MS SQL Server
invoke xp_cmdshell from java
Login failed for user 'sa' Reason: not associated with a trusted SQL server connection
storage space" " text datatype"
what is an SQL server?
SQL server characteristic
application OLAP SQL 2000 with ASP
To see the top searches for October click here
SQL SERVER BEGIN MONTH
xp_cmdshell
sql server" AND aggregate AND uniqueidentifier
northwind db
free sql code help
backup log files
Date formatting in SQL Server
union in sql
sql commands
show all tables sql statment
JDBC WITH SQL SERVER
determining server requirements
update row number sql
SQL functions in MS SQL Server
invoke xp_cmdshell from java
Login failed for user 'sa' Reason: not associated with a trusted SQL server connection
storage space" " text datatype"
what is an SQL server?
SQL server characteristic
application OLAP SQL 2000 with ASP
To see the top searches for October click here
Friday, December 02, 2005
SQL Server 2005 Sample Book Chapters
Karen's SQL Blog has a blog post with a list of SQL Server 2005 sample chapters. Since I did not want to duplicate or copy the work that she has put into it I have decided to provide a link to that specific blog post here. Take a look around on her blog there are always links to interesting articles/downloads on her blog and it's updated frequently.
Thursday, December 01, 2005
Mapping SQL Server 2000 System Views To SQL Server 2005
SQL Server 2000 System Table SQL Server 2005 System View
syscolumns.....................sys.columns
syscomments....................sys.sql_modules
sysconstraints.................sys.check_constraints
...............................sys.default_constraints
...............................sys.key_constraints
...............................sys.foreign_keys
sysdepends.....................sys.sql_dependencies
sysfilegroups..................sys.filegroups
sysfiles.......................sys.files
sysforeignkeys.................sys.foreignkeys
sysfulltextcatalogs............sys.fulltextcatalogs
sysindexes.....................sys.indexes
sysindexkeys...................sys.index_columns
sysmembers.....................sys.databases_role_members
sysobjects.....................sys.objects
syspermissions.................sys.database_permissions
...............................sys.server_permissions
sysprotects....................sys.database_permissions
...............................sys.server_permissions
sysreferences..................sys.foreign_keys
systypes.......................sys.types
sysusers.......................sys.database_principals
Tuesday, November 29, 2005
SQL Server 2000 Undocumented Procedures For Files, Drives and Directories
Below are some undocumented SQL Server 2000 procedures for information about files, drives and directories.
You should not depend on this because they might me deprecated in the future, but if you want to find out fast if a file exists, what the size is and other things I think that they are good to use since it saves you some scripting or using terminal services
returns the network name of the server
master..xp_getnetname
Returns 1 if a file exists, 1 if the file is a directory and 1 if a parent directory exists
master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'
Lists all the subdirecories of a given path including the level of the path
master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL\'
Lists a directory's immediate sub directory (1 level down)
master..xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL\'
List the file detals for a file, the following fields are returned
Alternate Name,Size,Creation Date, Creation, Time Last Written, Date Last Written, Time Last Accessed,
Date Last Accessed Time and Attributes
master..xp_getfiledetails 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'
List all the fixed drives and free MB on the machine
master..xp_fixeddrives
Returns the database name, size and space used for the temp db
master..Sp_tempdbspace
Lists all the ODBC datasources (name and description) available on the machine
master..xp_enumdsn
List the current error logs (Archive #, Date and Log File Size) on the server
master..xp_enumerrorlogs
You should not depend on this because they might me deprecated in the future, but if you want to find out fast if a file exists, what the size is and other things I think that they are good to use since it saves you some scripting or using terminal services
returns the network name of the server
master..xp_getnetname
Returns 1 if a file exists, 1 if the file is a directory and 1 if a parent directory exists
master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'
Lists all the subdirecories of a given path including the level of the path
master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL\'
Lists a directory's immediate sub directory (1 level down)
master..xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL\'
List the file detals for a file, the following fields are returned
Alternate Name,Size,Creation Date, Creation, Time Last Written, Date Last Written, Time Last Accessed,
Date Last Accessed Time and Attributes
master..xp_getfiledetails 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'
List all the fixed drives and free MB on the machine
master..xp_fixeddrives
Returns the database name, size and space used for the temp db
master..Sp_tempdbspace
Lists all the ODBC datasources (name and description) available on the machine
master..xp_enumdsn
List the current error logs (Archive #, Date and Log File Size) on the server
master..xp_enumerrorlogs
Wednesday, November 23, 2005
Calculating Thanksgiving By Using SQL in SQL Server
Happy Thanksgiving, here is some SQL code to calculate the next 11 (including today) Thanksgiving days
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'
DECLARE @dtmEnddate DATETIME
SELECT @dtmEnddate ='2015-11-30 00:00:00.000'
WHILE @intLoopCounter <(SELECT DATEDIFF(d,@dtmStardate,@dtmEnddate))
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)
--+ 7 will only work if you start from a Thursday, use +1 for other days
SELECT @intLoopCounter = @intLoopCounter +7
END
--One way
SET DATEFIRST 7
SELECT CASE WHEN COUNT(*) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
--And another
SELECT dt AS ThanksGivingDay, YEAR(dt) AS [Year]
FROM
( SELECT DATEADD(d,number,@dtmStardate) AS dt
FROM dbo.#NumberPivot
) X
WHERE MONTH(dt)=11
AND DAY(dt) BETWEEN 22 AND 28
ORDER BY YEAR(dt)
--Enjoy the turkey
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'
DECLARE @dtmEnddate DATETIME
SELECT @dtmEnddate ='2015-11-30 00:00:00.000'
WHILE @intLoopCounter <(SELECT DATEDIFF(d,@dtmStardate,@dtmEnddate))
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)
--+ 7 will only work if you start from a Thursday, use +1 for other days
SELECT @intLoopCounter = @intLoopCounter +7
END
--One way
SET DATEFIRST 7
SELECT CASE WHEN COUNT(*) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
--And another
SELECT dt AS ThanksGivingDay, YEAR(dt) AS [Year]
FROM
( SELECT DATEADD(d,number,@dtmStardate) AS dt
FROM dbo.#NumberPivot
) X
WHERE MONTH(dt)=11
AND DAY(dt) BETWEEN 22 AND 28
ORDER BY YEAR(dt)
--Enjoy the turkey
Monday, November 21, 2005
SQL Server 2005 Virtual Labs
There are virtual labs available for SQL Server 2005
You get a downloadable manual and a 90-minute block of time for each module. You can sign up for additional 90-minute blocks at any time.
Go here to access the following labs
SQL Server 2005 Integration Services
SQL Server 2005 Reporting Services
SQL Server 2005 Introduction to SQL Server Management Studio
SQL Server 2005 XML Capabilities
SQL Server 2005 SQL Server and ADO.NET (Lab A)
SQL Server 2005 SQL Server and ADO.NET (Lab B)
SQL Server 2005 T-SQL Enhancements
SQL Server 2005 Server Management Objects
SQL Server 2005 Web Services
SQL Server 2005 SQL CLR Integration
SQL Server 2005 SQL Query Tuning
You get a downloadable manual and a 90-minute block of time for each module. You can sign up for additional 90-minute blocks at any time.
Go here to access the following labs
SQL Server 2005 Integration Services
SQL Server 2005 Reporting Services
SQL Server 2005 Introduction to SQL Server Management Studio
SQL Server 2005 XML Capabilities
SQL Server 2005 SQL Server and ADO.NET (Lab A)
SQL Server 2005 SQL Server and ADO.NET (Lab B)
SQL Server 2005 T-SQL Enhancements
SQL Server 2005 Server Management Objects
SQL Server 2005 Web Services
SQL Server 2005 SQL CLR Integration
SQL Server 2005 SQL Query Tuning
Use OBJECTPROPERTY To Find Out Table Properties
How many times did you open up a table in design view to find out if a table has
a delete trigger
a clustered index
an identity column
a primary key
a timestamp
a unique constraint
etc etc?
There is an easier way to find out all this info by using the OBJECTPROPERTY function
You will have to get the object id from the table to pass it into the OBJECTPROPERTY function.
To get the object id you can use the OBJECT_ID function.
Run the code below in the Northwind database to see how it works
USE Northwind
DECLARE @objectID INT
SELECT @objectID =OBJECT_ID('customers')
select OBJECTPROPERTY(@objectID,'TableDeleteTrigger') AS 'Table Has A Delete Trigger',
OBJECTPROPERTY(@objectID,'TableHasClustIndex') AS 'Table Has A Clustered Index',
OBJECTPROPERTY(@objectID,'TableHasIdentity') AS 'Table Has An Identity Column',
OBJECTPROPERTY(@objectID,'TableHasPrimaryKey') AS 'Table Has A Primary Key',
OBJECTPROPERTY(@objectID,'TableHasTimestamp') AS 'Table Has A Timestamp',
OBJECTPROPERTY(@objectID,'TableHasUniqueCnst') AS 'Table Has A Unique Constraint',
OBJECTPROPERTY(@objectID,'IsAnsiNullsOn') AS 'ANSI NULLS Is Set To ON'
Lookup OBJECTPROPERTY in Books On Line to find out about additional properties
a delete trigger
a clustered index
an identity column
a primary key
a timestamp
a unique constraint
etc etc?
There is an easier way to find out all this info by using the OBJECTPROPERTY function
You will have to get the object id from the table to pass it into the OBJECTPROPERTY function.
To get the object id you can use the OBJECT_ID function.
Run the code below in the Northwind database to see how it works
USE Northwind
DECLARE @objectID INT
SELECT @objectID =OBJECT_ID('customers')
select OBJECTPROPERTY(@objectID,'TableDeleteTrigger') AS 'Table Has A Delete Trigger',
OBJECTPROPERTY(@objectID,'TableHasClustIndex') AS 'Table Has A Clustered Index',
OBJECTPROPERTY(@objectID,'TableHasIdentity') AS 'Table Has An Identity Column',
OBJECTPROPERTY(@objectID,'TableHasPrimaryKey') AS 'Table Has A Primary Key',
OBJECTPROPERTY(@objectID,'TableHasTimestamp') AS 'Table Has A Timestamp',
OBJECTPROPERTY(@objectID,'TableHasUniqueCnst') AS 'Table Has A Unique Constraint',
OBJECTPROPERTY(@objectID,'IsAnsiNullsOn') AS 'ANSI NULLS Is Set To ON'
Lookup OBJECTPROPERTY in Books On Line to find out about additional properties
Saturday, November 19, 2005
Feature Pack for Microsoft SQL Server 2005
The Feature Pack is a collection of standalone install packages that provide additional value for SQL Server 2005. It includes:
Latest versions of redistributable components for SQL Server 2005
Latest versions of add-on providers for SQL Server 2005
Latest versions of backward compatibility components for SQL Server 2005
Go here (http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en) to get to the page
The following things can be downloaded from that page
Microsoft ADOMD.NET
ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.
Microsoft Core XML Services (MSXML) 6.0
Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.
Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with SQL Server Integration Services, SQL Server Analysis Services, Replication, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on both x86 and x64 computers. Read the installation guide and Readme for more information.
Microsoft SQL Server 2000 PivotTable Services
PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Note: Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.
Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with SQL Server 2005 and include all fixes shipped through SQL Server 2000 SP4.
Microsoft SQL Server 2005 Command Line Query Utility
The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 instances. SQLCMD is a replacement for ISQL and OSQL, but can coexist with installations that have ISQL or OSQL installed.
Note: Microsoft SQL Server 2005 Command Line Query Utility requires Microsoft SQL Server Native Client, also available on this page.
Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.
Note: Microsoft SQL Server 2005 Datamining Viewer Controls requires Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider, also available on this page.
Microsoft SQL Server 2005 JDBC Driver
In its continued commitment to interoperability, Microsoft will release and support a new Java Database Connectivity (JDBC) driver for SQL Server 2005. The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides de-facto database connectivity through the standard JDBC application programming interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
Microsoft SQL Server 2005 Management Objects Collection
The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.
Note: Microsoft SQL Server 2005 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server Native Client, also available on this page.
Microsoft SQL Server 2005 Mobile Edition
Use SQL Server 2005 Mobile Edition (SQL Server Mobile) to rapidly develop applications that extend enterprise data management capabilities to mobile devices. For the latest SQL Server Mobile add-ins, updates, tools, and components, visit the SQL Server Mobile Download site.
Microsoft SQL Server 2005 Notification Services Client Components
The SQL Server 2005 Notification Services Client Components package provides client APIs that enable subscription management and event submission within custom applications that include SQL Server 2005 Notification Services functionality. The subscription management APIs allow developers to create subscriptions and subscribers, and manage subscriber devices. The event submission APIs allow users to specify events using the event APIs or stored procedures.
Microsoft SQL Server 2005 Upgrade Advisor
Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
Microsoft .NET Data Provider for mySAP Business Suite, Preview Version
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create an Integration Services package that can connect to a mySAP Business Suite solution and then execute commands to access data via supported interfaces. You can also create Reporting Services reports against a server running the mySAP Business Suite.You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard and in various Integration Services features (including the Script task, the DataReader Source component, and the Script component), as well as the data processing extensions in Reporting Services.The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005. The preview version is licensed as pre-release software as outlined in the License Terms. See the Readme included with the download for information on the prerequisites for using the Microsoft .NET Data Provider for mySAP Business Suite.
Reporting Add-In for Microsoft Visual Web Developer 2005 Express
The Reporting Add-In for Microsoft Visual Web Developer 2005 Express includes a ReportViewer control, an integrated report designer, and a comprehensive API that lets you customize run-time functionality. You can use the control to add interactive real-time data reports to your ASP.NET Web applications. Reports can use data from any ADO.NET DataTable or custom Business object. You can create reports that combine tabular, matrix, and visual data in free-form or traditional report layouts. An integrated report designer is included so that you can create the custom reports that bind to the control.The Reporting Add-In is the same reporting component that is included with other editions of Visual Studio, but without support for Windows Forms applications. For more information including product documentation and samples, see the ReportViewer Controls (Visual Studio) topic on MSDN.
Latest versions of redistributable components for SQL Server 2005
Latest versions of add-on providers for SQL Server 2005
Latest versions of backward compatibility components for SQL Server 2005
Go here (http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en) to get to the page
The following things can be downloaded from that page
Microsoft ADOMD.NET
ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.
Microsoft Core XML Services (MSXML) 6.0
Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.
Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with SQL Server Integration Services, SQL Server Analysis Services, Replication, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on both x86 and x64 computers. Read the installation guide and Readme for more information.
Microsoft SQL Server 2000 PivotTable Services
PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Note: Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.
Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with SQL Server 2005 and include all fixes shipped through SQL Server 2000 SP4.
Microsoft SQL Server 2005 Command Line Query Utility
The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 instances. SQLCMD is a replacement for ISQL and OSQL, but can coexist with installations that have ISQL or OSQL installed.
Note: Microsoft SQL Server 2005 Command Line Query Utility requires Microsoft SQL Server Native Client, also available on this page.
Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.
Note: Microsoft SQL Server 2005 Datamining Viewer Controls requires Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider, also available on this page.
Microsoft SQL Server 2005 JDBC Driver
In its continued commitment to interoperability, Microsoft will release and support a new Java Database Connectivity (JDBC) driver for SQL Server 2005. The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides de-facto database connectivity through the standard JDBC application programming interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
Microsoft SQL Server 2005 Management Objects Collection
The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.
Note: Microsoft SQL Server 2005 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server Native Client, also available on this page.
Microsoft SQL Server 2005 Mobile Edition
Use SQL Server 2005 Mobile Edition (SQL Server Mobile) to rapidly develop applications that extend enterprise data management capabilities to mobile devices. For the latest SQL Server Mobile add-ins, updates, tools, and components, visit the SQL Server Mobile Download site.
Microsoft SQL Server 2005 Notification Services Client Components
The SQL Server 2005 Notification Services Client Components package provides client APIs that enable subscription management and event submission within custom applications that include SQL Server 2005 Notification Services functionality. The subscription management APIs allow developers to create subscriptions and subscribers, and manage subscriber devices. The event submission APIs allow users to specify events using the event APIs or stored procedures.
Microsoft SQL Server 2005 Upgrade Advisor
Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
Microsoft .NET Data Provider for mySAP Business Suite, Preview Version
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create an Integration Services package that can connect to a mySAP Business Suite solution and then execute commands to access data via supported interfaces. You can also create Reporting Services reports against a server running the mySAP Business Suite.You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard and in various Integration Services features (including the Script task, the DataReader Source component, and the Script component), as well as the data processing extensions in Reporting Services.The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005. The preview version is licensed as pre-release software as outlined in the License Terms. See the Readme included with the download for information on the prerequisites for using the Microsoft .NET Data Provider for mySAP Business Suite.
Reporting Add-In for Microsoft Visual Web Developer 2005 Express
The Reporting Add-In for Microsoft Visual Web Developer 2005 Express includes a ReportViewer control, an integrated report designer, and a comprehensive API that lets you customize run-time functionality. You can use the control to add interactive real-time data reports to your ASP.NET Web applications. Reports can use data from any ADO.NET DataTable or custom Business object. You can create reports that combine tabular, matrix, and visual data in free-form or traditional report layouts. An integrated report designer is included so that you can create the custom reports that bind to the control.The Reporting Add-In is the same reporting component that is included with other editions of Visual Studio, but without support for Windows Forms applications. For more information including product documentation and samples, see the ReportViewer Controls (Visual Studio) topic on MSDN.
Friday, November 18, 2005
SQL Server 2005 Launch In Philadelphia
I have attended the SQL Server 2005 Launch In Philadelphia. It was very informative I learned some stuff I didn't know and saw some usefull implementations of the new features. There were about 30-40 vendors present. The one vendor that I hoped would be there was not. This company is red-gate and they are the makers of a product called SQL Compare I have used this product for about 2 years now and was hoping to speak to someone and letting them know how much I liked their product. The Architecting Scalable Flexible and Secure Database Systems track was interesting as well as all the others. What kind of goodies did you receive here? Of course everyone received SQL 2005 Standard and Visual studio 2005 Standard Editions. Also included was Windows 2003 Server R2 RC1, Biztalk server 2006 CTP, A free exam voucher, free Biztalk 2006 Developer Edition download (after registering for the beta download) and a shirt of course (ironically Philadelphia is not mentioned on the shirt).
Perhaps the best give away was the Launch 2005 Resource DVD, it is packed with all kinds of stuff. For SQL Server these are
Case Studies
Datasheets and More
Launch 2005 - Data Platform Track
Videos
Webcasts
Whitepapers
eLearning
All in all a good event and I am glad I went
Tuesday, November 15, 2005
Find all Primary and Foreign Keys In A Database
To find all your foreign and primary keys in your database run the code below.
The ouput will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output
USE Northwind
SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
The ouput will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output
USE Northwind
SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Monday, November 14, 2005
SQL Server 2005 Book Rant
I have 2 store credits one for Borders and the other one for Barnes and Noble. So I decided to buy the Pro SQL server 2005 and SQL Server 2005 Administrators Companion this Saturday. I first went to Barnes and Noble and no books. So that already damped my mood. Next I decided to go to Borders and the same thing no books either. This really made me furious, how long does it take for a store to get the books? I wanted instant gratification and I can’t have any. So I went home and ordered them on Amazon I just hope I will get them by Wednesday.
Thursday, November 10, 2005
Cannot add, update, or delete a job that originated from an MSX Server
Well this is one of those very friendly error messages that when you look at it you know immediately what is going on. Of course I am being sarcastic here. This happened to a client who was migrating their servers and backed up all the SQL server Databases and restored them on the new machine they bought. What happened is this, they named the new machine differently than the old machine. When you look at the jobs you can see them (and that’s about it). You can not delete, update or run these jobs. Unfortunately renaming the server was not an option for the client. I looked in the sysjobs table in the msdb database and found out that the name stored was the old servers name
SELECT originating_server FROM msdb..sysjobs
I then updated the originating_server fields in the table with the new server name value and everything was fine after that. As far as I know this doesn't effect SQL server 7 since it stores (local) in the originating_server column
SELECT originating_server FROM msdb..sysjobs
I then updated the originating_server fields in the table with the new server name value and everything was fine after that. As far as I know this doesn't effect SQL server 7 since it stores (local) in the originating_server column
Tuesday, November 08, 2005
Pad Numbers By Using CASE, LEFT And CONVERT
Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001
How do you do this in SQL server?
Below are two ways to accomplish this.
CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable
--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable
I have received a comment from Rob Farley who was so kind to point out to me that I should have used RIGHT instead of left
Here is the code he supplied
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS FormattedValue
FROM #ValueTable
How do you do this in SQL server?
Below are two ways to accomplish this.
CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000
--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable
--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable
I have received a comment from Rob Farley who was so kind to point out to me that I should have used RIGHT instead of left
Here is the code he supplied
--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS FormattedValue
FROM #ValueTable
Monday, November 07, 2005
SQL Server 2005 launch Webcast
Today at 9AM Pacific Standard Time Steve Ballmer launches Microsoft SQL Server 2005, the next generation data management and analysis software. You can follow the launch, including a Webcast of the launch from this Web site:
http://www.microsoft.com/windowsserversystem/applicationplatform/launch2005/default.mspx
SQL Server product site: http://microsoft.com/sql
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
Data Access and Storage Developer Center: http://msdn.microsoft.com/data
http://www.microsoft.com/windowsserversystem/applicationplatform/launch2005/default.mspx
SQL Server product site: http://microsoft.com/sql
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
Data Access and Storage Developer Center: http://msdn.microsoft.com/data
Thursday, November 03, 2005
Formatting Data By Using CHARINDEX And SUBSTRING
Let's say you have names stored in the format [Klein, Barbara] but would like it to be [Barbara Klein]
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work
CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))
INSERT INTO Names
SELECT 'Klein, Barbara',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL
UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)
SELECT * FROM Names
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work
CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))
INSERT INTO Names
SELECT 'Klein, Barbara',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL
UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)
SELECT * FROM Names
Wednesday, November 02, 2005
Finding ASCII, Numbers and Alphabet Characters In SQL Server Tables
SQL Server has limited support for regular expressions,
You can run the code below to see what some of the results are by searching for data while using regular expressions
CREATE TABLE AsciiTest (id INT identity, TextField VARCHAR(50))
INSERT INTO AsciiTest
SELECT '%&&&123%#$#$'
UNION ALL
SELECT 'safdsfsdrfsdfse'
UNION ALL
SELECT '12121212'
UNION ALL
SELECT '1212asasas'
UNION ALL
SELECT '%&&&%#$#$'
UNION ALL
SELECT '4564gg565656'
UNION ALL
SELECT '12'
--No Alphabet Characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[a-Z]%'
--Starting with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '[0-9]%'
--Ending with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-9]'
--Numbers and characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-Z]%'
--Only non alphabet/numbers characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[0-Z]%'
--some non alphabet/number characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[^0-Z]%'
-- ASCII Code 38 (&)
SELECT * FROM AsciiTest
WHERE TextField LIKE '%' + char(38) + '%'
You can run the code below to see what some of the results are by searching for data while using regular expressions
CREATE TABLE AsciiTest (id INT identity, TextField VARCHAR(50))
INSERT INTO AsciiTest
SELECT '%&&&123%#$#$'
UNION ALL
SELECT 'safdsfsdrfsdfse'
UNION ALL
SELECT '12121212'
UNION ALL
SELECT '1212asasas'
UNION ALL
SELECT '%&&&%#$#$'
UNION ALL
SELECT '4564gg565656'
UNION ALL
SELECT '12'
--No Alphabet Characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[a-Z]%'
--Starting with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '[0-9]%'
--Ending with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-9]'
--Numbers and characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-Z]%'
--Only non alphabet/numbers characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[0-Z]%'
--some non alphabet/number characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[^0-Z]%'
-- ASCII Code 38 (&)
SELECT * FROM AsciiTest
WHERE TextField LIKE '%' + char(38) + '%'
Tuesday, November 01, 2005
Top Google Searches On SQL Server Code
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
Here are the results...
non-ANSI outer join
HOW TO COMMENT CODE IN sql SERVER
veritas 10 sql module
backup log files
CHAINTECH 6BTM0
Free SQL Code help
query analiser
An interesting list, lets say what next month will bring
I have left out searches that have nothing to do with SQL Server or programming
Here are the results...
non-ANSI outer join
HOW TO COMMENT CODE IN sql SERVER
veritas 10 sql module
backup log files
CHAINTECH 6BTM0
Free SQL Code help
query analiser
An interesting list, lets say what next month will bring
Monday, October 31, 2005
SQL Server/Math Puzzle
Use the numbers 3,4,5,6 and the operators +, -, / and * to get the number 28
You can use each number only one time and also each operator one time
You don't have to use all operators, as far as I know the only way is to use all the numbers
Create a SQL SELECT statement You will need parentheses to make it work, good luck
The solution is below highlight to see, but don't give up so fast
solution below (highlight to see)
select 4*(5+6/3)
solution above (highlight to see)
You can use each number only one time and also each operator one time
You don't have to use all operators, as far as I know the only way is to use all the numbers
Create a SQL SELECT statement You will need parentheses to make it work, good luck
The solution is below highlight to see, but don't give up so fast
solution below (highlight to see)
select 4*(5+6/3)
solution above (highlight to see)
Percentage Of NULLS And Values In A SQL Server Table
Sometimes you want to know what the percentage is of null values in a table for a field
Or you might want to know what the percentage of all values in a field is grouped by value
You can get these answers by running the code below
CREATE TABLE #perc ( Field1 INT,Field2 INT,Field3 INT)
INSERT INTO #perc
SELECT NULL,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT NULL,NULL,1
UNION ALL
SELECT NULL,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 1,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 2,1,2
UNION ALL
SELECT 3,1,1
--Get the percentage of nulls in all the fields in my table
SELECT 100.0 * SUM(CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field1Percent,
100.0 * SUM(CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field2Percent,
100.0 * SUM(CASE WHEN Field3 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field3Percent
FROM #perc
--Get the values and the percentage of all values in a field
SELECT Field3 AS Value,COUNT(Field3) AS ValueCount,
100.0 * COUNT(coalesce(Field3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Field3
ORDER BY Percentage DESC
DROP TABLE #perc
Or you might want to know what the percentage of all values in a field is grouped by value
You can get these answers by running the code below
CREATE TABLE #perc ( Field1 INT,Field2 INT,Field3 INT)
INSERT INTO #perc
SELECT NULL,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT NULL,NULL,1
UNION ALL
SELECT NULL,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 1,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 2,1,2
UNION ALL
SELECT 3,1,1
--Get the percentage of nulls in all the fields in my table
SELECT 100.0 * SUM(CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field1Percent,
100.0 * SUM(CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field2Percent,
100.0 * SUM(CASE WHEN Field3 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field3Percent
FROM #perc
--Get the values and the percentage of all values in a field
SELECT Field3 AS Value,COUNT(Field3) AS ValueCount,
100.0 * COUNT(coalesce(Field3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Field3
ORDER BY Percentage DESC
DROP TABLE #perc
Sunday, October 30, 2005
Microsoft SQL Server Developer Edition 2005 CD/DVD For Sale On Amazon
For all of you people who don't have a MSDN subscription, Amazon is selling the developer edition of SQL Server 2005 for $59.99 with free shipping
Microsoft SQL Server Developer Edition 2005 CD/DVD
Microsoft SQL Server Developer Edition 2005 CD/DVD
Thursday, October 27, 2005
SQL Server 2005 and Visual Studio 2005 are released!!!
SQL Server 2005 and Visual Studio 2005 are available for download at the MSDN site
For people who have a subscription go here
http://www.msdn.microsoft.com/subscriptions/
Enjoy
For people who have a subscription go here
http://www.msdn.microsoft.com/subscriptions/
Enjoy
SQL Server 2005 Certification Links
Below are the links for the SQL Server Certification exams
Microsoft Certified Technology Specialist: SQL Server 2005
Microsoft Certified IT Professional: Database Administrator
Microsoft Certified IT Professional: Database Developer
Microsoft Certified IT Professional: Business Intelligence Developer
Microsoft Certified Database Administrator
Microsoft Certified Technology Specialist: SQL Server 2005
Microsoft Certified IT Professional: Database Administrator
Microsoft Certified IT Professional: Database Developer
Microsoft Certified IT Professional: Business Intelligence Developer
Microsoft Certified Database Administrator
My Top 3 SQL Server Books
This is my top 3 SQL server books.
They are not in any order. I use all 3 of them frequently
The Guru's Guide to Transact-SQL
by Ken Henderson
This is a fantastic book, Ken has done such a great job. I have bought this book in December 2001 after reading some reviews on Amazon'
I must tell you that it is much better than I would ever have expected.
There are so many cool things that I didn't know you could do in SQL Server, the chapter about undocumented things is a gem.
Ken will show you many ways to accomplish something and of course every example is better than the one before it
If you are a SQL Server Programmer then this book belongs in your library, it doesn't matter if you are a beginner or a MVP you will definitely learn something from this book
Another bonus is that all the source code is on a CD
Inside Microsoft SQL Server 2000
by Kalen Delaney
If you need to know about locking/blocking/deadlocks/performance then this is the book for you It goes into such detail and with great examples on how to find out what causes deadlocks and how to prevent them. For example using sp_lock2
The SQL server architecture part is a must read as well the chapter about cursors and large objects
Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
This is a very good reference book about SQL Server it covers everything from programming to administration to OLAP. I keep it on my desk when I need to look up things (with the help of post-it notes)
They are not in any order. I use all 3 of them frequently
The Guru's Guide to Transact-SQL
by Ken Henderson
This is a fantastic book, Ken has done such a great job. I have bought this book in December 2001 after reading some reviews on Amazon'
I must tell you that it is much better than I would ever have expected.
There are so many cool things that I didn't know you could do in SQL Server, the chapter about undocumented things is a gem.
Ken will show you many ways to accomplish something and of course every example is better than the one before it
If you are a SQL Server Programmer then this book belongs in your library, it doesn't matter if you are a beginner or a MVP you will definitely learn something from this book
Another bonus is that all the source code is on a CD
Inside Microsoft SQL Server 2000
by Kalen Delaney
If you need to know about locking/blocking/deadlocks/performance then this is the book for you It goes into such detail and with great examples on how to find out what causes deadlocks and how to prevent them. For example using sp_lock2
The SQL server architecture part is a must read as well the chapter about cursors and large objects
Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
This is a very good reference book about SQL Server it covers everything from programming to administration to OLAP. I keep it on my desk when I need to look up things (with the help of post-it notes)
Monday, October 24, 2005
SQL Server 2005 Books On Line on MSDN
For those of you who don't want to download the books on line for SQL Server 2005 but would like to see it online here is the link (http://msdn2.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx)
Here are the links for the main categories
Database Engine(link)
The Database Engine is the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.
Analysis Services(link)
Analysis Services delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services enables you to design, create, and visualize data mining models. These mining models can be constructed from other data sources by using a wide variety of industry-standard data mining algorithms.
Integration Services(link)
Integration Services is a platform for building high performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.
Replication(link)
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.
Reporting Services(link)
Reporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.
Notification Services(link)
Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and can deliver the messages to a variety of devices.
Service Broker(link)
Service Broker helps developers build scalable, secure database applications. This new Database Engine technology provides a message-based communication platform that enables independent application components to perform as a functioning whole. Service Broker includes infrastructure for asynchronous programming that can be used for applications within a single database or a single instance, and also for distributed applications.
Full-Text Search(link)
Full-Text Search contains the functionality you can use to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.
Here are the links for the main categories
Database Engine(link)
The Database Engine is the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.
Analysis Services(link)
Analysis Services delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services enables you to design, create, and visualize data mining models. These mining models can be constructed from other data sources by using a wide variety of industry-standard data mining algorithms.
Integration Services(link)
Integration Services is a platform for building high performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.
Replication(link)
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.
Reporting Services(link)
Reporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.
Notification Services(link)
Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and can deliver the messages to a variety of devices.
Service Broker(link)
Service Broker helps developers build scalable, secure database applications. This new Database Engine technology provides a message-based communication platform that enables independent application components to perform as a functioning whole. Service Broker includes infrastructure for asynchronous programming that can be used for applications within a single database or a single instance, and also for distributed applications.
Full-Text Search(link)
Full-Text Search contains the functionality you can use to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.
Friday, October 21, 2005
Some Undocumented DBCC Commands
DBCC MEMORYSTATUS
This DBCC command provides detailed info about SQL Server memory usage
DBCC TRACEON(3604)
DBCC Resource
DBCC TRACEOFF(3604)
This DBCC command list SQL Server resource utilization
DBCC DBREINDEXALL('pubs')
This DBCC command will rebuild all the indexes for a user Database, the DB has to exclusively locked or you will get the error message “The database could not be exclusively locked to perform the operation.”
DBCC FLUSHPROCINDB
This will force a recompile of all the stored procedures in a database
DECLARE @ID INT
SET @id =DB_ID('pubs')
DBCC FLUSHPROCINDB(@ID)
This DBCC command provides detailed info about SQL Server memory usage
DBCC TRACEON(3604)
DBCC Resource
DBCC TRACEOFF(3604)
This DBCC command list SQL Server resource utilization
DBCC DBREINDEXALL('pubs')
This DBCC command will rebuild all the indexes for a user Database, the DB has to exclusively locked or you will get the error message “The database could not be exclusively locked to perform the operation.”
DBCC FLUSHPROCINDB
This will force a recompile of all the stored procedures in a database
DECLARE @ID INT
SET @id =DB_ID('pubs')
DBCC FLUSHPROCINDB(@ID)
Thursday, October 20, 2005
Find Out How Many Occurrences Of A Substring Are In A String
If you want to know how many occurrences of a substring you have in a string play around with the code below. The trick here is to take the total length of the string, subtract the same string minus the occurrences and divide by the length of the substring
For example
String is ’ABABABABZZZZZ’, length is 13
Substring is ’AB’ length is 2
Length of string minus all occurrences of substring is 5
So (13 -5) /2 =4 occurrences
DECLARE @chvString VARCHAR(500)
SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
DECLARE @chvSearchString VARCHAR(50)
SELECT @chvSearchString = 'ab'
SELECT LEN(@chvString) AS StringLength,
LEN(@chvSearchString) AS SearchForStringLength,(LEN(@chvString)-
(LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString) AS HowManyOccurances
For example
String is ’ABABABABZZZZZ’, length is 13
Substring is ’AB’ length is 2
Length of string minus all occurrences of substring is 5
So (13 -5) /2 =4 occurrences
DECLARE @chvString VARCHAR(500)
SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
DECLARE @chvSearchString VARCHAR(50)
SELECT @chvSearchString = 'ab'
SELECT LEN(@chvString) AS StringLength,
LEN(@chvSearchString) AS SearchForStringLength,(LEN(@chvString)-
(LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString) AS HowManyOccurances
Monday, October 17, 2005
Do Not Drop And Create Indexes On Your Tables
When you do this the nonclustered indexes are dropped and recreated twice, once when you drop the clustered index and then again when you create the clustered index.
Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.
Here is an example:
CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING
Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.
Here is an example:
CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING
Saturday, October 15, 2005
Which Service Pack Is Installed On My SQL Server
To get the answer to this question run the code below
SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
SERVERPROPERTY( 'ProductLevel') AS ServicePack
There are 3 possible values for ProductLevel
'RTM' = shipping version.
'SPn' = service pack version
'Bn' = beta version.
SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
SERVERPROPERTY( 'ProductLevel') AS ServicePack
There are 3 possible values for ProductLevel
'RTM' = shipping version.
'SPn' = service pack version
'Bn' = beta version.
Wednesday, October 12, 2005
List All The Indexes In My Database
The query below will list all the indexes on all user tables in the database
SELECT OBJECT_NAME ( si.id ) AS TableName ,
CASE indid WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
END TypeOfIndex,
si.[name] AS IndexName
FROM sysindexes si
JOIN sysobjects so ON si.id =so.id
WHERE xtype ='U'
AND indid < 255
ORDER BY TableName,indid
SELECT OBJECT_NAME ( si.id ) AS TableName ,
CASE indid WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
END TypeOfIndex,
si.[name] AS IndexName
FROM sysindexes si
JOIN sysobjects so ON si.id =so.id
WHERE xtype ='U'
AND indid < 255
ORDER BY TableName,indid
Monday, October 10, 2005
Concatenate Data Into One Comma Delimited String
To concatenate data from several rows you can use the code below
I have declared the variable as varchar 1000, adjust the size if you think the size doesn't fit your needs
USE pubs
DECLARE @chvCommaDelimitedString VARCHAR(1000)
SELECT @chvCommaDelimitedString = COALESCE(@chvCommaDelimitedString + ',', '') + REPLACE(au_lname,'''','''''')
FROM dbo.authors
SELECT @chvCommaDelimitedString
I have declared the variable as varchar 1000, adjust the size if you think the size doesn't fit your needs
USE pubs
DECLARE @chvCommaDelimitedString VARCHAR(1000)
SELECT @chvCommaDelimitedString = COALESCE(@chvCommaDelimitedString + ',', '') + REPLACE(au_lname,'''','''''')
FROM dbo.authors
SELECT @chvCommaDelimitedString
Friday, October 07, 2005
Find Out Server Roles For a SQL Server Login
This is a quick way to find out what roles a user has on the SQL Server
Just change the value of @chvLoginName to the login you want
DECLARE @chvLoginName VARCHAR(50)
SELECT @chvLoginName='BUILTIN\Administrators'
SELECT [name],sysadmin,securityadmin,serveradmin,
setupadmin,processadmin,diskadmin,
dbcreator,bulkadmin,loginname
FROM master..syslogins
WHERE loginname =@chvLoginName
-------------------------------------------------------------------------------
The query below returns all logins whose role is a certain role.
The available roles are:
sysadmin
Can perform any activity in SQL Server.
serveradmin
Can set serverwide configuration options, shut down the server.
setupadmin
Can manage linked servers and startup procedures.
securityadmin
Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin
Can manage processes running in SQL Server.
dbcreator
Can create, alter, and drop databases.
diskadmin
Can manage disk files.
bulkadmin
Can execute BULK INSERT statements.
SELECT [name],sysadmin,bulkadmin
FROM master..syslogins
WHERE sysadmin =1 or bulkadmin =1
Just change the value of @chvLoginName to the login you want
DECLARE @chvLoginName VARCHAR(50)
SELECT @chvLoginName='BUILTIN\Administrators'
SELECT [name],sysadmin,securityadmin,serveradmin,
setupadmin,processadmin,diskadmin,
dbcreator,bulkadmin,loginname
FROM master..syslogins
WHERE loginname =@chvLoginName
-------------------------------------------------------------------------------
The query below returns all logins whose role is a certain role.
The available roles are:
sysadmin
Can perform any activity in SQL Server.
serveradmin
Can set serverwide configuration options, shut down the server.
setupadmin
Can manage linked servers and startup procedures.
securityadmin
Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin
Can manage processes running in SQL Server.
dbcreator
Can create, alter, and drop databases.
diskadmin
Can manage disk files.
bulkadmin
Can execute BULK INSERT statements.
SELECT [name],sysadmin,bulkadmin
FROM master..syslogins
WHERE sysadmin =1 or bulkadmin =1
Thursday, October 06, 2005
How to find all the tables and views in a database
Use the code below
USE PUBS
GO
--Get All Tables
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO
--Get All Views
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
GO
That's all, plain and simple
USE PUBS
GO
--Get All Tables
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO
--Get All Views
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
GO
That's all, plain and simple
Wednesday, September 28, 2005
Split a comma delimited string fast!
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO
--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO
--order the string
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO
--Get distinct values
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2'
SELECT DISTINCT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO
--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO
--order the string
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='Abc,A,V,G,KJHLJJKHGGF,J,L,O,O,I,U,Y,G,V,R'
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO
--Get distinct values
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2'
SELECT DISTINCT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
ORDER BY 1
GO
Tuesday, September 27, 2005
Fast Date Ranges Without Loops In SQL Server 2000
The trick to create date ranges without loops is to use a pivot table.
How does this work? Run the code below and you will see, only create the pivot table once and run all the other code seperately
-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
--Last 10 years from today
SELECT DATEADD(yy,-numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10
--Next 10 years from today
SELECT DATEADD(yy,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10
--Next 100 months from today
SELECT DATEADD(mm,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100
-- next 100 weeks from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'
SELECT DATEADD(wk,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100
-- next 100 quarters from today
SELECT DATEADD(qq,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100
That's it, come back tomorrow and I will show you how to use the Pivot table to split strings
How does this work? Run the code below and you will see, only create the pivot table once and run all the other code seperately
-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO
--Last 10 years from today
SELECT DATEADD(yy,-numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10
--Next 10 years from today
SELECT DATEADD(yy,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10
--Next 100 months from today
SELECT DATEADD(mm,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100
-- next 100 weeks from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'
SELECT DATEADD(wk,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100
-- next 100 quarters from today
SELECT DATEADD(qq,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100
That's it, come back tomorrow and I will show you how to use the Pivot table to split strings
Sunday, September 25, 2005
Put Tables Into Memory
If you have lookup tables (or other small tables) that are frequently accessed and you don’t want to reduce I/O use the command DBCC PINTABLE. What this does is it keeps the table in the data cache all the time so that you reduce I/O which in turn will boost SQL Server performance
Once you pin a table it is not in memory until it’s first requested and then only the data pages requested are in memory not the whole table
How to pin a table?
To pin a table use the script below
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC PINTABLE (@intDBID, @intTableID)
Be careful not to pin large tables since they will stay in memory and SQL Server will have less memory available for other task
To unpin a table use the same script but replace PINTABLE with UNPINTABLE (see below)
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC UNPINTABLE (@intDBID, @intTableID)
Test it out of you staging/development environment first before doing this on a production box
Once you pin a table it is not in memory until it’s first requested and then only the data pages requested are in memory not the whole table
How to pin a table?
To pin a table use the script below
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC PINTABLE (@intDBID, @intTableID)
Be careful not to pin large tables since they will stay in memory and SQL Server will have less memory available for other task
To unpin a table use the same script but replace PINTABLE with UNPINTABLE (see below)
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC UNPINTABLE (@intDBID, @intTableID)
Test it out of you staging/development environment first before doing this on a production box
Friday, September 23, 2005
SQL Server MVP's
Here is a link to a list of all the MS SQL Server MVP's
All of them have website links next to their name
Now you can see who all those people answering your questions in the newsgroups are
http://www.mvps.org/links.html#SqlServer
All of them have website links next to their name
Now you can see who all those people answering your questions in the newsgroups are
http://www.mvps.org/links.html#SqlServer
Thursday, September 22, 2005
Query Analyzer Trick
In Query Analyzer you can save a lot of time by using this trick instead of typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
Wednesday, September 21, 2005
Date formatting in SQL Server
According to Joe Celko this should always happen on the client side, but in case you ever need it (for example in DTS when you have to output to a file) here is the SQL code.
Declare @d datetime
select @d = getdate()
select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
Declare @d datetime
select @d = getdate()
select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'