SQL Server Code,Tips and Tricks, Performance Tuning

SQL Server 2005 Service Pack 3 Beta and SQL Server 2008 Feature Pack October 2008 are available for download

Download SQL Server 2005 Service Pack 3 - Beta here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

To obtain SQL Server 2005 SP3 Beta for Express Edition or Express Edition with Advanced Services go here: http://www.microsoft.com/downloads/details.aspx?FamilyId=80DFF99C-CC82-4639-92BB-BBDAFF677792&displaylang=en

Microsoft SQL Server 2008 Feature Pack October 2008 is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

• Redistributable components for SQL Server 2008.
• Add-on providers for SQL Server 2008.
• Backward compatibility components for SQL Server 2008.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

I interviewed Louis Davidson in 2005 about the 2005 edition of his book. I did another one today about Pro SQL Server 2008 Relational Database Design and Implementation. You can check out the interview at the link below, you will enjoy it


Interview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';



No problem right?

Run just this part on SQL 2008


DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;

SELECT @num_Passed


No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';




Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.


Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = 1;
SELECT @num_Passed



IF (@num_Passed = 0)
PRINT 'True';




No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)
PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)
PRINT 'True';



No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

Get it here: http://www.microsoft.com/downloads/thankyou.aspx?familyId=fbee1648-7106-44a7-9649-6d9f6d58056e&displayLang=en

Here is the link to the 831MB ISO
http://www.microsoft.com/downloads/details.aspx?FamilyId=27673C47-B3B5-4C67-BD99-84E525B5CE61&displaylang=en


Finally, Now I can also install SQL Server 2008 :-)

Tibor Karaszi has created a very useful index information stored procedure for SQL Server 2005 and up.
This stored procedure will tell you the following"

What indexes exists for a or each table(s)
Clustered, non-clustered or heap
Columns in the index
Included columns in the index
Unique or nonunique
Number rows in the table
Space usage
How frequently the indexes has been used

Check it out here: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

SQL Server 2008 Release Candidate 0 has been made available to the general public.

There are 2 versions (and 3 flavors of each) ISO or DVD

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=35F53843-03F7-4ED5-8142-24A4C024CA05&displaylang=en

Don't forget to also visit the SQL Server 2008 Release Candidate 0 connect site here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

I have interviewed Erland Sommarskog, you can find that interview here: Interview With Erland Sommarskog About SQL Server and Transact SQL

Enjoy

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage

First the advantages

Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)


SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'


Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple

Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have launched a Wiki with Solutions for Common T-SQL Problems. Check it out here: http://code.msdn.microsoft.com/SQLExamples

This is how you return all the tables with their last generated identity values in a SQL Server Database

SELECT IDENT_CURRENT(table_name),*
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'TableHasIdentity')=1

I went to the 6th Annual Financial Services Developer Conference today in New York City. This year’s focus was on High Performance Computing (HPC). I must say that I have enjoyed this conference a lot. It seems that SQL Server is very prevalent on the street. The OLAP capabilities of SQL Server are making Sybase slowly disappear from Wall Street. Financial firms are doing some very interesting stuff; there is a hedge fund which records an earnings call, runs it through some voice stress analysis software and based on the outcome of that will short or long the stock. You should hear the stories of how financial firms handle IT, the innovation is happening in the financial markets. I also saw a couple of very cool WPF applications. Check out the demos from Lab49: http://www.lab49.com/insidethelab/demos
Visit http://www.financialdevelopers.com/ to download The Silverlight 2 Retail Financial Services Demonstrator

Financial Services Developer Conference

Carl Franklin from Dotnetrocks recorded a podcast at the event, this podcast was about concurrency and how to handle multi-core programming. You should definitely check it out when it becomes available on dotnetrocks. If you are interested about PLINQ and concurrent programming with .NET then visit this site: http://msdn2.microsoft.com/en-us/concurrency/default.aspx

Dotnetrocks Podcast Recording

Tomorrow there are three sessions at the same time that I want to attend
Software Engineering with VSTS
Parallelize you .Net applications with parallel extensions to the .NET framework.
Useful evolution: Programming the New features in SQL Server 2008.

I have my (削除) black (削除ここまで)crackberry with me so if you are attending the event tomorrow then send me an email at FirstName.LastName@dowjones.com and maybe we can discuss about this event during lunch.

