Search results matching tag 'TSQL'

sp_spaceused Alternative

Andrew Kelly — Fri, 17 May 2013 20:35:00 GMT

I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily occurrence. Now to be totally accurate I started with code that one of the SSMS reports uses to show data and index space usage. The code was not the prettiest so I did clean it up a little and modified it to better suite my needs but the core worked so I used it. I then added some additional queries to get other data that the original query didn’t have such as the file size and growth info. I also slightly modified one of the many variations of functions available out there that format numbers with commas for better readability. In this case it is simply called dbo.commas. Normally I have a database on every instance in which I keep custom procedures / functions etc. in which I can count on certain objects to exist so I can reference them in other objects. These examples are shown with no fully qualified path to the dbo.commas UDF so the UDF and the procedure would have to exist in each database or the one that you care to use it in. While that works you should consider doing one of two alternatives. Either add a utility database like I mentioned and put the two objects there or rename the procedure to have sp_xxx and put the objects in the master database and then mark them as system objects. That way you can call the procedure from the context of any database.

OK so enough about that lets see what the procedure does and how to use it. The procedure has two parameters @DBName and @ShowFileInfo. The @DBName parameter is pretty self explanatory and will dictate which database the results are for. The second parameter is really optional as it defaults to a value = 0. If the @ShowFileInfo parameter = 0 then only one result set will be returned which contains the relevant data for each table in the database. More on that in a bit. If a value of 1 is passed in then a 2nd result set is returned that contains information related to the size of the database, it’s files and growth. See further down for more details on both result sets. These are all acceptable ways to call the stored procedure:

EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks'
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 0
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 1
EXEC dbo.ListDBSpaceInfo 'Adventureworks'
EXEC dbo.ListDBSpaceInfo 'Adventureworks', 1

Here is what the results would look like if you ran it against the Adventureworks database.

The first result set lists each table showing the size in MB’s for the reserved, data and index space. The row counts and the Created and Modified dates. This is the information I find most useful the majority of the time. It orders the tables by Reserved MB but you may prefer by table name or something else. The code is easy enough to modify to sort however you like and you can even add an additional parameter to chose the sort by column. Again I use it this way most often so it is what it is.

The 2nd result set if you choose to return it will list certain information about the database and it’s files. It will show the current total size of all the data files which is pretty definitive by nature. However the next few columns are dependent on the max size and growth properties of each file. For instance if any of the data files are set to –1 which means unlimited growth the resultant column for Max Size will say Unlimited as will the Potential Free Space. If the files have a fixed size the total max size if then calculated and displayed along with that amount minus the current data usage to give you the potential free space. Basically that is how much free space the database can theoretically accommodate assuming there is enough space on the disks. The same holds true for the log file. These numbers are most useful if you have limits set but the information can give you a good idea of the overall condition of the data and log file usage.

Now I understand that none of this is information that wasn’t available to users before but I usually had to go to several different sources to get a comprehensive view like this. The purpose of this blog post was not to wow everyone with some rocket science code it was simply to share with others who may find it useful. I use it all the time so I figure there must be others who can take advantage of it as well and hopefully customize it to suite their needs like I have. Have fun everyone, here is the code:

Andy

Fun with Aggregates

Paul White — Sun, 11 Mar 2012 15:49:00 GMT

There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten.

One possible query to meet that specification is:

     SELECT

        p.Name

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

        p.ProductID = th.ProductID

    GROUP BY

        p.ProductID,

        p.Name

    HAVING

        COUNT_BIG(*) < 10;

That query correctly returns 23 rows (execution plan and data sample shown below):

The execution plan looks a bit different from the written form of the query: the base tables are accessed in reverse order, and the aggregation is performed before the join. The general idea is to read all rows from the history table, compute the count of rows grouped by ProductID, merge join the results to the Product table on ProductID, and finally filter to only return rows where the count is less than ten.

This ‘fully-optimized’ plan has an estimated cost of around 0.33 units. The reason for the quote marks there is that this plan is not quite as optimal as it could be – surely it would make sense to push the Filter down past the join too? To answer that, let’s look at some other ways to formulate this query. This being SQL, there are any number of ways to write logically-equivalent query specifications, so we’ll just look at a couple of interesting ones. The first query is an attempt to reverse-engineer T-SQL from the optimized query plan shown above. It joins the result of pre-aggregating the history table to the Product table before filtering:

    SELECT p.Name

    FROM

    (

        SELECT 

            th.ProductID, 

            cnt = COUNT_BIG(*) 

        FROM Production.TransactionHistory AS th 

        GROUP BY

            th.ProductID

    ) AS q1

    JOIN Production.Product AS p 

        ON p.ProductID = q1.ProductID

    WHERE

        q1.cnt < 10;

Perhaps a little surprisingly, we get a slightly different execution plan:

The results are the same (23 rows) but this time the Filter is pushed below the join! The optimizer chooses nested loops for the join, because the cardinality estimate for rows passing the Filter is a bit low (estimate 1 versus 23 actual), though you can force a merge join with a hint and the Filter still appears below the join. In yet another variation, the < 10 predicate can be ‘manually pushed’ by specifying it in a HAVING clause in the “q1” sub-query instead of in the WHERE clause as written above.

The reason this predicate can be pushed past the join in this query form, but not in the original formulation is simply an optimizer limitation – it does make efforts (primarily during the simplification phase) to encourage logically-equivalent query specifications to produce the same execution plan, but the implementation is not completely comprehensive.

Moving on to a second example, the following query specification results from phrasing the requirement as “list the products where there exists fewer than ten correlated rows in the history table”:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID 

        HAVING COUNT_BIG(*) < 10

    );

Unfortunately, this query produces an incorrect result (86 rows):

The problem is that it lists products with no history rows, though the reasons are interesting. The COUNT_BIG(*) in the EXISTS clause is a scalar aggregate (meaning there is no GROUP BY clause) and scalar aggregates always produce a value, even when the input is an empty set. In the case of the COUNT aggregate, the result of aggregating the empty set is zero (the other standard aggregates produce a NULL). To make the point really clear, let’s look at product 709, which happens to be one for which no history rows exist:

    -- Scalar aggregate

    SELECT COUNT_BIG(*)

    FROM Production.TransactionHistory AS th 

    WHERE th.ProductID = 709;

    -- Vector aggregate

    SELECT COUNT_BIG(*)

    FROM Production.TransactionHistory AS th 

    WHERE th.ProductID = 709

    GROUP BY th.ProductID;

The estimated execution plans for these two statements are almost identical:

You might expect the Stream Aggregate to have a Group By for the second statement, but this is not the case. The query includes an equality comparison to a constant value (709), so all qualified rows are guaranteed to have the same value for ProductID and the Group By is optimized away.

In fact there are some minor differences between the two plans (the first is auto-parameterized and qualifies for trivial plan, whereas the second is not auto-parameterized and requires cost-based optimization), but there is nothing to indicate that one is a scalar aggregate and the other is a vector aggregate. This is something I would like to see exposed in show plan so I suggested it on Connect. Anyway, the results of running the two queries show the difference at runtime:

