SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: July 2011

The Good, the Bad and the Ugly SQL Cursor types

T-SQL cursors are generally bad approach and are often misused. In today’s world of correlated subqueries, CTE’s, recursive CTE’s, ranking and windowing functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and other tools, you have really few moments where cursor is better solution. On the other hand, there are still scenarios where cursor… Read more

0 comments, 1,735 reads

Posted in SQL Treeo on 30 July 2011

SQL Azure – simple description, part I.

I was studying SQL Azure parameters some time ago and found out that there are no materials which tell you clearly what it is, what it costs, what are limitations, what developers can and can’t do etc. There are so many marketing materials, business materials and even very technical materials… Read more

0 comments, 1,564 reads

Posted in SQL Treeo on 29 July 2011

T-SQL refactoring

Keeping your SQL objects’ naming rules during furious development is hard. New levels of information are added continuously, entities are being splitted, then joined together again, entities are often renamed several times just because you have better name for them etc. For me, renaming is heart of T-SQL refactoring. But… Read more

1 comments, 2,471 reads

Posted in SQL Treeo on 28 July 2011

Fastest row counting method

SELECT COUNT(*) is most common method (and exact) how to find out how many records is in table. There is also another method which is not exact but is way faster especially when you are expecting zillion of rows to be counted. This method is based on fact that SQL… Read more

23 comments, 6,072 reads

Posted in SQL Treeo on 26 July 2011

Partial name matching in SQL Server 2011 “Denali”

While playing with SQL Server 2011 “Denali”, I’ve accidentally found out that it has slightly enhanced intellisense in SSMS. There is one thing which I really like much – partial name matching. Prior to SSMS 2011 you had to enter object’s first letters in order to be completed by intellisense.… Read more

2 comments, 1,280 reads

Posted in SQL Treeo on 21 July 2011

Article: How to use temporary table in function

Steve Jones from voiceofthedba.com, editor on SQLServerCentral.com gave me an opportunity to publish humble article about workaround solution for using temp table in user-defined function. I was very pleased and I am now proud that my article is on SQLServerCentral homepage (at least for today).

After reading discussion under article… Read more

0 comments, 442 reads

Posted in SQL Treeo on 21 July 2011

Custom folders SSMS Add-In slightly delayed

I am desperately trying to finalize this add-in and it is very very close. I was performing another testing on few thousands of objects and found out that add-in is causing instability to Management Studio from time to time while handlilng this high numbers of objects. It is very hard… Read more

0 comments, 494 reads

Posted in SQL Treeo on 17 July 2011

SQL Injection is still actual :)

Here are two samples showing that SQL Injection is still here and dangerous !!!


This use case is especially insidious

Read more

0 comments, 616 reads

Posted in SQL Treeo on 16 July 2011

Tracking table changes seamlessly with Change Data Capture (CDC)

I am going to describe quite forgotten feature in SQL Server 2008. It is Change Data Capture (CDC) – powerful feature to track changes in database (tables) without touching table’s schema. There are two features in SQL Server 2008 which support similar thing and look same but they are not… Read more

0 comments, 2,081 reads

Posted in SQL Treeo on 15 July 2011

Do you use T-SQL debugging?

T-SQL debugging is not 100% in SQL Server. It has few bad drawbacks starting from difficulties when setting it up and ending with inability to view any data table during debug.
I have an idea for post for DBAs who use T-SQL debugging and are interested in improving debugging experience.… Read more

3 comments, 1,095 reads

Posted in SQL Treeo on 13 July 2011

Copying parent-child rows and SQL Dependency Tracker

I was facing quite well-known issue today – I had to develop logic of duplicating (versioning) parent-child records. Because I was not fully familiar with database schema I simply didn’t know which entities I should involve. I am very visual person and that’s why I employed Red Gate’s tool called… Read more

2 comments, 1,517 reads

Posted in SQL Treeo on 12 July 2011

Select random records using TABLESAMPLE clause

While I was reading Martin Catherall’s post about Selecting from a table with no rows returned I remembered TABLESAMPLE function which does similar thing.

TABLESAMPLE function works together with SELECT statemenet to show sample of rows from respective table. You can use this function to “randomize” standard SELECT TOP xx… Read more

1 comments, 1,288 reads

Posted in SQL Treeo on 11 July 2011

Denali’s Skeletons in the closet (and workaround), Part II

I continued to check SQL Server 2011 “Denali” for few things I dislike on 2008 R2 and tried to find out whether they were improved (first part is here).

User defined table value types are great step forward in T-SQL programming. They worth in many situations, for me it… Read more

0 comments, 552 reads

Posted in SQL Treeo on 11 July 2011

How to merge (combine) date intervals in T-SQL

On my current project, I am dealing with date intervals in T-SQL very heavily. I’ve hit interesting issue recently – how to combine/merge date intervals into more intervals.

I am talking about this:

I was googling and googling but found only not suitable solutions or solutions which I don’t like.… Read more

5 comments, 1,900 reads

Posted in SQL Treeo on 8 July 2011

Showing slowest parts of SQL query

I’ve received interesting question/requirement few days ago:

“… When viewing a query plan graphically, we usually have to hunt for the most expensive operator.  In some queries, it’s hard to spot ‘em due to the high number of operators.  It would be cool to have a keyboard command that would… Read more

4 comments, 1,474 reads

Posted in SQL Treeo on 8 July 2011

Wife and kids are leaving for holiday (it’s not off-topic)

My wife and kids are leaving for holiday (without me). Bad thing is that I will miss them, good thing is that I will finally finish my SSMS add-in I promised. If you are not aware about it, read here why I am developing it.

After this SSMS add-in development… Read more

3 comments, 1,195 reads

Posted in SQL Treeo on 7 July 2011

Less code with rethrowing “exception” in SQL Server 2011

I wrote about some skeletons in the Denali’s closet here. Let’s also put some good on the table. Very handy new thing in Denali is the way how you can rethrow error in catch block.

In SQL Server 2008 you must do this to rethrow error:



Read more

2 comments, 1,064 reads

Posted in SQL Treeo on 6 July 2011

Denali’s Skeletons in the closet, Part I

I’ve started to play little bit with SQL Server 2011 CTP Denali to find out whether some open wounds were healed yet. I like new things in Denali but I’ve hit one thing I am still terribly missing. This is RAISERROR in function.

Today is black day for me because… Read more

2 comments, 425 reads

Posted in SQL Treeo on 6 July 2011

SQL Server on steroids with RAM disk

I’ve faced pretty common situation recently – you need to work with huge data during development/testing. It’s good if you have option how to access large data volumes XXX% faster on your laptop. I can imagine many other scenarios when you need to work with hundreds thousands of records on… Read more

2 comments, 1,387 reads

Posted in SQL Treeo on 5 July 2011

How to re-create database quickly

I was running some database unit tests and needed to drop completely everyhing in my database. There are really many ways how to do that but I like following way because it kills all existing connections automatically and can be executed with one keystroke. It is not any rocket science… Read more

1 comments, 621 reads

Posted in SQL Treeo on 4 July 2011