SQL Advent Recap and What is coming in SQL Server 2012 recap

In case you missed these, here is a recap of the SQL Advent and the SQL Server 2012 posts

SQL Advent Recap

Day 1: Date and time
In this post I covered the new date, datetime2 and time datatypes

Day 2: System tables and catalog views
In this post we took a look what the replacements are for the all system tables and also gave you a table with the new catalog view/compatibility view equivalent of the old system table

Day 3: Partitioning
In this post I looked at partitioning in pre sql 2005 days by showing you how to create partitioned views. I also showed you how to user partitioned function in sql 2005 and up

Day 4: Schemas
In this post I show you what schemas are and how they can help with security and logical grouping of objects

Day 5: Common Table Expressions
The Common Table Expressions post showed you what Common Table Expressions are and how they can be used to simplify your code

Day 6: Windowing functions
The Windowing functions post showed you how to do different kinds of rankings

Day 7: Crosstab with PIVOT
This post was all about pivoting/transposing/crosstabbing data with the PIVOT operator, also was shown how to do it dynamically

Day 8: UNPIVOT
This post showed you how to use UNPIVOT to get the reversed effect of PIVOT

Day 9: Dynamic TOP
The dynamic TOP post showed you how to do dynamic TOP without dynamic SQL or SET ROWCOUNT

Day 10: Upsert by using the Merge statement
This post was all about how to use MERGE to do an UPSERT (Update if it exists otherwise insert)

Day 11: DML statements with the OUTPUT clause
This post showed the usefulness of the OUTPUT clause

Day 12: Table Value Constructor
This post showed you how to use Table Value Constructor

Day 13: DDL Triggers
The DDL trigger post showed you how to use DDL triggers and also explained why you might want to use them

Day 14: EXCEPT and INTERSECT SET Operations
This post was all about the two new SET Operations EXCEPT and INTERSECT 

Day 15: Joins
This post showed you how to use the newer ANSI SQL JOIN syntax and also showed you what was deprecated

Day 16: CROSS APPLY and OUTER APPLY
Shown was how to use APPLY with derived tables as well as functions

Day 17: varchar(max)
In this post I showed you why varchar(max) is much better than the text data type

Day 18: Table-valued Parameters
I showed you how to use Table-valued Parameters to pass around tables

Day 19: Filtered Indexes
In this post I showed you how to create a filtered index and why it can be beneficial in your database

Day 20: Indexes with Included Columns
On this day I showed you how to cover you query by using Indexes with Included Columns

Day 21: TRY CATCH
Error handling go better in SQL Server 2005 and I show you how to use TRY CATCH

Day 22: Dynamic Management Views
In this post I show how you can use Dynamic Management Views to get all kinds of information about your server and databases

Day 23: OBJECT_DEFINITION
The OBJECT_DEFINITION covers ways to get the text of an object and also show you why it is better than sp_helptext or syscomments

Day 24: Index REBUILD and REORGANIZE
This post is all about rebuilding and reorganizing(defragmenting) indexes

What is coming in SQL Server 2012

First look at SQL Server Management Studio Denali
In this post I show you what the new SQL Server Management Studio which is based on Visual Studio 2010 looks like

A first look at sequences in SQL Server Denali
Sequences finally made it into SQL Server, this post will show you how to use sequences

Screenshots of the new look and feel of BIDS in Denali
Business Intelligence Development Studio has a new look and feel, SSIS has never looked better

Debugging In SQL Server Denali
Debugging got much better in SQL Server 2012, this post will look at some of the new things

Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging
With OFFSET N ROWS FETCH NEXT N ROWS ONLY paging has never been easier

Playing around with sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object
The sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object data management views makes it easy to find out what a proc or SQL statement returns

A Quick look at the new IIF function in Denali CTP3
SQL Server now has the IIF statement, this is just shorthand for CASE but it will make for shorter code

A Quick look at the new EOMONTH function in SQL Server Denali CTP3
The EOMONTH function will give you the last date of the month

DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3
With DATEFROMPARTS and DATETIMEFROMPARTS you can construct a date by passing in a bunch of integers

ColumnStore Index limitations in SQL Server Denali CTP3
Some limitation that the current version of ColumnStore Index have