The scalar aggregate (no GROUP BY) returns a result of zero, whereas the vector aggregate (with a GROUP BY clause) returns nothing at all. Returning to our EXISTS query, we could ‘fix’ it by changing the HAVING clause to reject rows where the scalar aggregate returns zero:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID 

        HAVING COUNT_BIG(*) BETWEEN 1 AND 9

    );

The query now returns the correct 23 rows:

Unfortunately, the execution plan is less efficient now – it has an estimated cost of 0.78 compared to 0.33 for the earlier plans. Let’s try adding a redundant GROUP BY instead of changing the HAVING clause:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY th.ProductID

        HAVING COUNT_BIG(*) < 10

    );

Not only do we now get correct results (23 rows), this is the execution plan:

I like to compare that plan to quantum physics: if you don’t find it shocking, you haven’t understood it properly :) The simple addition of a redundant GROUP BY has resulted in the EXISTS form of the query being transformed into exactly the same optimal plan we found earlier. What’s more, in SQL Server 2008 and later, we can replace the odd-looking GROUP BY with an explicit GROUP BY on the empty set:

    SELECT p.Name

    FROM Production.Product AS p

    WHERE EXISTS 

    (

        SELECT *

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

        HAVING COUNT_BIG(*) < 10

    );

I offer that as an alternative because some people find it more intuitive (and it perhaps has more geek value too). Whichever way you prefer, it’s rather satisfying to note that the result of the sub-query does not exist for a particular correlated value where a vector aggregate is used (the scalar COUNT aggregate always returns a value, even if zero, so it always ‘EXISTS’ regardless which ProductID is logically being evaluated).

The following query forms also produce the optimal plan and correct results, so long as a vector aggregate is used (you can probably find more equivalent query forms):

WHERE Clause

    SELECT

        p.Name

    FROM Production.Product AS p

    WHERE 

    (

        SELECT COUNT_BIG(*) 

        FROM Production.TransactionHistory AS th

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

    ) < 10;

APPLY

    SELECT p.Name

    FROM Production.Product AS p

    CROSS APPLY

    (

        SELECT NULL

        FROM Production.TransactionHistory AS th 

        WHERE th.ProductID = p.ProductID

        GROUP BY ()

        HAVING COUNT_BIG(*) < 10

    ) AS ca (dummy);

FROM Clause

    SELECT q1.Name 

    FROM

    (

        SELECT

            p.Name, 

            cnt = 

            (

                SELECT COUNT_BIG(*) 

                FROM Production.TransactionHistory AS th 

                WHERE th.ProductID = p.ProductID 

                GROUP BY ()

            )

        FROM Production.Product AS p

    ) AS q1 

    WHERE

        q1.cnt < 10;

This last example uses SUM(1) instead of COUNT and does not require a vector aggregate…you should be able to work out why :)

    SELECT q.Name 

    FROM

    (

        SELECT

            p.Name, 

            cnt = 

            (

                SELECT SUM(1)

                FROM Production.TransactionHistory AS th 

                WHERE th.ProductID = p.ProductID

            )

        FROM Production.Product AS p

    ) AS q

    WHERE q.cnt < 10;

The semantics of SQL aggregates are rather odd in places. It definitely pays to get to know the rules, and to be careful to check whether your queries are using scalar or vector aggregates. As we have seen, query plans do not show in which ‘mode’ an aggregate is running and getting it wrong can cause poor performance, wrong results, or both.

Twitter: @SQL_Kiwi
email: SQLkiwi@gmail.com

Is Distinct Aggregation Still Considered Harmful?

Paul White — Sat, 03 Dec 2011 18:30:00 GMT

Back in 2008, Marc Friedman of the SQL Server Query Processor Team wrote a blog entry entitled “Distinct Aggregation Considered Harmful”, in which he shows a way to work around the poor performance that often results simply from adding the keyword DISTINCT to an otherwise perfectly reasonable aggregate function in a query. This post is an update to that, presenting a query optimizer enhancement in SQL Server 2012 RC0 that reduces the need to perform the suggested rewrite manually. First, though, it makes sense for me to cover some broader aggregation topics in general.

Query Plan Options for DISTINCT

There are two main strategies to extract distinct values from a stream of rows. The first option is to keep track of unique values in a hash table. The second is to sort the incoming rows into groups and return one value from each group. SQL Server uses the Hash Match operator to implement the hash table option, and Stream Aggregate or Sort Distinct for the sorting option.

Hash Match Aggregate

The optimizer tends to prefer the Hash Match Aggregate on larger rowsets, with fewer groups, where there is no reason to produce a sorted output, and where the incoming rows are not sorted on the DISTINCT expression(s). Larger inputs favour hash matching because the algorithm generally scales well (although it does require a memory grant) and can make good use of parallelism. Fewer groups are better for hashing because it means fewer entries in the hash table, and the memory needed to store unique values is proportional to the number of groups (and the size of the group). Hash matching does not require or preserve the order of the incoming row stream. The query and plan below show a Hash Match Aggregate building a hash table on values in the Quantity column:

     SELECT DISTINCT 

        th.Quantity

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

The standard Hash Match Aggregate is blocking: it produces no output until it has processed its entire input stream. If we restrict the number of rows using TOP or SET ROWCOUNT the aggregate can operate in a streaming fashion, producing new unique values as soon as they are encountered. This streaming mode is known as Flow Distinct, and is activated depending on the estimated number of unique values in the stream. In the example above, the estimated number of output rows from the Hash Match Aggregate is 455 (based on the statistics on my test machine). Limiting the query to 455 or fewer rows using TOP or ROWCOUNT produces a Hash Match Aggregate running in Flow Distinct mode:

    SET ROWCOUNT 10

    SELECT DISTINCT 

        th.Quantity

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

    SET ROWCOUNT 0

This plan is interesting because it limits the output to 10 rows without including a specific TOP operator for that purpose. TOP is generally preferred to ROWCOUNT for the reasons set out in the Books Online topic “Limiting Result Sets by Using TOP and PERCENT”. Ignore the part that says “because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be used to generate a query plan for a query” – we have just seen how it can affect a query plan.

For completeness, this is the TOP query and execution plan:

    SELECT DISTINCT TOP (10)

        th.Quantity

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

Stream Aggregate

Performing a DISTINCT is the same as applying a GROUP BY on every expression in the SELECT list. A stream of rows sorted on these GROUP BY expressions has the useful property that all rows with the same GROUP BY values appear together, so all we need to do is output a row of GROUP BY keys each time a change occurs. If the required order can be obtained without an explicit sort, the query plan can use a Stream Aggregate directly:

    SELECT DISTINCT 

        th.ProductID 

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

This query plan shows an ordered scan of an index on the ProductID column, followed by a Stream Aggregate with a GROUP BY on the same column. The Stream Aggregate emits a new row each time a new group is encountered, similar to the Hash Match Aggregate running in Flow Distinct mode. This operator does not require a memory grant because it only needs to keep track of one set of GROUP BY expression values at a time.

