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

OT: Being healthier as a geek

This is not a technical post but I decided to spend a little of my blogging and web surfing time on my body this year...oh how vain right? Of course I didn't start January 1st but on January 8th, why do the same as everyone else. I lost 4.8 pounds the first week, you can follow my progress here: http://188point6.blogspot.com/

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

6 new SQL Server Denali CTP3 posts

I have created 6 new SQL Server Denali CTP3 posts

These posts are about the new things introduced in SQL Server Denali CTP3, below is a list of the posts

A Quick look at the new IIF function in Denali CTP3

A Quick look at the new EOMONTH function in SQL Server Denali CTP3

DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3

ColumnStore Index limitations in SQL Server Denali CTP3

Concat function in SQL Server Denali CTP3

Format function in SQL Server Denali CTP3

Post I created about SQL Server Denali

Today at PASS the SQL Server team has publicly announced the first Community Technology Preview of the next version of SQL Server code named Denali. You can download it here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

I have been playing around with Denali for a couple of weeks now and created a couple of posts. Below is a list of the posts, click on each link to get some more information about Denali

First look at SQL Server Management Studio Denali

Screenshots of the new look and feel of BIDS in Denali

Find what is deprecated in SQL Server Denali by using sys.dm_os_performance_counters

Playing around with sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object

A first look at sequences in SQL Server Denali

Don’t install Denali BIDS side by side with a 2008 instance

The new Dynamic Management Views in SQL Server Denali

Debugging In SQL Server Denali

Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging

Win A Copy Of SQL Server 2008 Administration in Action

Rod Colledge was kind to give the readers of Less Than Dot a chance to win a pdf copy of his book SQL Server 2008 Administration in Action.

Here is how you can win this book, first read the Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action post and tell me which question or answer you liked the most and why. Leave a comment on that post, do not leave it here. I will announce a winner this Friday at 12 PM EST here: Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action

10 must read SQL Server posts by Ted Krueger

A buddy of mine Ted Krueger writes some really interesting SQL Server posts. Most of these posts focus more on administration than on developer stuff but still stuff that is useful to a developer. Here are 10 of my favorites, I liked these and you will too!


6 ways to import data into SQL Server
Recover a database without the ldf file
Dynamic column names and fields in SSRS (Custom Matrix)
Rebuild master and restore system databases from complete disk failure
Do not truncate your ldf files!
Proactive Deadlock Notifications
Troubleshooting Blocking 101 on SQL Server
Not a fan of the Report Manager in SSRS? Using SSRS procedures to get the job done
Dynamic Data Sources in SSRS
Quick Data Model creation off SSAS Data Source in SSRS

You can also follow Ted to see what is up to on twitter, his username is @onpnt

Some SQL Server posts I wrote lately and I now have a RSS feed just for my posts on lessthandot.com

The reason I am writing this post is that we now have RSS feeds on lessthandot for a specific author. In my case the RSS feed is the following: http://blogs.lessthandot.com/index.php/All/?tempskin=_rss2&author=4

This URL has all the blogposts I have written: http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=4

Here is a list of the last 10 posts I wrote about SQL Server

• Finding Out How Many Times A Table Is Being Used In Ad Hoc Or Procedure Calls In SQL Server 2005 And 2008
• When Books On Line is not really correct
• Dealing with the could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT error message
• The differences between LEN and DATALENGTH in SQL Server
• Ecofont, Will this save the planet?
• Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008
• A couple of ways of getting the top 2 distinct values from a set in SQL Server
• Vulnerability in Microsoft Video ActiveX control could allow remote code execution
• How to copy data/append data into files from within T-SQL
• How To Script Out The Whole Database In SQL Server 2005 and SQL Server 2008

And remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

With this post I bid you all adieu

I have posted on this blog for a little over 3 years now and decided it is time to call it quits at this URL.
All SQL Server related post from me in the future will be on SQLBlog.com and on lessthandot.com.
All non tech post will be on denisgobo.blogspot.com, here you will also be able to connect with me on twitter, flickr, friendfeed and linkedin


below are some URLs

Lessthandot.com
Mainsite
Database Blogs
Database Blogs RSS feeds
All Blogs
All Blogs RSS Feed

SQLBlog.com
Mainsite
My Blog
My Blog RSS feed

Personal Blog
URL
RSS Feed

See you there, don't forget to subsrcibe

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

today is a sad day

