Technorati Tags: T-SQL,Scripts,Metadata

Someone asked a question in the forums the other day and I realized it would make a pretty decent blog post to explain the differences in functionality between these two and provide some code for working with them.

Differences

fn_get_sql returns the last *statement* executed, where as DBCC INPUTBUFFER returns the first statement in the batch.  See the example below for a better idea of what that means. fn_get_sql returns a TEXT field containing the statement that ran, DBCC INPUTBUFFER returns only a varchar(255).  This is important as you often won't get the full line here... and unfortunately there's not a lot you can do about that.  To my knowledge, DBCC INPUTBUFFER is still your only means of getting the first statement in the batch, however it returns an...

Read More »

If you read my last post, you may have noticed that my images now include boxes, balloon text and highlighting.  This is due to a friend insisting the he couldn’t live without Snagit for blogging or documentation and that I simply must try it.  I figured I’d sign up for the free trial and a week later… here I am, scribbling all over screen shots and sharing my lack of anything resembling artistic talent with the world.  So far… it does seem pretty useful.  Much better and easier editing of screen captures and it allows me to point things out a bit more readily. 

My question to all of you is… is there anything better out there that I should evaluate before I take the plunge? 

 

Here are many different thoughts/facts/preferences on aliasing.  Most of this applies to column aliasing.  Regardless of whether or not you like aliasing tables, you should still be aliasing columns.  If you do happen to be one of the people who is against table aliasing (some of you may be wondering "Who are these mythical people against table aliasing?", but trust me, they exist), keep in mind that three and four part names in the select clause is on the list of unsupported features in a future version of SQL Server as of SQL 2008.

 

Aaron Bertrand's ( Blog | Twitter ) recent alias posts

Inconsistent Table Aliasing:...

Read More »

Now that I have several posts on what you can do with a Tally table, I figured I'd share my favorite way to create one inline.  I still prefer to have a physical tally table (usually in a Utility database that can be accessed from anywhere and doesn't need to be created in each individual database) for permament code, but for times when you need one on the fly, this is my preferred method.  I can't really take the credit for this query, the base construct is based on something I've seen attributed to Itzik Ben-Gan.   I've modified it a bit and changed up the formatting to be the way I like it.  Anything over a few thousand rows I'd probably use a physical tally table for, but on small numbers you shouldn't see much of a performance hit with this script.

-- Tally Table CTE script (SQL 2005+ only) -- You can use this to create many different numbers of rows... for example: -- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows. -- The...

Read More »

The Monsoon

My trip to the meeting, short as it was, involved some of the worst driving conditions I've ever personally encountered.  It was coming down in sheets and I was doing 40mph on I-95.  Before you roll your eyes about how I'm one of "those guys", note that only 2 or 3 people passed me for the several miles that I drove that speed, and they were still going under 50.  It was bad.  If I didn't need to give a presentation at the meeting I'd probably have waited till later or skipped the evening.  Overall, I think the weather had a large impact on the attendance for this meeting(we only had 8 or 9 people), which is really a shame because it was a great presentation.  In any case , had a couple slices of Domino's Pizza ("New recipe" apparently involves melting a couple sticks of butter over the top of the pizza.), gave a quick presentation of all the upcoming SQL/Dev events that I knew about(after quickly adding the Orlando Code Camp...

Read More »

In case you missed David Pless in Orlando on Tuesday, you have another chance to see his presentation tonight (Thursday March 11, 2010 6:30 PM EST) at the SCSUG meeting (Brevard County, Fl).

David will be speaking about how to troubleshoot performance issues on SQL Server 2005 and SQL Server 2008.

"In this discussion I cover performance impact analysis using DMVs, SET options in SQL Server, and Profiler and perfmon analysis...[as well as]... SQLNexus, PAL (Performance Analysis of Logs), RML Utilities, SQLDiag, and other tools. Finally I discuss reading query plans graphically and what you can learn from reading the XML plans."  -- David Pless

The meeting is at the Space Coast Credit Union HQ...

Read More »