Sort Distinct

The third alternative is to perform an explicit Sort followed by a Stream Aggregate. The optimizer can often collapse this combination into a single Sort operating in Sort Distinct mode:

    SELECT DISTINCT

        p.Color

    FROM Production.Product AS p 

    OPTION (RECOMPILE)

To see the original Sort and Stream Aggregate combination, we can temporarily disable the GbAggToSort optimizer rule that performs this transformation:

    DBCC RULEOFF('GbAggToSort')

    SELECT DISTINCT

        p.Color

    FROM Production.Product AS p 

    OPTION (RECOMPILE)

    DBCC RULEON('GbAggToSort')

The query plan now shows a regular (non-distinct) Sort followed by the Stream Aggregate:

Distinct Aggregates

The DISTINCT keyword is most commonly used with the COUNT and COUNT_BIG aggregate functions, though it can be specified with a variety of built-in and CLR aggregates. The interesting thing is that SQL Server always processes distinct aggregates by first performing a DISTINCT (using any of the three methods shown previously) and then applying the aggregate (for example COUNT) as a second step:

    SELECT 

        COUNT_BIG(DISTINCT p.Color) 

    FROM Production.Product AS p 

    OPTION (RECOMPILE)

One thing worth highlighting is that COUNT DISTINCT does not count NULLs. The previous query that listed the distinct colours from the Product table produced 10 rows (9 colours and one NULL), but the the COUNT DISTINCT query here returns the value 9. The query plan uses a Distinct Sort to perform the DISTINCT (which includes the NULL group) and then counts the groups using a Stream Aggregate. The aggregate expression uses COUNT(expression) rather than COUNT(*), which eliminates the NULL group produced by the Distinct Sort. This second example shows a Hash Match Aggregate performing the DISTINCT, followed by a Stream Aggregate to count the non-NULL groups:

    SELECT

        COUNT_BIG(DISTINCT th.Quantity)

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

So far, the counting step has always been performed by a Stream Aggregate. This is because it has been a scalar aggregate – an aggregate without a GROUP BY clause – and SQL Server always implements scalar aggregates using Stream Aggregate (there would be no point using hashing since there will only be one group by definition). If we add a GROUP BY clause, the final COUNT is no longer a single (scalar) result, so we can get a plan with two Hash Match Aggregates:

    SELECT

        th.ProductID,

        COUNT_BIG(DISTINCT th.Quantity)

    FROM Production.TransactionHistory AS th

    GROUP BY

        th.ProductID

    OPTION (RECOMPILE)

In this plan, the rightmost aggregate is performing the DISTINCT, and the leftmost one implements the COUNT (with GROUP BY).

Combining Aggregates

The decision to always implement distinct aggregates as separate DISTINCT and aggregate steps makes life easier for the optimizer. Separating the two operations makes it easier to plan and cost parallelism, partial aggregation, combining similar aggregates, moving aggregates around (for example pushing an aggregate below a join) and so on. On the downside, it creates problems for queries that include multiple distinct aggregates, or combine a single distinct aggregate with an ‘ordinary’ aggregate.

Multiple Aggregates

As you probably know, there is no problem combining multiple ordinary aggregations into a single operator:

    SELECT

        COUNT_BIG(*),

        MAX(th.ActualCost),

        STDEV(th.Quantity)

    FROM Production.TransactionHistory AS th

    GROUP BY

        th.TransactionType

The single Hash Match Aggregate operator computes the three separate aggregates concurrently on the incoming stream.

Multiple Distinct Aggregates

This next query does a similar thing, but with three DISTINCT aggregations on the stream:

    SELECT 

        COUNT_BIG(DISTINCT th.ProductID), 

        COUNT_BIG(DISTINCT th.TransactionDate), 

        COUNT_BIG(DISTINCT th.Quantity)

    FROM Production.TransactionHistory AS th

    OPTION (RECOMPILE)

It produces a more complex query plan:

The issue here is that once a DISTINCT has been performed on a stream, the stream no longer contains the columns necessary to perform the other DISTINCT operations. To work around this, the optimizer builds a plan that reads the source stream once per DISTINCT, computes the aggregates separately, and then combines the results using a cross join (which is safe because these are all scalar aggregates, guaranteed to produce one row each). The same basic pattern is employed if the query contains an outer GROUP BY clause, but instead of cross joins there will be inner joins on the GROUP BY columns.

More often than not, the source of rows will not be an unrestricted table scan. Where the source is complex (and therefore expensive to re-run for every distinct aggregate) or where a filter significantly reduces the number of rows, the query optimizer may choose to Eager Spool the source rows and replay them once per distinct aggregate:

    SELECT 

        COUNT_BIG(DISTINCT th.ProductID), 

        COUNT_BIG(DISTINCT th.TransactionDate), 

        COUNT_BIG(DISTINCT th.Quantity)

    FROM Production.TransactionHistory AS th

    WHERE

        th.ActualCost < $5

    GROUP BY

        th.TransactionType

    OPTION (RECOMPILE)

This is a plan shape that you are likely to encounter in the real world, since most queries will likely have a filtering condition or have a row source that is more complex than a simple scan of an index or table. For queries over larger tables than Adventure Works provides, this plan is likely to perform poorly, as you might expect. Aside from the obvious concerns, inserting rows into a spool has to be performed on a single thread (like all data modification operations in today’s SQL Server). Another limitation is that the spool does not support parallel scan for reading, so the optimizer is very unlikely to restart parallelism after the spool (or any of its replay streams). In queries that operate on large data sets, the parallelism implications of the spool plan can be the most important cause of poor performance.

Performing Multiple Distinct Aggregates Concurrently

If SQL Server were able to perform the whole COUNT DISTINCT aggregate in a single operator (instead of splitting the DISTINCT and COUNT into two steps) there would be no reason to split plans with spools as seen above. This could not be done with a Stream Aggregate since that operator requires the stream to be sorted on the DISTINCT expression, and it is not possible to sort a single stream in more than one way at the same time.

On the other hand, the Hash Match Aggregate does not require sorted input (it keeps the distinct values in a hash table, remember), so it should be possible to design a Hash Match Aggregate that computes COUNT DISTINCT in a single operation. We can test this idea with a User-Defined Aggregate (UDA) – SQL Server 2008 required:

    using System;

    using System.Collections.Generic;

    using System.Data.SqlTypes;

    using System.IO;

    using Microsoft.SqlServer.Server;

    [SqlUserDefinedAggregate

        (

        Format.UserDefined,

        IsInvariantToDuplicates = true,

        IsInvariantToNulls = true,

        IsInvariantToOrder = true,

        IsNullIfEmpty = false,

        MaxByteSize = -1

        )

    ]

    public struct CountDistinctInt : IBinarySerialize

    {

        // The hash table

        private Dictionary<int, object> dict;

        // Recreate the hash table for each new group

        public void Init()

        {

            dict = new Dictionary<int, object>();

        }

        // Ignore NULLs, store key values in the hash table

        public void Accumulate(SqlInt32 Data)

        {

            if (!Data.IsNull)

            {

                dict[Data.Value] = null;

            }

        }

        // Merge partial aggregates

        public void Merge(CountDistinctInt Group)

        {

            foreach (var item in Group.dict.Keys)

            {

                dict[item] = null;

            }

        }

        // Return the DISTINCT COUNT result

        public int Terminate()

        {

            return dict.Count;

        }

        // Required by SQL Server to serialize this object

        void IBinarySerialize.Write(BinaryWriter w)

        {

            w.Write(dict.Count);

            foreach (var item in dict.Keys)

            {

                w.Write(item);

            }

        }

        // Required by SQL Server to deserialize this object

        void IBinarySerialize.Read(BinaryReader r)

        {

            var recordCount = r.ReadInt32();

            dict = new Dictionary<int, object>(recordCount);

            for (var i = 0; i < recordCount; i++)

            {

                dict[r.ReadInt32()] = null;

            }

        }

    }