Concat function in SQL Server Denali CTP3
Finally SQL Server 2012 has the Concat, this is something other RDBMSes had for years

Format function in SQL Server Denali CTP3
Formatting has never been easier, no need to use style parameters anymore

SQL Server Columnstore Index FAQ wiki page published
Columnar storage is new in SQL Server 2012, make sure to read the wiki for all your questions

TRY_CONVERT in SQL Server Denali CTP3
Before converting, use TRY_CONVERT that will tell you if the value can be converted to the data type you want

SQL Server Code Name “Denali” CTP3 Product Guide available for download
There is some cool documentation available, this includes white papers and videos

Microsoft SQL Server, codename "Denali", will be the last release to support OLE DB, ODBC is the new new thing
Looks like OLE DB has run its course, ODBC is the new thing

SQL Advent 2011 series of posts

I have started a series of post about upgrading your skills to 2005 and 2008 versions of your code from 2000. Below is a list of posts that are already up

Day 1: Date and time
Day 2: System tables and catalog views
Day 3: Partitioning
Day 4: Schemas
Day 5: Common Table Expressions
Day 6: Windowing functions
Day 7: Crosstab with PIVOT
Day 8: UNPIVOT
Day 9: Dynamic TOP
Day 10: Upsert by using the Merge statement
Day 11: DML statements with the OUTPUT clause

Also check out the post Are you ready for SQL Server 2012 or are you still partying like it is 1999? which is the one that explains why I did this

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

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

sp_indexinfo an enhanced index information procedure

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

Interview With Erland Sommarskog About SQL Server and Transact SQL

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

Enjoy

How to log when a function is called?

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb


--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table

SELECT * FROM LogMeNow

What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

Should SQLServer Have The CREATE [OR REPLACE] PROCEDURE Syntax?

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?

How to rename a column in a SQL Server table without using the designer

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

Solutions for Common T-SQL Problems Wiki Launched

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

Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database

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

SQL Champ - A Quiz to Prove Your SQL Knowledge

In case you are bored CodeProject has a small SQL test (7 questions)

Take the test here: SQL Champ - A Quiz to Prove Your SQL Knowledge

And did you get everything correct?

And yes they wrote Knowlege instead of Knowledge :-)

What tastes better, Microsoft SQL Server 2005 or Oracle 11g?

Find out here: http://www.databasetastechallenge.com/landing/index.html
You can win some cool prizes. the video is very funny

Review of Inside Microsoft SQL Server 2005 Query Tuning and Optimization

SQL performance tuning is probably one of those things you can do to really make a HUGE difference in performance. Let’s put this in perspective: take a typical application, if you can improve the performance by 100% then you really made a huge improvement. You can improve a SQL query by 1000% with 2 lines of code (sometimes all you have to do is take away a % sign). If you can make a query sargable so that the optimizer can do an index seek instead of an index scan your query might go from 12 seconds to 200 milliseconds. Now try doing that in an application, even if you change all the string concatenation to use a stringbuilder instead of creating new strings all the time you will not get such a drastic performance improvement. I am sure you get the point by now, let’s talk about the book.

Inside Microsoft SQL Server 2005: Query Tuning and Optimization is part 4 of the Inside Microsoft SQL Server 2005 series, it is written by Kalen Delaney and five other authors. There are 6 chapters in this book

1 A Performance Troubleshooting Methodology

This chapter explains some typical things that affect performance and also gives a troubleshooting overview


2 Tracing and Profiling

This chapter explains how to use the profiler and how to analyze traces. SQL Server’s built-in traces are also covered


3 Query Execution

This chapter gives a query processing and execution overview. It explains how to read plans and goes into a lot of detail about analyzing plans


4 Troubleshooting Query Performance

This chapter explains how to detect problems in plans, how to improve queries and some best practices


5 Plan Caching and Recompilation

This chapter goes into detail about plan caching and recompilation and how to troubleshoot plan cache issues


6 Concurrency Problems

The final chapter deals with concurrency (locking, blocking and deadlocking)