Technorati Tags: T-SQL,Scripts,Metadata

Someone asked a question in the forums the other day and I realized it would make a pretty decent blog post to explain the differences in functionality between these two and provide some code for working with them.

Differences

fn_get_sql returns the last *statement* executed, where as DBCC INPUTBUFFER returns the first statement in the batch.  See the example below for a better idea of what that means. fn_get_sql returns a TEXT field containing the statement that ran, DBCC INPUTBUFFER returns only a varchar(255).  This is important as you often won't get the full line here... and unfortunately there's not a lot you can do about that.  To my knowledge, DBCC INPUTBUFFER is still your only means of getting the first statement in the batch, however it returns an...

Read More »

If you read my last post, you may have noticed that my images now include boxes, balloon text and highlighting.  This is due to a friend insisting the he couldn’t live without Snagit for blogging or documentation and that I simply must try it.  I figured I’d sign up for the free trial and a week later… here I am, scribbling all over screen shots and sharing my lack of anything resembling artistic talent with the world.  So far… it does seem pretty useful.  Much better and easier editing of screen captures and it allows me to point things out a bit more readily. 

My question to all of you is… is there anything better out there that I should evaluate before I take the plunge? 

 

Here are many different thoughts/facts/preferences on aliasing.  Most of this applies to column aliasing.  Regardless of whether or not you like aliasing tables, you should still be aliasing columns.  If you do happen to be one of the people who is against table aliasing (some of you may be wondering "Who are these mythical people against table aliasing?", but trust me, they exist), keep in mind that three and four part names in the select clause is on the list of unsupported features in a future version of SQL Server as of SQL 2008.

 

Aaron Bertrand's ( Blog | Twitter ) recent alias posts

Inconsistent Table Aliasing:...

Read More »

Now that I have several posts on what you can do with a Tally table, I figured I'd share my favorite way to create one inline.  I still prefer to have a physical tally table (usually in a Utility database that can be accessed from anywhere and doesn't need to be created in each individual database) for permament code, but for times when you need one on the fly, this is my preferred method.  I can't really take the credit for this query, the base construct is based on something I've seen attributed to Itzik Ben-Gan.   I've modified it a bit and changed up the formatting to be the way I like it.  Anything over a few thousand rows I'd probably use a physical tally table for, but on small numbers you shouldn't see much of a performance hit with this script.

-- Tally Table CTE script (SQL 2005+ only) -- You can use this to create many different numbers of rows... for example: -- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows. -- The...

Read More »

The Monsoon

My trip to the meeting, short as it was, involved some of the worst driving conditions I've ever personally encountered.  It was coming down in sheets and I was doing 40mph on I-95.  Before you roll your eyes about how I'm one of "those guys", note that only 2 or 3 people passed me for the several miles that I drove that speed, and they were still going under 50.  It was bad.  If I didn't need to give a presentation at the meeting I'd probably have waited till later or skipped the evening.  Overall, I think the weather had a large impact on the attendance for this meeting(we only had 8 or 9 people), which is really a shame because it was a great presentation.  In any case , had a couple slices of Domino's Pizza ("New recipe" apparently involves melting a couple sticks of butter over the top of the pizza.), gave a quick presentation of all the upcoming SQL/Dev events that I knew about(after quickly adding the Orlando Code Camp...

Read More »

In case you missed David Pless in Orlando on Tuesday, you have another chance to see his presentation tonight (Thursday March 11, 2010 6:30 PM EST) at the SCSUG meeting (Brevard County, Fl).

David will be speaking about how to troubleshoot performance issues on SQL Server 2005 and SQL Server 2008.

"In this discussion I cover performance impact analysis using DMVs, SET options in SQL Server, and Profiler and perfmon analysis...[as well as]... SQLNexus, PAL (Performance Analysis of Logs), RML Utilities, SQLDiag, and other tools. Finally I discuss reading query plans graphically and what you can learn from reading the XML plans."  -- David Pless

The meeting is at the Space Coast Credit Union HQ...

Read More »