This UDA does nothing more than create a hash table, add (non-NULL) values to it, and return the count of values when aggregation is complete (the hash table takes care of duplicates). There is a little extra infrastructure code in there to allow SQL Server to serialize and reconstruct the hash table when needed (and merge partial aggregates) but the core of the function is just four lines of code. The example above only aggregates integer values, but it is easy to extend the idea to include other types. Anyway, armed with the integer and date time versions of the UDA, I now return to the multiple-distinct-count query that caused all the spooling, with COUNT DISTINCT replaced by UDA references:

    SELECT 

        dbo.CountDistinctInt(th.ProductID), 

        dbo.CountDistinctDateTime(th.TransactionDate), 

        dbo.CountDistinctInt(th.Quantity)

    FROM Production.TransactionHistory AS th

    WHERE

        th.ActualCost < $5

    GROUP BY

        th.TransactionType

    OPTION (RECOMPILE)

Instead of all the Eager Spools, we now get this query plan:

You may be surprised to see that the three distinct count aggregates are being performed by a Stream Aggregate; after all I just finished explaining why a Stream Aggregate could not possibly do what we want here. The thing is that all CLR UDAs are interfaced to query plans using the Stream Aggregate model. The fact that this UDA uses a hash table internally does not change that.

The Sort in this plan is there to ensure that groups of rows arrive at the ‘Stream Aggregate’ interface in the required GROUP BY order. This is so SQL Server knows when to call the Init() and Terminate() methods on our UDA. The COUNT DISTINCT aggregation that is happening inside the UDA for each group could not care less about ordering, of course. (In case you were wondering, yes, the UDA produces the same results as the original T-SQL code).

The point here is to demonstrate that multiple DISCOUNT COUNT operations can be performed within a single operator, not that UDAs are necessarily always a great way to do that in general. As far as performance is concerned, the original spool query runs in around 220ms, and the UDA settles down around 160ms which isn’t bad, all things considered.

We can also improve the performance of the T-SQL query by rewriting it to avoid the spools by scanning the source table three times (this executes in around 75ms). Part of the problem here (to go with the spool plan issues mentioned earlier) is that the optimizer assumes that all queries start executing with an empty data cache, and it does not account for the fact that the three scans complete sequentially, so the pages are extremely likely to be available from memory for the second and third scans. The rewrite and query plan are below.

    WITH 

        Stream AS 

        (

            SELECT 

                th.TransactionType, 

                th.ProductID, 

                th.TransactionDate, 

                th.Quantity 

            FROM Production.TransactionHistory AS th 

            WHERE 

                th.ActualCost < $5

        ),

        CountDistinctProduct AS

        (

        SELECT 

            TransactionType, 

            COUNT_BIG(DISTINCT ProductID) AS c

        FROM Stream 

        GROUP BY 

            TransactionType

        ),

        CountDistinctTransactionDate AS

        (

        SELECT 

            TransactionType, 

            COUNT_BIG(DISTINCT TransactionDate) AS c

        FROM Stream 

        GROUP BY 

            TransactionType

        ),

        CountDistinctQuantity AS

        (

        SELECT 

            TransactionType, 

            COUNT_BIG(DISTINCT Quantity) AS c

        FROM Stream 

        GROUP BY 

            TransactionType

        )

    SELECT

        p.c,

        d.c,

        q.c

    FROM CountDistinctProduct AS p

    JOIN CountDistinctTransactionDate AS d ON

        d.TransactionType = p.TransactionType

    JOIN CountDistinctQuantity AS q ON

        q.TransactionType = d.TransactionType

Combining a Single Distinct Aggregate with other Aggregates

Marc Friedman’s blog post presented a way to rewrite T-SQL queries that contain a single distinct aggregate and one or more non-distinct aggregates so as to avoid spools or reading the source of the rows more than once. The essence of the method is to aggregate first by the GROUP BY expressions in the query and the DISTINCT expressions in the aggregate, and then to apply some relational math to aggregate those partial aggregates to produce the final result. I encourage you to read the full post to see all the detail, but I will quickly work through an example here too:

    SELECT

        dp.EnglishProductName,

        SUM(frs.SalesAmount),

        COUNT_BIG(DISTINCT frs.SalesTerritoryKey)

    FROM dbo.FactResellerSales AS frs

    JOIN.dbo.DimProduct AS dp ON

        frs.ProductKey = dp.ProductKey

    GROUP BY

        dp.EnglishProductName

    OPTION (MAXDOP 1, RECOMPILE)

This query contains a regular SUM aggregate and a COUNT DISTINCT, so as expected the query optimizer produces a plan with an Eager Spool (click to enlarge):

To the left of the spool, the top branch performs the DISTINCT followed by the COUNT per group, and the spool replay on the lower branch of the plan computes the SUM per group. Finally, the two branches join on the common GROUP BY expression (EnglishProductName).

The rewrite starts by grouping on EnglishProductName (the GROUP BY expression) and SalesTerritoryKey (the DISTINCT expression) to produce a partial aggregate:

    SELECT 

        dp.EnglishProductName, 

        frs.SalesTerritoryKey, 

        SUM(frs.SalesAmount) AS ssa

    FROM dbo.DimProduct AS dp

    JOIN dbo.FactResellerSales AS frs ON 

        frs.ProductKey = dp.ProductKey

    GROUP BY 

        frs.SalesTerritoryKey, 

        dp.EnglishProductName

This query contains no distinct aggregates, so we get a plan with an ordinary join and and ordinary SUM aggregate:

To produce the results specified by the original query, we now need to SUM the partial SalesAmount sums, and COUNT (ignoring NULLs) the SalesTerritoryKey values. The final rewrite looks like this:

    WITH PartialAggregate AS

    (

        SELECT 

            dp.EnglishProductName, 

            frs.SalesTerritoryKey, 

            SUM(frs.SalesAmount) AS ssa

        FROM dbo.DimProduct AS dp

        JOIN dbo.FactResellerSales AS frs ON 

            frs.ProductKey = dp.ProductKey

        GROUP BY 

            frs.SalesTerritoryKey, 

            dp.EnglishProductName

    )

    SELECT

        pa.EnglishProductName, 

        SUM(pa.ssa),

        COUNT_BIG(pa.SalesTerritoryKey)

    FROM PartialAggregate AS pa

    GROUP BY pa.EnglishProductName

    OPTION (RECOMPILE)

