Archive for category: T-SQL

Transact-SQL related musings

TIE Fighters and sys.types: “There’s Too Many of Them!”

19 July, 2010 (08:39) | SQL, T-SQL | By: Mark V

As the Y-Wing pilot in Star Wars: Episode VI – Return of the Jedi proclaimed, sometimes there’s too many of them. He was referring to TIE Fighters during the assault on the second Death Star. I echoed the sentiment while querying the system catalog for a client’s database. I was building the foundation for a […]

A Hint on Using Indexed Views For Read Performance

13 April, 2010 (13:13) | SQL, T-SQL | By: Mark V

Some time ago, I was on a project in which we implemented a report model. The data was located in several different databases, none of which were very conducive to reporting. The consequence was that the views upon which we based our entities for the model required lots of joins and performed poorly. We decided […]

Finding Foreign Keys

11 March, 2010 (12:59) | SQL, T-SQL | By: Mark V

No, this is not a new initiative by United States Immigration and Customs Enforcement. I am talking databases. I recently found myself in a situation where I wanted to find all the tables that referenced a particular table via foreign key relationships. I had heard that relying on the built-in dependency tracking (right-clicking the object […]

Jinkies! Passing MultiValue SSRS Parameters To Stored Procedures

5 January, 2010 (13:08) | SQL, SSRS, T-SQL, XML | By: Mark V

SQL Server Reporting Services provides a great way to allows report users to select more than one value from the available values list in parameters, creating a multivalue parameter. SSRS dynamically creates a comma separated list of the values selected by the user to pass in to the data source. Great. It also dynamically creates […]

Cool Tool For Formatting Code

8 July, 2009 (11:11) | T-SQL | By: Mark V

Long time no blog. A colleague showed me this cool code formatting tool the other day. It is somewhat configurable and is right within my price range. I still end up making a few edits for my preference, but the vast majority of the formatting I care about can be done with the click of […]

Enforcing Uniqueness On A Nullable Column

19 May, 2009 (11:04) | T-SQL | By: Mark V

I recently found myself needing to enforce uniqueness on a column which was nullable. To make things interesting, there were already thousands of records for which the field was already NULL and would remain NULL. A unique index on the column was not possible since I had more than one NULL value. I therefore had […]

Using Medians For Quartiling When NTILE Is Too Arbitrary

26 March, 2009 (10:56) | T-SQL | By: Mark V

So, in a previous post on The Coolness of the T-SQL NTILE Function, I discuss that NTILE can perform quartiling (or any other –tiling not requiring a trip to Home Depot) for you. Very cool. However, there is something worth noting about this function that I discovered the hard way. I will continue with my […]

Using Dynamic Values in the PIVOT Operator

21 February, 2009 (10:20) | T-SQL | By: Mark V

I recently worked on a report for a client in which a date input parameter was used to determine the end dates of the previous four calendar quarters. Each time the report ran, it returned data for all four of those quarters. I ended up using PIVOT in the course of the stored procedure for […]

Things You Know Now…

20 February, 2009 (21:59) | T-SQL | By: Mark V

I was tagged by Jason Strate for cool blog chain. I guess I’m it now.    Get the Last Date in a Calendar Quarter I needed to accurately calculate the end dates for the four most recently completed calendar quarters prior to the date provided. I found this snippet online and thought it was a […]

The Coolness of the T-SQL NTILE Function

17 February, 2009 (21:47) | T-SQL | By: Mark V

I had need of calculating metrics across an industry and then returning the quartile in which each company scored. So, i wrote up a function that took in the MinValue, MaxValue, and CompanyMetricValue as inputs and returned the quartile for the Company. Great. Then, when asking  someone else about this using this new function, the […]