This is an excellent book for an intermediate/advanced developer. There is so much new stuff in SQL Server 2005 compared to 2000 to help you with tuning queries that you probably want to read each chapter several times. The Dynamic Management Views are a big help and this book shows you how to use them. Some other cool stuff in this book is the discussion of internal tables, undocumented DBCC commands and undocumented trace flags to discover information which could help you determine much faster what the cause of a performance problem might be.
Some pages are packed with so much information that you need to pause for a second and process all that info (I have read some pages two to three times in a row). You will also find out that there are more joins besides left, full and outer. Page 137 for example has a nice table with the three Physical Join Operators: Nested Loop Join, Hash Join and Merge Join. This table lists the characteristics for each of these joins.

If you are an intermediate to advanced developer then I highly recommend this book. I have interviewed Kalen a while back about this book and you can find that interview here
http://sqlservercode.blogspot.com/2007/10/interview-with-kalen-delaney-about.html

Vote for SQL Server 2005 Service Pack 3

Hugo Kornelis makes a good point here: Want a Service Pack? Ask for it!

Service pack 2 for SQL Server 2005 is already 11 months old. And there is still no sign of service pack 3 on the horizon. Why is that? Has Microsoft managed to release a perfect, completely bug-free product? No, of course not – with the size and complexity of a product such as SQL Server is, that will simply never happen.

So do yourself (and me) a favor and vote here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575

Tip: Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both

This question is asked every now and then so I decided to do a little blog post. How can you quickly without writing a bunch of OR statements determince if any columns have a NULL value, a value of 0 or if the value is 0 or NULL.
To test for NULL is very easy, you just concatenate the columns since NULL + anything else is always NULL. Okay that also depends on a setting.

Run this

SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + '1' --NULL

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + '1' --1

As you can see if CONCAT_NULL_YIELDS_NULL is OFF then the result is 1

Now take a look at this


SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + 1 --NULL



SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + 1 --NULL


So with numeric values it behaves differently. Either way by default CONCAT_NULL_YIELDS_NULL is set to on
To test for NULLS or zeroes you use NULLIF
To test for zeros you can combine COALESCE and NULLIF

Here is the code which shows all of that

CREATE TABLE #test(column1 int,column2 varchar(4),column3 float)

INSERT #test VALUES(2,'2',2)
INSERT #test VALUES(0,'1',0)
INSERT #test VALUES(null,'1',0)
INSERT #test VALUES(1,null,0)
INSERT #test VALUES(0,'1',null)
INSERT #test VALUES(null,null,null)



--Any column is Null
SELECT * FROM #test
WHERE column1 + column2+column3 is null

Output
------------
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL



--Any column is Null or zero
SELECT * FROM #test
WHERE NULLIF(column1,0) + NULLIF(column2,0)+NULLIF(column3,0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL




--Any column is zero
SELECT * FROM #test
WHERE NULLIF(COALESCE(column1,1),0) +
NULLIF(COALESCE(column2,1),0)+
NULLIF(COALESCE(column3,1),0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL


DROP TABLE #test

SQL Server 2005 Best Practices Analyzer (January 2008) Available For Download

This download is the January 2008 release of SQL Server 2005 Best Practices Analyzer.

Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Has Anyone Succeeded In Creating A Collision Between NEWID and NEWSEQUENTIALID

SQL Server 2005 introduced a new type of function to create a uniqueidentifier; the NEWSEQUENTIALID(). This new function has been created for performance reasons, each new value is greater than the previous value. In theory this means that the value will be inserted at the end of a page and not in the middle which can cause splits.

Let's run this code to see the difference

CREATE TABLE #TableSeqID (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
ColumnB uniqueidentifier DEFAULT NEWID())

INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
GO


SELECT * FROM #TableSeqID

Output
-----------
BBF765FE-57BD-DC11-875F-000D5684F8D8 CE51B9E4-1640-47E2-87C6-6ADD46C63A87
BCF765FE-57BD-DC11-875F-000D5684F8D8 CA220BAB-462E-440E-829A-E0037CAF0D1F
BDF765FE-57BD-DC11-875F-000D5684F8D8 01748772-8155-4F83-A58F-CC3253DDD3F3
BEF765FE-57BD-DC11-875F-000D5684F8D8 9C4B2C87-AE33-4432-8310-3BE731179382
BFF765FE-57BD-DC11-875F-000D5684F8D8 1F84B827-F42A-4C47-8A1B-4B672B4402F1


As you can see ColumnB is random (Or at least pseudo-random) while ColumnA is not
Let's say you have a table with a billion rows, this table used NEWID() up till now. What will happen when you change the table to use NEWSEQUENTIALID(), could you get a duplicate?
I tried my best and filled up my 400GB External Seagate drive without success

Have you run into a collision, is it even possible?

Use the *1 trick to do math with two varchars, this prevents the Invalid operator for data type. Operator equals subtract,type equals varchar message

Someone had code like this on the tek-tips forum

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) -SUBSTRING(@v,10,4)