The query plan adds another layer of aggregation on top of the partial aggregate plan above:

This plan avoids the Eager Spools seen earlier and improves execution time from 320ms to 95ms in this example. On larger sets, where parallelism becomes important and the spools might need to use physical tempdb storage, the gains are likely to be much larger.

New for SQL Server 2012 RC0

The good news is that SQL Server 2012 RC0 adds a new optimizer rule (ReduceForDistinctAggs) to perform this rewrite automatically on the original form of the query. This is particularly good because the rewrite, while ingenious, can be somewhat inconvenient to do in practice, and all too easy to get wrong (particularly ensuring that NULL partially-aggregated groups are handled correctly). The new optimizer rule was not available in CTP3, so you will need RC0 to see SQL Server turn this T-SQL:

    SELECT

        dp.EnglishProductName,

        SUM(frs.SalesAmount),

        COUNT_BIG(DISTINCT frs.SalesTerritoryKey)

    FROM dbo.FactResellerSales AS frs

    JOIN.dbo.DimProduct AS dp ON

        frs.ProductKey = dp.ProductKey

    GROUP BY

        dp.EnglishProductName

    OPTION (RECOMPILE)

…directly into this query plan:

This transformation is only valid for queries with a single distinct aggregate (and at least one non-distinct aggregate of course). If your query contains multiple distinct aggregates, it may not help you directly, though you may be able to refactor the T-SQL to take advantage.

If you want to see SQL Server 2012 RC0 produce the Eager Spool plan instead (created by the long-standing ExpandDistinctGbAgg rule), disable the new rule with:

    DBCC RULEOFF ('ReduceForDistinctAggs')

…and then recompile. Don’t forget to enable it again afterward using RULEON or by reconnecting to the server.

Even with the new rule enabled, you may still see the spool or multiple-scan plan from time to time. As always, the optimizer may explore many alternative plan forms and choose the one that looks cheapest. In some cases, the optimizer may still choose the spool plan, though it probably won’t be right to do so…

Thanks for reading; please consider voting for the following Connect item (to allow CLR UDA hash aggregates). Thank you.

http://connect.microsoft.com/SQLServer/feedback/details/629920/allow-option-hash-group-with-sqlclr-udas

Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

How Parallelism Works in SQL Server

Paul White — Sat, 05 Mar 2011 08:28:00 GMT

You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at:

http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/.

This introductory piece is not quite as deeply technical as my SQLblog posts tend to be, but I hope there be enough interesting material to make it worth a read.

Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Why can't we have column alias in ORDER BY?

TiborKaraszi — Sun, 03 Oct 2010 09:43:00 GMT

You probably think something like "What! Of course we can!". And so did I when I read the title for a post about this in a forum. Let us first establish that you can have a column alias in ORDER BY:

SELECT
 p.ProductID AS pid
,p.ListPrice AS pr
FROM Production.Product AS p
ORDER BY pr

The problem was that a table name was also in the ORDER BY clause. And this is when it becomes weird. The problem isn't the column alias, it is the table alias. Basically the desire is to write something like:

SELECT
p.ProductID AS pid
,p.ListPrice AS pr
FROM Production.Product AS p
ORDER BY p.pr

Above was apparently allowed in SQL Server ~~2005~~ 2000 (I never did it myself, however). (Update: Erland Sommarskog caught my error, this was allowed in 2000, not 2005.) In order to understand why above is backwards, we need to consider the logical query flow. The first part of a query which is executed is the FROM clause (logically, the optimizer can do as it pleases as long as we get the same result). In above example, I renamed the table (for the rest of the query) from "Production.Product" to "p". That means that we cannot refer to "Product" or "Production.Product" anymore in the query. However, SQL has imperfections and the topic of this blog is an attempt from MS to rectify such earlier imperfections (some of them), i.e., improving the product.

Continuing the logical query flow (WHERE, GROUP BY, HAVING), we towards the end find the SELECT clause. Here is where you can rename the columns (a.k.a. column alias) for the rest of the query. After the SELECT list, it is meaningless to talk about different tables (where the data originally comes from), all we have is the result of the query (so far). This is why, we can in the ORDER BY refer to a column alias (generally); ORDER BY comes logically after the SELECT list.

However, the original problem was that one also wanted to include a table alias, which shouldn't be allowed. So, why is it allowed, in the first place - consider it makes no sense from a logical standpoint? For that we need to look as somethining weird, which is allowed (both in T-SQL and ANSI SQL) and that can be somewhat useful:

SELECT
ProductID AS pid
,ListPrice AS pr
FROM Production.Product
ORDER BY ModifiedDate

We don't need to return something to the client app in order to sort over it. Sure, that can be useful, but from a logical perspective, this is spooky. We sort over something which doesn't exist! So, the imperfections of the SQL language is the reason things starts to break down. Basically, what we do is to sort over something as if we would have included it in the SELECT list. We are saying, in the ORDER BY clause something like

"Hey, dbms! Can you please pretend we included Product.ModifiedDate in the SELECT list so we can sort over it. Bur don't return it to the client app, I don't want to see that column."

Where it really breaks down is when we combine table and column alias. Let me repeat the problematic query:

SELECT
p.ProductID AS pid
,p.ListPrice AS pr
FROM Production.Product AS p
ORDER BY p.pr

The problem is that it is meaningless to specify a table alias here, the column does exist in the SELECT list. The only reason we can refer to something table-something at all, is to let the DBMS pretend we included something additional from the base tables, so we could sort over it. Such as (allowed):

SELECT
ProductID AS pid
,ListPrice AS pr
FROM Production.Product AS p
ORDER BY p.ModifiedDate

One could of course argue that below would be more logical (not allowed):

SELECT
ProductID AS pid
,ListPrice AS pr
FROM Production.Product AS p
ORDER BY Product.ModifiedDate

Arguing that one form of two above is more logical than the other is pretty irrelevant, houever. Both are weird constructs, and the language is what it is. Anyone feel like checking the ANSI SQL standard which of above two (first, second or both) are allowed/preferred? No me, I'm off to grab some breakfast!

Sorting, Row Goals, and the TOP 100 Problem

Paul White — Thu, 26 Aug 2010 13:34:00 GMT

When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause. To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause. Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned.

The ‘Top N’ Sort

Thinking about how this requirement might be implemented in an executable query plan, we might expect to see a Sort iterator followed by a Top. In reality, the query optimizer can often collapse these two related operations into a single iterator: a Sort iterator running in Top N Sort mode:

That’s an idea you might find familiar if you read my previous post on Row Goals and Grouping. In that entry, we saw how a Sort followed by a Stream Aggregate can sometimes be collapsed into a Sort iterator running in Sort Distinct mode.

The General Sorting Algorithm