SQL Server 2008 CTP 6 has enhanced the convert function when you do conversion between binary and character hexadecimal values
There are 3 styles:
Style 0 works the same as on 2005 it converts binary to varchar, if you have 0x64656E6973 then you will get 'denis'
Style 1 converts binary to varchar but the values stay the same, if you have 0x64656E6973 you will get '0x64656E6973'
Style 2 strips the 0x but leaves the rest of the values, if you have 0x64656E6973 you will get '64656E6973'

Try it out

When you run this

SELECTCONVERT(varbinary(5),'denis')

The output will be this 0x64656E6973. Now we can use that in the select statements below

SELECTCONVERT(varchar(18), 0x64656E6973, 0)AS'Style 0'--denis
SELECTCONVERT(varchar(18), 0x64656E6973, 1)AS'Style 1'--0x64656E6973
SELECTCONVERT(varchar(18), 0x64656E6973, 2)AS'Style 2'--64656E6973

When you run the code above on SQL Server 2005 all 3 select statements return 'denis'

It is all in Books On Line for CTP 6, it would have been nice if they also included the sys.dm_tran_commit_table dmv or the sys.dm_exec_trigger_stats dmv. I have no clue what the sys.dm_tran_commit_table dmv is supposed to do :-(

Remember the 33 new DMVs in SQL Server 2008 post? I was just poking around in SQL Server 2008 CTP6 and wanted to know if there were any new Dynamic Management Views since CTP5. There are 13 new DMVs in CTP6 and there are a total of 45 new Dynamic Management Views compared to SQL server 2005. The list of them is below, I think their names make clear what the dmv is supposed to do (or at least I think I know)

dm_audit_actions
dm_audit_class_type_map
dm_cdc_errors
dm_cdc_log_scan_sessions
dm_cryptographic_provider_algorithms
dm_cryptographic_provider_keys
dm_cryptographic_provider_properties
dm_cryptographic_provider_sessions
dm_database_encryption_keys
dm_db_mirroring_auto_page_repair
dm_db_mirroring_past_actions
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_filestream_oob_handles
dm_filestream_oob_requests
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatcher_pools
dm_os_dispatchers
dm_os_memory_brokers
dm_os_memory_node_access_stats
dm_os_memory_nodes
dm_os_nodes
dm_os_process_memory
dm_os_spinlock_stats
dm_os_sys_memory
dm_resource_governor_configuration
dm_resource_governor_resource_pools
dm_resource_governor_workload_groups
dm_server_audit_status
dm_sql_referenced_entities
dm_sql_referencing_entities
dm_tran_commit_table
dm_xe_map_values
dm_xe_object_columns
dm_xe_objects
dm_xe_packages
dm_xe_session_event_actions
dm_xe_session_events
dm_xe_session_object_columns
dm_xe_session_targets
dm_xe_sessions



The 13 DMVs listed below are new in CTP6

dm_audit_actions
dm_audit_class_type_map
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatchers
dm_os_memory_node_access_stats
dm_server_audit_status

If you want to find out what the names of all the new DMVs are by running a query on your SQL Server 2008 box and you don't have a linked server to a SQL Server 2005 box then don't worry I have prepared the query below for you.

SELECT * FROM sysobjects
WHERE name LIKE 'dm_%'
AND name NOT IN('dm_os_hosts',
'dm_exec_cached_plan_dependent_objects',
'dm_os_memory_allocations',
'dm_os_loaded_modules',
'dm_db_task_space_usage',
'dm_os_memory_objects',
'dm_os_schedulers',
'dm_os_threads',
'dm_exec_requests',
'dm_db_missing_index_columns',
'dm_repl_tranhash',
'dm_qn_subscriptions',
'dm_db_session_space_usage',
'dm_exec_query_optimizer_info',
'dm_tran_top_version_generators',
'dm_os_waiting_tasks',
'dm_exec_background_job_queue',
'dm_exec_cursors',
'dm_db_missing_index_details',
'dm_clr_properties',
'dm_os_sublatches',
'dm_exec_query_memory_grants',
'dm_tran_current_snapshot',
'dm_io_virtual_file_stats',
'dm_exec_xml_handles',
'dm_os_wait_stats',
'dm_exec_text_query_plan',
'dm_broker_connections',
'dm_os_stacks',
'dm_os_ring_buffers',
'dm_db_missing_index_groups',
'dm_exec_cached_plans',
'dm_exec_sessions',
'dm_broker_forwarded_messages',
'dm_os_memory_clerks',
'dm_repl_articles',
'dm_fts_memory_buffers',
'dm_fts_index_population',
'dm_tran_current_transaction',
'dm_os_child_instances',
'dm_exec_connections',
'dm_exec_background_job_queue_stats',
'dm_fts_active_catalogs',
'dm_tran_database_transactions',
'dm_os_memory_cache_clock_hands',
'dm_repl_schemas',
'dm_db_mirroring_connections',
'dm_db_index_operational_stats',
'dm_db_partition_stats',
'dm_io_pending_io_requests',
'dm_os_memory_cache_entries',
'dm_os_virtual_address_dump',
'dm_tran_transactions_snapshot',
'dm_exec_query_plan',
'dm_os_memory_cache_hash_tables',
'dm_exec_query_stats',
'dm_clr_tasks',
'dm_os_worker_local_storage',
'dm_db_index_usage_stats',
'dm_db_index_physical_stats',
'dm_os_buffer_descriptors',
'dm_tran_active_snapshot_database_transactions',
'dm_tran_active_transactions',
'dm_db_file_space_usage',
'dm_broker_activated_tasks',
'dm_broker_queue_monitors',
'dm_os_memory_cache_counters',
'dm_tran_session_transactions',
'dm_clr_appdomains',
'dm_exec_sql_text',
'dm_os_memory_pools',
'dm_os_latch_stats',
'dm_io_backup_tapes',
'dm_fts_memory_pools',
'dm_os_sys_info',
'dm_tran_locks',
'dm_exec_query_transformation_stats',
'dm_exec_query_resource_semaphores',
'dm_repl_traninfo',
'dm_db_missing_index_group_stats',
'dm_fts_population_ranges',
'dm_os_performance_counters',
'dm_os_workers',
'dm_io_cluster_shared_drives',
'dm_os_tasks',
'dm_exec_plan_attributes',
'dm_tran_version_store',
'dm_os_cluster_nodes',
'dm_clr_loaded_assemblies')

Enjoy exploring these DMVs

The Feature Pack is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

Redistributable components for SQL Server 2008
Add-on providers for SQL Server 2008
Backward compatibility components for SQL Server 2008

Microsoft SQL Server 2008 Native Client
Microsoft SQL Server 2008 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 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.


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 both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report processing and management capabilities in SharePoint integrated mode. This version also includes data-driven subscriptions. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services or Microsoft Office SharePoint Services.

Get it all here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D68DE3C9-60A9-49C9-A28C-5C46BBC3356F&displaylang=en

SQL Server 2008 February CTP is finally here, this CTP is supposed to be feature complete.

Download the February CTP from the Download Center!

What's New in SQL Server 2008 February CTP?

Enter to win an Xbox in the SQL Server 2008 February CTP Bug Bash!

I decided to do a weekly links post with interesting SQL Server stuff, I also include a bunch of funny things at the bottom.

Technical Stuff:
Bob Beauchemin has two post that deal with Spatial Data: Using SQL Server 2008 spatial and the Virtual Earth map control - 1 and Using SQL Server 2008 spatial and the Virtual Earth map control - 2. The Spatial Data posts are more for people who do front end as well as back end development.
Two Interesting articles by the PSS SQL Server Engineers
How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s) and How It Works: SQL Server 2005 Connection and Task Assignments
Kalen Delany's Geek City: String Statistics post. My own Functions That Are Not Often Used: SIGN from sqlblog. Amdahl’s Law by Linchi Shea

If that is not enough for you there are also a bunch of SQL Server 2008 Webcasts available as well as a bunch of SQL Server 2008 Videos.

The non technical stuff:
Like Father Like Grandson
I had friends...
Pronounce this if you can
Peek into offices at LinkedIn, Flickr & Facebook

Just like Notification Services HTTP Endpoints have been deprecated in SQL Server 2008. Bob Beauchemin's Blog has more info here: HTTP Endpoints to be deprecated in SQL Server 2008