Integrating SQL Server 2014 constructs into the T-SQL canon

So I was going through the features in SQL Server 2014, seeing how and where they fit into the canon of SQL Server’s T-SQL. I’m going to add some of this for my advanced T-SQL Immersion Event at the end of September. SQL Server 2014 is supposed to be a release with no T-SQL changes at all. […]

Does everybody get that? (generalizing esoteric optimization techniques)

This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title.  Figured that I couldn't wrap up this series without mentioning T-SQL, as most of the "bring your own problem"-type problems have […]

New T-SQL 2012 scalar functions (all but 1) work in SQL Azure

Back when I was saw SQL Azure was producing an @@version string that mentioned version 11 (that's SQL Server 2012's major version number), I'd started looking at the version number every week or so. And looking for SQL Server 2012 functionality in SQL Azure. See the blog post "SQL Denali T-SQL features in SQL Azure […]

SQL Denali T-SQL features in SQL Azure now

Earlier this month I noticed that SQL Azure, with the latest upgrade has a version of 11…something (actually 11.0.1467.26, to be exact). And I wondered, aside from the long-touted new spatial library, if there were any Denali T-SQL enhancements that were now available. That is, what version of "version 11" is this? I did do […]

SQL Azure “version 11″ arrives on my server

I've been trying this out every day or so since I'd heard about the update (originally named the July 2011 Service Release), but I'd forgotten about it for about a week. So I don't exactly know when this happened on my SQL Azure server. But tonight, around midnight, SELECT @@version returns: Microsoft SQL Azure (RTM) […]

Window clause, range specification, and missing values

In the previous post, I insinuated that allowing LAG/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won't do that unless there's some logic that allows the offset that LAG/LEAD uses to be calculated on a row-by-row basis (i.e. LAG/LEAD uses an expression based on the window […]

Remember: LAG function in SQL Server Denali uses rows

You've heard my rant before "measure what you think you are measuring". If not, follow the link. Here's an example using the LAG function, new in SQL Server Denali, to measure sales trends. We'll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT  EmployeeKey as [Employee],         […]

Denali window clause followup question

So, quickly on the heels of the first window clause and last_value() question, came a followup: OK smartie, why does last_value work fine here? I didn't have to change from the default window. What gives? select SalesPersonID, SalesOrderID,  first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as FirstOrderForSalesPerson,  last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as […]

An FAQ about the window clause in SQL Server Denali

I've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ. Why does last_value not always "work right"? Take, as an example, the following query (against Adventureworks2008R2) select  AccountNumber,  SalesPersonID, SalesOrderID,  first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct, […]

What exactly does PERCENTILE_CONT do, anyhow?

I'm always leery when I hear people say "statistics show that…" followed by whatever their opinion is. Scientists do it. And your users probably do it too. I worked with a product called SAS once, on statistics for response time. Got some lovely reports and statistics, *from which other folks draw conclusions*. It's important to step […]

Composable DML and Composable Queries

In a previous blog entry a while ago, I wrote about writing a single SQL statement that did a SQL MERGE operation, used the OUTPUT clause to put out a rowset and directed the rowset into an INSERT statement. This happens in one statement without the need of explicitly defining a temporary table and using […]

Another use for SQL Server 2008 row constructors

One last SQL syntax post for the evening… We've all heard about SQL Server 2008 row constructors. They allow syntax like this to work: CREATE TABLE name_table (name varchar(20), age int); go INSERT INTO name_table VALUES ('Bob', 54), ('Mary', 30), ('Sam', 15), ('Buddy', 9); go But how about using them as a table source: SELECT […]

Using the OUTPUT clause results and INSERT-SELECT

SQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause, you can output column values to a table variable, a table, or return these values to the user. You can even use multiple OUTPUT clauses to push the values into both a table variable and a table from the same statement. […]

New SQL Server 2008 Date/Time-related types and ADO.NET

I've been looking at the mapping of the new Katmai date/time data types to .NET types, both from the point of view of SQL Server ADO.NET clients and of SQLCLR procedures/functions/triggers. There are a couple of things that "interesting", if I'm not misunderstanding something obvious. There are no new System.Data.SqlTypes to correspond to the new […]

Katmai: Using Table-Valued Parameters with ADO.NET

I've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, DbDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let's say that I want a TVP and a procedure for insert that […]

MERGE, JOINS, and determinism

SQL Server 2008 will contain an ANSI SQL standard MERGE statement with extensions. Listening to the webcast last Friday, there's a fairly straightforward way to describe how this works. Let's go back to first principals. Relational databases support two ways to do UPDATE and DELETE; positioned updates and searched updates. In a positioned update, you […]

ORDPATH, ORDPATH, everywhere

ORDPATH is a hierarchical labeling scheme used in the internal implementation of the XML data type in SQL Server 2005. It's meant to provide optimized representation of hierarchies, simplify insertion of nodes at arbitrary locations in a tree, and also provide document order. It's described in the academic whitepaper "ORDPATHs: Insert-Friendly XML Node Labels". In […]

SQL Server system data types implemented in .NET

SQL Server 2008 will contain, if my count is correct, 7 new data types. Note: none of these are in the current CTP. DATE – ANSI-compliant date data type TIME – ANSI-compliant time data type with variable precision DATETIMEOFFSET – timezone aware/preserved datetime DATETIME2 – like DATETIME, but with variable precision and large date range GEOMETRY […]

Table valued parameters in SQL Server 2008

The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while. Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire […]

Grouping sets and TABLIX – made for each other?

SQL Server 2008 will include GROUPING SETS; a (very) short explanation is that these allow the equivalent of multiple GROUP BY clauses in a single SQL statement. The result is a UNION ALL of the resultsets. SQL Server 2008 also contains/allows standard syntax for ROLLUP and CUBE, which have been in SQL Server for a while. […]

Does sp_dropuser “do the right thing” with schemas?

Here's another blog posting to answer a question from over a month ago. With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA […]

Just Add 50000

I've been doing my talk on try-catch in T-SQL for a while and whining about not being able to "rethrow" (via RAISERROR) a system error. Even went as far as writing a RegEx decoder so that I can copy the error into a user error message text and parse the text back on the client. Two […]

What’s that stuff good for, anyway?

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and […]

Seeing errors in callers, SQLCLR error 6522, and T-SQL TRY-CATCH

I've been going over how errors are raised in SQLCLR against the SQL Server 2005 RTM version. If you remember (search the blog on "6522" if you don't) how errors made their way from the managed to the unmanaged stack evolved throughout the beta. My goal in investigating this was to determine if you can […]