SQL Server’s normal sorting algorithms are suited to a very wide range of ordering requirements. They work extremely well regardless of the data types involved, the size of data to be sorted, or the number of sort keys specified. They also make good use of available memory resources, and can spill to tempdb if required.

It is a common misconception that SQL Server will try to perform a sort entirely in memory if it can. In fact the algorithms used are much more complex: they aim to achieve a balance between memory usage, average response time, while maintaining high levels of resource concurrency. Memory is a precious resource in the server, so SQL Server may spill a sort to tempdb, even if sufficient main memory is available.

Sorting with a Row Goal

As sophisticated and highly-tuned as the main sorting algorithms are, they do assume that the full set of sorted rows is always required. When we use a TOP expression, the FAST N query hints or even an EXISTS clause, we indicate that we would prefer a plan (or part of a plan) optimized to produce the first ‘n’ rows quickly.

Say we have a million-row table, and we just want the first ten rows (in some particular order). It seems wasteful to fully sort all one million rows when we know that a maximum of ten rows will ultimately be needed. SQL Server addresses this problem by providing a second algorithm, optimized to return a small number of rows quickly. This second method must still examine all candidate rows of course, but it knows only to keep the ‘n’ highest-ranked rows as it goes.

It is important to realize that the two sorting approaches are complimentary. The second method only works well in a fairly narrow set of circumstances, and is particularly unsuitable for large sorts since it cannot spill to tempdb. This alternative algorithm can be used by a Sort iterator running in Top N Sort mode – normal Sort iterators always use the default sorting method. To be clear, the Top N Sort iterator may use either algorithm. Sadly, the query plan does not currently expose any information to identify which algorithm was used in a given query execution.

Achieving the Right Balance

As you might imagine, determining the optimal algorithm to use in a particular circumstance depends on some very complex considerations. You might further imagine that SQL Server performs a number of fairly hairy calculations to determine the optimal choice, and those calculations might depend on heuristics as well as statistical information. No doubt, you may reason, many extremely clever people have given the best years of their careers to find a great way to make this important choice.

You would be quite wrong. SQL Server always uses the alternative algorithm where the TOP 100 (or fewer) rows are requested. In all other cases, the normal sorting routines are used. It really is as simple as that.

In many cases, this simple heuristic works well, though it is not at all difficult to engineer situations where it comes unstuck.

Coming Unstuck - 1

This first example is based on a question originally asked on the MSDN forums in December 2009. At the end of this post, you will find links to the original forum thread (with important contributions from Brad Schulz among others), and subsequent blogs on the subject by Adam Haines and Fabiano Amorim.

As usual, we will need a test table and some sample data:

The table consists of a clustered primary key on the identity column RowNum, an integer SomeId column containing random numbers, and a CHAR(1000) padding column named SomeCode. Here’s the code necessary to create the above table and populate it with 50,000 rows:

IF      OBJECT_ID(N'dbo.TestData', N'U')
        IS NOT NULL
        DROP TABLE dbo.TestData;
GO
CREATE  TABLE dbo.TestData
        (
        RowNum      INTEGER IDENTITY NOT NULL,
        SomeId      INTEGER NOT NULL,
        SomeCode    CHAR(1000)
                    COLLATE LATIN1_GENERAL_CI_AS 
                    NOT NULL DEFAULT (REPLICATE('X', 1000)),
        
        CONSTRAINT  [PK dbo.TestData RowNum]
            PRIMARY KEY CLUSTERED (RowNum)
        );
GO
INSERT  TOP (50000)
        dbo.TestData WITH (TABLOCKX)
        (
        SomeId
        )
SELECT  SomeId = CHECKSUM(NEWID())
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO
UPDATE  STATISTICS dbo.TestData ([PK dbo.TestData RowNum]) WITH FULLSCAN;
CREATE  STATISTICS [stats dbo.TestData SomeId] ON dbo.TestData (SomeId) WITH FULLSCAN;
CREATE  STATISTICS [stats dbo.TestData SomeCode] ON dbo.TestData (SomeCode) WITH FULLSCAN;

We are asked to take a range of rows (based on RowNum) and return the first ‘n’ rows in SomeId sorted order. We’ll run two tests, which are identical except one specifies TOP 100, and other TOP 101:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO
-- Test 1
DECLARE @RowNum INTEGER,
        @SomeId INTEGER,
        @SomeCode CHAR(1000);
 
SELECT  TOP (100) 
        @RowNum = TD.RowNum,
        @SomeId = TD.SomeId,
        @SomeCode = TD.SomeCode
FROM    dbo.TestData TD
WHERE   TD.RowNum < 30000
ORDER   BY
        TD.SomeId ASC
OPTION  (MAXDOP 1);
GO
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO
-- Test 2
DECLARE @RowNum INTEGER,
        @SomeId INTEGER,
        @SomeCode CHAR(1000);
 
SELECT  TOP (101) 
        @RowNum = TD.RowNum,
        @SomeId = TD.SomeId,
        @SomeCode = TD.SomeCode
FROM    dbo.TestData TD
WHERE   TD.RowNum < 30000
ORDER   BY
        TD.SomeId ASC
OPTION  (MAXDOP 1);
GO

Both produce identical query plans, which use a range seek to find the rows, and a Top N Sort to perform the sort:

Despite this, we find that the two methods have very different performance:

In this particular test, the TOP(101) query is over six times slower than the TOP (100) version.

Analysis

One clear difference between the two runs is that the 101-row example spilled to tempdb (as indicated by the sort warning), whereas the TOP (100) version (using the alternative algorithm) did not. In fact, the TOP (101) example will always spill to tempdb with the sample data set, regardless of the amount of memory available to SQL Server.

This behaviour is by design. As I mentioned earlier, SQL Server does not always attempt to grant enough memory to a sort to ensure it occurs entirely in memory. The behaviour seen is a result of a careful compromise between the performance of one individual query, and the needs of the SQL Server instance as a whole.

The exact formula used to compute the memory grant for a sort operation is quite complex (and undocumented, so the details may change at any time). I’ll explore the precise formula currently employed by SQL Server in my next post.

It is interesting that if we change the test to use a CHAR(100) padding column instead of CHAR(1000), the spill to tempdb does not occur, and the performance difference between TOP (100) and TOP (101) disappears.

Workarounds

Since it seems that spilling to tempdb is the problem, we might think of artificially modifying the query to fool the server into giving it a larger memory grant. This is usually a misguided approach in my view.

From the actual execution plan, we can see that the intermediate result set flowing into the Sort iterator is 29MB (30,000 rows of 1015 bytes each). Just twenty concurrent executions of that query would require 580MB of (non-AWE) memory. If you enjoy debugging RESOURCE_SEMAPHORE waits and unpredictable query performance, larger memory grants might be the option for you :)

That aside, it turns out that SQL Server gives even the standard-algorithm query a memory grant of almost 11MB. This seems excessive considering the task at hand: we are only sorting on 30,000 integer SomeId column values, which ought to fit within 120KB or so. This implies that the normal sort algorithms require a memory grant at least as big as the entire data set (not just the sort keys) to avoid spilling to disk.