If you run this code, you will get the following message
Server: Msg 403, Level 16, State 1, Line 4
Invalid operator for data type. Operator equals subtract, type equals varchar.

Instead of casting to integers you can also use this little trick. You basically multiply one of the values by 1

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) *1 -SUBSTRING(@v,10,4)



Another example. This doesn't work
SELECT '2' - '1'

This does work
SELECT '2' * 1 - '1'

A year in review, The 21 + 1 best blog posts on SQLBlog

These are the best posts according to me, it might have been the content or it might have been the comments. Either way here are the 21 + 1 posts I enjoyed the most. But wait the year is not over yet. Doesn't matter no one will write anything good till next year anyway (prove me wrong and I WILL update this post).

The first post is really from last year, I mean really, who writes a post on '2006-12-31T13:49:00.000' (yes that is ISO8601)? Since I did not see the post until 2007 I have included it in the list. I tried not to include more than 2 or 3 posts per blogger, I would have included all Hugo's NULL posts otherwise. What is with the 21 + 1 title? The +1 one is my post about using bitwise logic, it is the last link

Below is the list, let me know if I missed anything you really enjoyed and Happy New Year



The Beatles versus the Stones
How Many Data Files Should I Create for a User Database?
[Editorial] Get rid of the bad apples in IT?
NULL - The database's black hole
Performance Impact of Procedure Calls without Owner Qualification -- SQL Server 2000
Performance Impact of Procedure Calls without Owner Qualification
Did You Know? Run a batch multiple times
Want to Control the Procedure Cache?
Is statistics over non-indexed columns updated by index rebuild?
Xp_cmdshell and permissions
The Perils of Hyperthreading for SQL Server
5 Things every DBA should know like the back of their hand...
Filegroups Part I - Dividing Data for Performance
Did You Know? SP2 does NOT limit the amount of plan cache you can have
Sorted views...
2008: Initializing Table Data with Row Constructors
2008: Row Constructor or Table-Valued Parameter
Anti-Patterns and Malpractices, Volume 1: Tumbling Data
10 lessons from 35K tps
What’s wrong with SELECT * ?
Backup compression in SQL Server 2008


This one is mine


Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic

Red Gate SQL Data Generator 1.0 Beta Available For Download

Red Gate have released Red Gate SQL Data Generator 1.0 Beta

From the site:

This tool is aimed at generating test data for SQL Server databases. It can either generate data from scratch or import from existing sources of data.(Like SQL tables or CSV files.)
Features:
- Full SQL Server 2000 and 2005 support
- All data types supported except CLR types
- Pre & Post Scripts execution
- Command-line access version
- Import data from CSV or SQL tables
- Customizable generator settings that allow configuring the amount of nulls, unique values, minimum and maximum values, etc..
- Diverse range of inbuilt generators
The product is not yet complete, and we are looking for user feedback about what features and or functionality you would like in the final product.
*The beta version is set to work until 11 Apr 2008. "

This is a free open beta with the application due to expire on 11 April 2008 with a planned final released sometime before then.

What we really want now is for everyone to use the software and provide us with feedback for the final version. Please let us know on the forums what you like and don't like about the software and what we could do to solve all of your data generation problems!

Visit http://www.red-gate.com/MessageBoard/viewtopic.php?t=6140 for the announcement and download URL

Forums: http://www.red-gate.com/MessageBoard/viewforum.php?f=76