This was not the way I envisioned going back to Amsterdam. My mom past away today, two weeks shy of her 60th birthday
Since my passport was expired they issued me a temporary passport. My parents were visiting me in Princeton last month so I did see my mom recently. I just feel sad for my dad now who is all alone. :-(

Help me win this bet

You might know that some friends and myself have launched a site named LessThanDot, this is a site where users can collaborate via the wiki, ask questions in the fora and can read out blogs. It is a technical site but we do have a forum where you can post pretty much anything you want(not vulgar or offensive). Here are some examples
The Rage of the Previously Rich 
We're In Ur Bank, Bailing It Out 
Girls Habanero Eating Contest 

Of course we do have our technical forums, some examples
Microsoft SQL Server
ASP.NET
Tech Rants

So I made a bet with the other owners of this site that I will have the most people sign up until November 1st. So please sign up and let your coworkers/friends know and let them sign up too, after all you don't want me to lose or do you?


Don't be scared to participate in the forums  :-)


My username is SQLDenis

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

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

Interesting Bug/Feature In SQL Server 2008 RTM

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?

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Take a look at this query.

SELECT * FROM

(

SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE YEAR(payment_dt) = YEAR(GETDATE())

and MONTH(payment_dt) = MONTH(GETDATE())

GROUP BY customer_id) MTD_payments

UNION ALL

(

SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE

WHERE YEAR(payment_dt) = YEAR(GETDATE())

GROUP BY customer_id) YTD_payments

UNION ALL

(

SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table) LTD_payments

) payments_report

ORDER BY customer_id, record_type

Can you see the problem?
A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don't think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

The problem is that the following piece of code

where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())

is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

Let's get back to the query, what can we do to make this piece of code use an index seek?
where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())

You would change it to this:
where payment_dt >= dateadd(mm, datediff(mm, 0, getdate())+0, 0)
and payment_dt < dateadd(mm, datediff(mm, 0, getdate())+1, 0)

You can see the complete question on the MSDN forum site here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1
The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

The person had this

AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0

AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0

I told him to change it to this

AND DateCreated >= '08/10/2008'
and DateCreated <= '08/15/2008'

And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns

If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
How Are Dates Stored In SQL Server?
Do You Know How Between Works With Dates?

SQL Tip, Compiling Your SQL Without Running It to See If It Would Run

Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
Take this simple example

SELECT GETDATE()
GO
SELECT 1/asasasas
GO


You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
Execeute the code above and you will get this


(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.

SQL server has the SET NOEXEC statement. From BOL:

When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
batches are executed after compilation.

The execution of statements in
SQL Server consists of two phases: compilation and execution. This setting is
useful for having SQL Server validate the syntax and object names in
Transact-SQL code when executing. It is also useful for debugging statements
that would usually be part of a larger batch of statements.

The setting
of SET NOEXEC is set at execute or run time and not at parse time.

So execute the code below

SET NOEXEC ON
GO

SELECT GETDATE()
GO
SELECT 1/asasasas
GO
SET NOEXEC OFF
GO



As you can see the output is the following:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.


You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem.

Microsoft Visual Studio 2008 Service Pack 1 Available For Download

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 :-)

SQL Server 2008 is RTM

that is right and you can already download it from MSDN

Microsoft Visual Studio Team System 2008 Database Edition GDR July CTP Released

Microsoft® Visual Studio Team System 2008 Database Edition GDR July CTP is available for download

In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.

Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.

Download it here: http://www.microsoft.com/downloads/deta ... laylang=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

Awesome Collection Of ASP.NET Hacks, Tips and Tricks

We added a bunch of ASP.NET Hacks, Tips and Tricks

we have the following categories
1 Applications
2 Caching
3 Controls
4 Database
5 Dates
6 Debugging
7 Email
8 Encryption
9 Files
10 Images
11 Javascript
12 Objects and Classes
13 Pages
14 Sessions
15 Strings
16 Validation
17 Visual Studio
18 Web

The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks
Bookmark that URL because we will be adding more hacks, tips and tricks

Interview With SSIS Guru Jamie Thomson

As promised yesterday, here is the link to the interview I did with jamie Thomson: Interview With SSIS Guru Jamie Thomson

I tried to keep it a secret but it is all over the internet: I am a SQL Server MVP

So all over the internet is exaggerated,I became a SQL Server MVP today and was not going to do a blog post about it. However some other people had other ideas

Congratulations, Denis!
SQL Server MVP - Denis Gobo

and even an announcement on lessthandot

My profile is here: https://mvp.support.microsoft.com/profile=BCCF7416-DA4E-4D73-83E2-65FD61BAB16D

Stay tuned, I will have an interview with Jamie Thomson tomorrow

Working On SQL Admin Hacks, Tips and Tricks

I haven't posted for a while because I have been working on SQL Admin Hacks, Tips and Tricks lately. it is still a work in progress but below is what is on the wiki currently. It is not yet categorized but we will do that once we get more of these hacks done. To see what it will look like when it is done take a look at the SQL Server Programming Hacks

Can you think of any admin stuff you would like to see? This is what we have right now