Adding Lookups

By excluding the large padding column from the sort, we can reduce the memory grant to just 2.75MB. We can modify our query to execute in two stages:

Find the top ‘n’ rows sorted by SomeId
Join back to the table to fetch the padding column values for just those rows (using the primary key values from step 1)

In the sample implementation below, I have used a common-table expression to find the 101 rows:

WITH    Top101
AS      (
        -- Top 101 rows, excluding the padding
        SELECT  TOP (101) 
                TD.RowNum,
                TD.SomeId
        FROM    dbo.TestData TD
        WHERE   TD.RowNum < 30000
        ORDER   BY
                TD.SomeId ASC
        )
SELECT  Top101.RowNum,
        Top101.SomeId,
        TD2.SomeCode
FROM    Top101
JOIN    -- Fetch the padding column for just 101 rows
        dbo.TestData TD2
        ON  TD2.RowNum = Top101.RowNum
ORDER   BY
        Top101.SomeId ASC
OPTION  (MAXDOP 1);

The query plan produced shows a Top N Sort of the key values, followed by a Nested Loops lookup to fetch the SomeCode column values:

There are many ways to rewrite the query following the same general idea (using APPLY, a subquery, or a ranking function like ROW_NUMBER). Brad Schultz suggested one particularly compact approach (though with slightly different semantics that are not important in this case):

SELECT  TOP (101) 
        TD.RowNum,
        TD.SomeId,
        (SELECT TD2.SomeCode FROM dbo.TestData TD2 WHERE TD2.RowNum = TD.RowNum)
FROM    dbo.TestData TD
WHERE   TD.RowNum < 30000
ORDER   BY
        TD.SomeId ASC
OPTION  (MAXDOP 1);

Avoiding the Sort

A further solution is to create an index to avoid the sort altogether:

CREATE  UNIQUE INDEX [IX dbo.TestData SomeId, RowNum]
ON      dbo.TestData (SomeId, RowNum);

This is a very compact index – just four bytes (plus overhead) to store each SomeId value. The original TOP (101) query now produces this sort-free plan:

Rows are acquired from the index in SomeId order, and the RowNum predicate is applied as a residual. The first 101 rows that match result in a Key Lookup to fetch the padding column, and we’re done.

Each of these solutions has its advantages and disadvantages, so each might be considered ‘best’ depending on the wider circumstances. For example, the index-based query is the only version which does not require a memory grant; if the design goal is predictable (rather than absolute best) performance, only the last version can guarantee that it will not wait on a memory grant.

Coming Unstuck – 2

It seems only fair to present a case where TOP (100) performs significantly worse than TOP (101).

It turns out that the alternative sort algorithm’s worst case performance occurs with long similar sort keys which are presented in exactly reversed order. To demonstrate this, we’ll use the CHAR(100) version of the test rig:

CREATE  TABLE dbo.TestData
        (
        RowNum      INTEGER IDENTITY NOT NULL,
        SomeId      INTEGER NOT NULL,
        SomeCode    CHAR(100)
                    COLLATE LATIN1_GENERAL_CI_AS 
                    NOT NULL DEFAULT (REPLICATE('X', 100)),
        
        CONSTRAINT  [PK dbo.TestData RowNum]
            PRIMARY KEY CLUSTERED (RowNum)
        );

The test queries are modified to reflect the change to CHAR(100), and to sort by SomeCode ascending, and then by RowNum descending. This provides the long keys needed, and ordering by RowNum descending helps ensure that the rows arrive at the sort in exactly reversed order:

SELECT  TOP (100) 
        @RowNum = TD.RowNum,
        @SomeId = TD.SomeId,
        @SomeCode = TD.SomeCode
FROM    dbo.TestData TD
WHERE   TD.RowNum < 30000
ORDER   BY
        TD.SomeCode ASC,
        TD.RowNum DESC
OPTION  (MAXDOP 1);

Again, both queries produce exactly the same query plan:

But the Profiler results are very different:

This time, the TOP (100) query is more than ten times slower than TOP (101).

As an aside, notice that the cost of key comparisons is crucial. I deliberately chose a compound key which differs only in the final few bytes, and which requires comparison using the expensive Windows collation rules.

Summary

SQL Server provides a secondary sort algorithm which is used by the Top N Sort iterator when 100 or fewer rows are requested. This alternate algorithm uses less memory than a full sort, but can perform poorly if the sort keys are large and similar, and if the keys happen to be presented to the Sort iterator in reverse sorted order. The nature of the algorithm makes it unsuitable for larger or more general sorting needs.

Naturally, absolute best sort performance is obtained when the sort can be performed entirely in main memory, regardless of which algorithm is used. However, large sorts (or many concurrent smaller sorts) can quickly consume all of a SQL Server’s available query execution memory.

System designers may choose to create indexes to avoid some sorting operations altogether. They may also choose to reduce the average row size of intermediate result sets that pass through a sort iterator to minimize memory grant requirements – though this may require careful query design, and may incur extra logical reads and higher CPU utilization on average. For many systems, good, predictable performance with high concurrency will be preferred over a wide range of best-case/worst-case execution times, and relatively poor resource concurrency.

Next time, I’ll look more deeply into memory grants, and specifically at the details of the current algorithms used by SQL Server to calculate those grants.

Paul White
Email: SQLkiwi@gmail.com
Twitter: @SQL_Kiwi

Be careful with constraints calling UDFs

TiborKaraszi — Thu, 17 Dec 2009 17:28:00 GMT

You might just not get what you think. I would be surprised if this hasn't been blogged already, but if so, it would be worth repeating. Here's the deal (example from a forum,, slightly re-worked):

I want the values in one column to be unique, assuming the value in another column is 1. Can I use an UDF for that?

On the surface, yes. You can write an UDF to wich you pass the value which should be conditionally unique and in that UDF check how many rows has this value AND othercolumn = 1. If more than 1 row, then function returns 0, else 1 (or something else to signal "OK" or "Not OK"). Now, you can call this function in a CHECK constraint. Something like CHECK(myFunction(uniqueCol) = 1). this will on the surface do its job, as long as you INSERT into the table. But if you update a row and only set the otherColumn for some row from 0 to 1, then the check constraint will not be checked. The optimizer is smart enough to understand that the update doesn't change anything that we refer to in our CHECK constraint, so why bother checking the constraint? End result here is that the constraint doesn't do what we want it to do. Use a trigger instead (or some other method). Here's a repro:

USE tempdb GO IF OBJECT_ID('t') IS NOT NULL DROP TABLE t IF OBJECT_ID('t_uq') IS NOT NULL DROP FUNCTION t_uq GO CREATE TABLE t(c0 INT, c1 NVARCHAR(50), c2 bit) GO CREATE FUNCTION t_uq(@c1 NVARCHAR(50)) RETURNS bit AS BEGIN DECLARE @ret bit IF (SELECT COUNT(*) FROM t WHERE c1 = @c1 AND c2 = 1) > 1 SET @ret = 0 ELSE SET @ret = 1 RETURN @ret END GO ALTER TABLE t ADD CONSTRAINT t_c CHECK(dbo.t_uq(c1) = 1) INSERT INTO t(c0, c1, c2) VALUES(1, 'a', 0) --OK INSERT INTO t(c0, c1, c2) VALUES(2, 'a', 0) --OK INSERT INTO t(c0, c1, c2) VALUES(3, 'b', 1) --OK INSERT INTO t(c0, c1, c2) VALUES(4, 'b', 1) --Fails --So far so good, but watch now: UPDATE t SET c2 = 1 WHERE c0 = 2 --No error, the constraint doesn't do its job! --We have invalid data: SELECT * FROM t