SQL Server 200x wish list – part 4, T-SQL and Engine

Transact-SQL has been around for a lot longer, the list will be a bit smaller. But the T-SQL enhancements in 2005 were numerous but, of course, left some folks wanting more. As always. More error handling improvements – TRY-CATCH is VERY nice. For next time, how about FINALLY and RETHROW? The ability to rethrow system […]

Hello again. and SQL Server 200x wish list part 1

It's just a few weeks until the launch of SQL Server 2005. I'm been hiding under a rock since returning from Hong Kong. TechEd there was a great time, biggest attendence they've ever had. Security and SQL Server 2005 were the hot topics. Yes, even after PDC in September. It's been over three years since […]

IsNull != IS NULL, the reprise and the solution

About a week or two ago, there was a LONG discussion on the SQLCLR beta newsgroup about the fact that the IsNull property that you use on CLR UDTs won't return TRUE or FALSE inside the server. It returns FALSE or NULL. Turns out that, although you use this property to *indicate* to the engine […]

What’s a truncation exception?

I commonly do a demo when teaching SQL Server 2005 where I write a SQLCLR UDF that's returns the string "Hello World". The define it, sans VS autodeploy, like this. CREATE FUNCTION somefunc() RETURNS NVARCHAR(4) AS EXTERNAL NAME MyAssembly.MyClass.MyFunction When invoked, it returns "Hell", silently truncating the string the CLR sent it. UNTIL Apr CTP. […]

Combined provider: transactions and the return of 6522

With April CTP came the new combined managed provider replaces System.Data.SqlServer with a new improved, works in-process or out, System.Data.SqlClient. I just call it “the combined provider” now. People that didn’t work on the betas will look at me funny when the product RTMs; “was there ever anything other than System.Data.SqlClient?”. Why yes, Virginia… With […]

New functions to use with try-catch

I'm still assimilating little tidbits of information about the Nov-Dec CTP build. Put this one in your pubs database in the CTP: create procedure deletejob (@id int) as begin try  delete jobs where job_id = @id end try begin catch  — you knew about these  print error_number()  print error_message()  print error_severity()  print error_state()  — these are […]

A First Look at Dec CTP

OK, I couldn't help myself. The Dec 2004 CTP-specific readme had quite a number of things that are new. There are many improvements in SSIS, for example. The “real“ readme and the known issues file (sqlki.chm) still have July dates on them. But there are always a few little things I have queued up to try with a […]

Intersect and Except….almost

There's a new Community Technology Preview of SQL Server 2005 available on MSDN for universal subscribers. I'm sure you've all heard of it by now, I was out of the country last week with limited bandwidth and just downloaded mine yesterday. I was meandering through the BOL, looking for interesting things, when it dawned on me […]

Error 6522:the reprise

About a month ago, I'd written about unhandled errors in .NET procedural code always causing error 6522 to be returned to the (T-SQL) caller (see "Error 6522 and You"). Because the "real" error (the one that caused the .NET code exception) is wrapped in a 6522 error, retrieving the value of @@error will always give […]

OLE DB/ODBC, SNAC, and the new “MAX” data types

After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. […]

Error 6522 and You

What's error 6522? It's the error that you always get from an unhandled exception in a SQLCLR stored procedure. If I write a SQLCLR error that throws an unhandled exception (let's write one on purpose): [SqlProcedure] public static void ErrorExecute() {     SqlCommand cmd = SqlContext.GetCommand();     // everyone knows Bob can't type     cmd.CommandText = […]