Find Primary Keys and Columns Used in SQL Server
Get The Domain Name Of Your SQL Server Machine With T-SQL
Grant Execute/SELECT Permissions For All User Defined Functions To A User
Grant Execute Permissions For All Stored Procedures To A User
Kill All Active Connections To A Database
SQL Server 2008: When Was The Server Last Started?
Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
Three Way To List All Databases On Your Server
Generate A List Of Object Types By Using OBJECTPROPERTY
How to find all the tables and views in a database
Find Out Server Roles For a SQL Server Login
Which Service Pack Is Installed On My SQL Server
Test SQL Server Login Permissions With SETUSER
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Compare Tables With Tablediff
Find All Tables Without Triggers In SQL Server
Find All Tables With Triggers In SQL Server
Create Stored Procedures That Run At SQL Server Startup
Cycle The SQL Server Error Log
How to read sql server error messages
Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
SQL Compare Without The Price Tag
How To Get The Database Name For The Current User Process
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server

SQL Server 2008 Release Candidate 0 Available To The General Public For Download

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

Puzzle: Friday the Thirteenths

There is a puzzle on LessThanDot

The goal is to identify all friday the thirteenths for a given timeframe. We'll use a relatively small number of years, like 10. This should make it a little easier in procedural languages.

I posted my SQL solution, what about you?

Go here: Friday the Thirteenths and show me what you got. Make sure to use the [hide][/hide] tags so you don't spoil it for other people

Less Than Dot A New community Site Has Been Launched

Myself and a bunch of friends have been working on Less Than Dot for a while now. The site has a forum, blogs and a wiki. More info why we started Less Than Dot and who we are can be found here: http://www.lessthandot.com/aboutus.php

Since I am mostly a SQL guy, I wrote a collection of SQL Server hacks. This collection of SQL hacks is available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.

SQL Server Hacks Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are some direct links to a couple hacks, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks

Trouble With ISDATE And Converting To SMALLDATETIME
Row To Column (PIVOT)
Column To Row (UNPIVOT)
Five ways to return all rows from one table which are not in another table
Order IP Addresses
Use XACT_STATE() To Check For Doomed Transactions
Three differences between COALESCE and ISNULL
Case sensitive search
Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

Hopefully you will like the site and find some good content, if you have a question then don't hesitate to ask it in a forum.

SQL Teaser uniqueidentifier

Create this table

CREATE TABLE #bla (SomeVal uniqueidentifier)
INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')

what do you think this will return?


SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'
UNION ALL
SELECT * FROM #bla
WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'

Surprised?

What about this?

SELECT * FROM #bla
WHERE SomeVal = CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')

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

Interview With Craig Freedman About Indexing, Query Plans And Performance

I interviewed Craig Freedman about Indexing, Query Plans And Performance and more. You can find that interview here: Interview With Craig Freedman About Indexing, Query Plans And Performance

Enjoy

I have a question for you on DB development blunders!

Last week Kalen Delaney wrote Did You Know? I have a question for you on DBA Blunders! I though the comments were very interesting, S we did the DBA part but what about developers? What are some of the worst blunders you have seen?

Here are a couple of things

Starting a begin tran, then some insert/update command, never commiting but minimizing the window.


Here is my all time favorite, can you reduce this by 90%?


declare @Token int
select @Token = 51234567

declare @val int


if left(@Token,1) = 1
select @val = 1
else if left(@Token,1) = 2
select @val = 2
else if left(@Token,1) = 3
select @val = 3
else if left(@Token,1) = 4
select @val = 4
else if left(@Token,1) = 5
select @val = 5
else if left(@Token,1) = 6
select @val = 6
else if left(@Token,1) = 7
select @val = 7
else if left(@Token,1) = 8
select @val = 8
else if left(@Token,1) = 9
select @val = 9
else if left(@Token,1) = 0
select @val = 0


select @val


Actually we put that on the whiteboard after we found it in our code and every time the developer wanted something we teased him about it...Oh you mean like that (pointing to the whiteboard)


What about changing the datatype from smallint to int in the table but keeping the params smallint in the proc.....mmmm why do I get a conversion in the execution plan?





So let's here some of what you have seen others write, we all know we couldn't write stuff like that ourselves right? :-)

Spring Cleaning, Getting Rid Of Some Books

I did some spring cleaning yesterday, I got rid of some old or obsolete books. I asked some people at work if they want any of these books and I believe all the books except for the JCL book are about to be given away.

I got rid of these books because:
I got a new version
I don't use the technology anymore
The book is obsolete


Click on the image and you can see a bigger version on my flickr page.

SSMS Tools PACK 1.0 is now available

Mladen Prajdić just released SSMS Tools PACK 1.0. SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

Here are some of the features