Cleaning up Un-Named Defaults

Andrew Kelly — Fri, 26 Jun 2009 16:07:00 GMT

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP & ADD’s which I figured others may find useful as well. The code would look much better if Windows Live Writer would keep the rich text when pasting into it but it doesn’t by default. There are some plug in’s to allow this but I have had little success so far in getting ones to install that actually do what I want. I will just have to deal with that later when I have more time.

The code below simply adds the necessary meta-data to the temp table that I need to Drop and Add the default constraints. I then run a select statement that dynamically builds each Drop or Add using that data and the resulting text can be used as a script to do the cleanup. It’s pretty simple stuff but may save some typing for anyone else wishing to cleanup messes like this that lazy developers :) and SSMS create. In my opinion ALL objects should be explicitly named at the time of initial creation and nothing should be left to be auto-generated by the tool or the engine. It will only lead to heartache down the road. And as always, test any code you find here first…

SET NOCOUNT ON ;

CREATE TABLE #DF ([Table Name] NVARCHAR(128), [Column Name] NVARCHAR(128),
[Default Name] NVARCHAR(128), [definition] NVARCHAR(200)) ;

INSERT INTO #DF ([Table Name], [Column Name], [Default Name], [definition] )
SELECT object_name(d.[parent_object_id]) AS [Table Name], c.[name] AS [Column Name],
d.[name] AS [Default Name], d.[definition]
FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
AND d.is_system_named = 1

-- Drop the existing constraints
SELECT 'DECLARE @DF_Name NVARCHAR(128) ;'

SELECT N'SELECT @DF_Name = d.[name] from sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[object_id] = object_id(''' + [Table Name] + N''') AND c.[name] = N''' + [Column Name] + N''' ;

IF @DF_Name IS NOT NULL
BEGIN
EXEC(''ALTER TABLE [dbo].[' + [Table Name] + N'] DROP CONSTRAINT ['' + @DF_Name + '']'') ;
END ;

'
FROM #DF
ORDER BY [Table Name];

--   Create the New constraints
SELECT N'IF NOT EXISTS(SELECT * FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
    ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[name] = ''' + [Column Name] + N''' AND OBJECT_NAME(d.[parent_object_id]) = ''' + [Table Name] + N''')
BEGIN
    ALTER TABLE [' + [Table Name] + N'] ADD CONSTRAINT [DF_' + [Table Name] + N'_'
            + [Column Name] + N'] DEFAULT ' + [definition] + N'FOR [' + [Column Name] + N'] ;
END ;

'
FROM #DF
ORDER BY [Table Name];

My datetime article updated for 2008

TiborKaraszi — Tue, 23 Sep 2008 14:10:00 GMT

Actually, the article has been out for about a month now, but I first solicited feedback on errors and typo's and then I forgot to mention the update here...

Anyhow, the article now also covers 2008 and the new date, time, datetime2 and datetimeoffset types. There are some interesting aspects of the new types which sets them apart from the old types. As always, I'll be happy if you let me know about errors or typos.

http://www.karaszi.com/SQLServer/info_datetime.asp

When a Function is indeed a Constant

Andrew Kelly — Sat, 01 Mar 2008 16:19:19 GMT

In my last blog post:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx

I mentioned that I ran across a situation in which GETDATE() used in a SELECT statement occasionally returned more than 1 value for the result set. That sparked quite a debate amongst SQL Server programmers who belonged to one of two camps in how they believed this really should behave. For instance if you run the following SQL statement:

SELECT GETDATE() AS [The Datetime] FROM sys.sysobjects

Should you expect all the rows to have the same DateTime value or would you expect a different value for each row? Now in this simple example it would be easy to say that the query was so fast that there was not enough elapsed time between rows to give a different DateTime value and you could very well be correct. But this next example calls a UDF that introduces some lag as a result of the WHILE loop and returns GETDATE(). If you run that example you will definately see differences in the return from the function. But what about the plain GETDATE()? Will it be the same value for all rows or similar to the UDF output? After all they are both spitting out GETDATE() right? Well try it and see.

USE tempdb
go
CREATE FUNCTION dbo.test$wait()

RETURNS DATETIME
AS
   BEGIN
       DECLARE @I INT SET @I = 1
       WHILE @I < 10000
         BEGIN
           SET @I = @I + 1
         END
       RETURN (GETDATE())
   END
GO
SELECT GETDATE() AS [Getdate],dbo.test$wait() AS [UDF]
    FROM master.sys.sysobjects

As I am sure most of you will see it turns out that GETDATE() or any other non-deterministic runtime constant scalar function will indeed act as a constant for the life of the query execution. What does that mean exactly? Well in a nutshell it means these functions will be evaluated once at the beginning of the query execution and that value will be used for all resulting rows. You will always get the same value and if you don't you have a bug. This behavior was confirmed today by one of the SQL Server developers who maintains this code and guarantees this behavior.

While this may not be news to many of you (I was always under the impression this was the intended behavior) but it is to many others. There has been a lot of code written over the years that expects functions like this to return a potentially different value for each row in a single Select statement. That assumption is wrong and I wanted to get the word out there in order to minimize any future coding errors in this regard. As you can see in my original blog post that there is at least 1 older revision of SQL Server in which this was not the case, but don't let that fool you into thinking that is the intended behavior. Keep this behavior in mind as you code along in the future.

Search results matching tag 'TSQL'

sp_spaceused Alternative

Fun with Aggregates

WHERE Clause

APPLY

FROM Clause

Is Distinct Aggregation Still Considered Harmful?

Query Plan Options for DISTINCT

Hash Match Aggregate

Stream Aggregate

Sort Distinct

Distinct Aggregates

Combining Aggregates

Multiple Aggregates

Multiple Distinct Aggregates

Performing Multiple Distinct Aggregates Concurrently

Combining a Single Distinct Aggregate with other Aggregates

New for SQL Server 2012 RC0

How Parallelism Works in SQL Server

Why can't we have column alias in ORDER BY?

Sorting, Row Goals, and the TOP 100 Problem

The ‘Top N’ Sort

The General Sorting Algorithm

Sorting with a Row Goal

Achieving the Right Balance

Coming Unstuck - 1

Analysis

Workarounds

Adding Lookups

Avoiding the Sort

Coming Unstuck – 2

Summary

Related Reading

Be careful with constraints calling UDFs

Cleaning up Un-Named Defaults

My datetime article updated for 2008

When a Function is indeed a Constant