Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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, 859 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, 667 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, 1,384 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, 2,473 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, 752 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, 245 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, 192 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 !!!


source:xkcd.com


This use case is especially insidious

Read more

0 comments, 369 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, 1,186 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, 466 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, 687 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, 589 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, 236 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, 861 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, 649 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, 816 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:

BEGIN TRY

    --…

Read more

2 comments, 524 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, 198 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, 479 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, 304 reads

Posted in SQL Treeo on 4 July 2011