Uppercase/Lowercase keywords.
Run one script on multiple databases.
Copy execution plan bitmaps to clipboard.
Search Results in Grid Mode and Execution Plans.
Generate Insert statements for a single table, the whole database or current resultsets in grids.
Query Execution History (Soft Source Control).
Text document Regions and Debug sections.
Running custom scripts from Object explorer's Context menu.
CRUD (Create, Read, Update, Delete) stored procedure generation.
New query template.

Check it out here: http://www.ssmstoolspack.com/Main.aspx

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 :-(

SQL Teaser: @@ROWCOUNT

Without running this what do you think will be printed?


SET ROWCOUNT 0
DECLARE @ int
SET @ =6
IF @@ROWCOUNT = 1
PRINT 'yes'
ELSE
PRINT 'no'
PRINT @@rowcount

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?

What Would You Like To Ask Erland Sommarskog?

In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Erland Sommarskog's name was submitted in comments a couple of times. I contacted Erland and am happy to anounce that he has agreed to do this.

The first time I heard of Erland Sommarskog was in the SQL Server programming newsgroups. I also read all the articles on his site. When I answer questions I usually give the link out to these 4 articles
Arrays and Lists in SQL Server
Implementing Error Handling with Stored Procedures
Error Handling in SQL Server – a Background
and of course The curse and blessings of dynamic SQL

I always wondered why he didn't write a SQL book. Hey, now I can ask him that :-)

Before you think of any questions please visit Erland's page first (http://www.sommarskog.se/index.html) and read some of the articles he wrote.

You can submit questions until Monday May 12th and then I will forward the questions to Erland.

What Would You Like To Ask Craig Freedman?

In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Craig Freedman's name was submitted in comments a couple of times. I contacted Craig and am happy to anounce that he has agreed to do this.

Before you think of any questions please visit Craig's blog first(http://blogs.msdn.com/craigfr/) to get a feel of what kind of questions to ask. It would make sense to keep the questions focused on query processing, query execution, and query plans. I told Craig that if he doesn't like a question that he does not have to answer it.

You can submit questions until Monday May 12th and then I will forward the questions to Craig

Do you depend on sp_depends (no pun intended)

HTML Source EditorWord wrap I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO


now create this function

CREATE FUNCTION somefuction(@id int)
RETURNS int
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go


now run this


sp_depends 'somefuction'

result: Object does not reference any object, and no objects reference it.



Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT

specific_name,*

FROM information_schema.routines

WHERE

object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND

routine_type = 'procedure'

BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character

Testing for SQL Server Vulnerabilities

I found this SQL Server Testing (not unit but vulnerability) page and decided I would post a link to it since it has some useful stuff. The link is below

http://www.owasp.org/index.php/Testing_for_SQL_Server

Here is what is covered. Enjoy (or live in fear over the weekend)


1 Brief Summary
2 Short Description of the Issue
3 Black Box testing and example
3.1 SQL Server Peculiarities
3.2 Example 1: Testing for SQL Injection in a GET request.
3.3 Example 2: Testing for SQL Injection in a GET request (2).
3.4 Example 3: Testing in a POST request
3.5 Example 4: Yet another (useful) GET example
3.6 Example 5: custom xp_cmdshell
3.7 Example 6: Referer / User-Agent
3.8 Example 7: SQL Server as a port scanner
3.9 Example 8: Upload of executables
3.10 Obtain information when it is not displayed (Out of band)
3.11 Blind SQL injection attacks
3.11.1 Trial and error
3.11.2 In case more than one error message is displayed
3.11.3 Timing attacks
3.11.4 Checking for version and vulnerabilities
3.12 Example 9: bruteforce of sysadmin password
4 References

Who do you want to see interviewed next?

I have interviewed a bunch of people already, some of them on this blog and some of them on SQLBlog

Below is the list of all the people so far

Louis Davidson
Itzik Ben-Gan
Ken Henderson
Kalen Delaney
Adam Machanic
Interview With Stéphane Faroult About Refactoring SQL Applications
Database Refactoring Interview With Scott W. Ambler
Interview With Joe Celko About The Forthcoming Book Thinking In Sets

Who else should I interview? I have some ideas but I would like you to participate. Here is my thinking, after we have a list of possible people, I will contact them and ask if they will agree to an interview. If they agree I will make a post here and ask you what you would like to ask this person. I usually have a good set of questions but there might be someone out there with some real good/interesting questions.
So post the name of the person you would like to see interviewed and hopefully this person will agree.

It would be nice if you did not pick a person who blogs at SQLBlog but if_you_really_wanted_to then I will not stop you :-0

Please keep it database related, it doesn't have to be specific to SQL Server but it has to be applicable to SQL Server.

SQL Teaser: Some Things Are Not What They Seem

This one is a little sneaky, don’t send me hate mail for it.

What does this return?



SELECT ISNUMERIC('+'),ISNUMERIC('–')

Copy and paste it into QA/SSMS to verify :-0