Dimension Properties in SSAS 2008

SSAS Dimension Attribute Properties : Part 2

Yesterday, I was reading through some articles and happened to glance through a very memorable quote by  Berthold Auerbach – “ The little dissatisfaction which every artist feels at the completion of a work forms the germ of a new work “. That was when I realized I haven’t yet completed my dimension attribute article and so here I am with the second part.

In SSAS Dimension Attribute Properties : Part 1, we reviewed the Advanced and Basic properties of dimension attributes. This article would be continuing with the Misc, Parent-Child and Source properties.

The properties are explained below:-

MISC

1) AttributeHierarchyOrdered : This particular property specifies whether the members of the attribute are ordered or not. The values of this property can just be True or false. If the order of the members do not matter, setting this property to false for attributes where the attribute hierarchy is enabled or high cardinality attributes can significantly increase the processing performance.

2) GroupingBehavior :  This property is used to give a hint to the client application whether to encourage or discourage users to group on this attribute and does not affect any of the structures on disk. The values are EncurageGrouping and DiscourageGrouping.

3) InstanceSelection : This property also is used to give a hint to the client application’s UI on the recommended means of selection of a member from the attribute. The options available are

• None - (Default) no selection used.
• DropDown - Appropriate for situations where the number of items is small enough to display within a dropdown list.
• List - Appropriate for situations where the number of items is too large for a dropdown list, but not large enough to require filtering.
• Filtered List - Most useful in scenarios where the number of items is large enough to require users to filter the items to be displayed.
• Mandatory Filter - Appropriate for situations where the number of items is so large that the display must always be filtered.

4) MemberNamesUnique : This property indicates whether the member names are unique across the attribute hierarchy and this property affects the way member unique names are generated. The available options are True or False.

Parent-Child

1) MembersWithData : In a parent-child hierarchy, some of the non-leaf members may also have data associated with them (unlike normal hierarchies, where the non-leaf members have a value equal to the sum of it’s leaf values). These members are called data members and are present only for parent-child hierarchies. This particular property is used to set the visibility of the data members in parent-child hierarchies and the available options are NonLeafDataHidden and NonLeafDataVisible. This MSDN article - Working with Attributes in Parent-Child Hierarchies does a very nice job of explaining this property with an example.

2) MembersWithDataCaption : This particular property is used as a naming template for the system generated data members. For eg, if we have the MembersWithData property set to NonLeafDataVisible, then a leaf member representation of the data member is added. For eg, if Jason is a data member, with Thomas and Tom as his leaf members, then there would be an additional Jason added as a leaf member. Now to differentiate between the leaf member and the data member (in this case, both are Jason) would be difficult and hence we can use a template like *(leaf member) as the value of this property. The asterisk symbol is a placeholder for the original name. So our example would become Jason for the data member and Jason(leaf member) for the leaf member.

3) NamingTemplate : This property specifies how levels in a particular parent-child hierarchy would be named. Click the ellipsis button (..) in this property’s cell and then you should be able to view a popup window as shown below:-

You can specify a name for the level by clicking on the Name column of the second row and entering for eg, Employee *. This will ensure that instead of Level 02, Level 03, etc., you will be getting Employee 02, employee 03 and so on. For more details, refer to the MSDN article - Defining Parent Attribute Properties in a Parent-Child Hierarchy.

4) RootMemberIf : This property is used to specify the criteria by which we can identify the members of the highest level (excluding the ALL level). Again quoting from an article of William Pearson -

“ The four selection options include the following:

• ParentIsBlankSelfOrMissing - (Default) Only members that meet one or more of the conditions described for ParentIsBlank, ParentIsSelf, or ParentIsMissing are treated as root members.
• ParentIsBlank - Only members with a null, a zero, or an empty string in the key column or columns are treated as root members.
• ParentIsSelf - Only members with themselves as parents are treated as root members.
• ParentIsMissing - Only members with parents that cannot be found are treated as root members.

The behavior of the RootMemberIf property in determining how the root or topmost members of a parent-child hierarchy are identified, is, therefore, dependent upon which of the selections above is made. The default, as noted above, is ParentIsBlankSelfOrMissing. ”

5) UnaryOperatorColumn : We can control how members on a hierarchy (usually parent/child, but not necessarily) aggregate up to their parents by defining unary operators. To do this, you must create a new column in your dimension table to hold the unary operator values and then set the attribute's UnaryOperatorColumn property to point to it. This property specifies the column which holds the unary operator. You will find a very good example under the Unary Operators and Weights heading of this article - Measures and Measure Groups in Microsoft Analysis Services: Part 1. The values are (none) and (new) for this property. On clicking New, a dialog box opens which will prompt us to select the binding type, source table and the source column.

Source

1) CustomRollupColumn : Unary operators do not give enough flexibility for rollup, and in such cases, we can write our own rollup formulas as MDX expressions. This property is used to specify a column which will contain the custom rollup formula. A valid expression will ensure that the aggregation logic defined in the AggregateFunction property of the measure would be overridden for this attribute.

2) CustomRollupPropertiesColumn : This property is used to contain the properties of a custom rollup column. Refer Custom Member Formulas heading of this article - Measures and Measure Groups in Microsoft Analysis Services: Part 1 to learn more about the above two properties.

3) KeyColumns : This property contains the column/columns that constitute the key for the attribute, which is the column in the underlying relational table in the data source view to which the attribute is bound. If the NameColumn property is not defined, the value of KeyColumns property would be used to display the attribute members.

4) NameColumn : In most of the cases, the key of the attribute would be a integer value, and this would not make any sense to the user who is viewing the attribute members. For this, we can specify a column in this property which will have the user friendly name of the attribute member.

5) ValueColumn : This property identifies the column that provides the value of the attribute. If the NameColumn element of the attribute is specified, the same DataItem values are used as default values for the ValueColumn element. If the NameColumn element of the attribute is not specified and the KeyColumns collection of the attribute contains a single KeyColumn element representing a key column with a string data type, the same DataItem values are used as default values for the ValueColumn element.

In KeyColumns and NameColumn property, there are additional properties which can be got on expanding the plus symbol on the left. This article gives a pretty good overview on them.

Procedures and Functions Difference

UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

• A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn't have to.
• You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.
• A UDF can't use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
• A UDF can't change server environment variables; a stored procedure can.
• A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you've used proper error handling code.

Both UDFs and stored procedures can perform well, depending on how you write the code. To determine whether a UDF or a stored procedure would yield the best performance in a particular implementation, you should do performance testing.

HAVING vs. WHERE
You typically use the T-SQL HAVING clause along with the GROUP BY clause to search or sort based on a certain condition. But when you don't use GROUP BY, the HAVING clause acts like a WHERE clause to filter the results that a query should return.

You can use the WHERE clause in SELECT, DELETE, and UPDATE statements, but you can use HAVING only in a SELECT statement. However, HAVING can contain an aggregate function, such as COUNT(), whereas WHERE can't.

The following two queries illustrate the WHERE and HAVING clauses:

USE AdventureWorks
GO
--Return records that have an
--OrderQty greater than 20.
SELECT SalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty > 20
GO
--Return records that have an
--OrderQty greater than 20 and a
--total SalesOrderID greater
--than 9.
SELECT COUNT(SalesOrderID)
TotalSalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty > 20
GROUP BY OrderQty
HAVING COUNT(SalesOrderID)
> 9
GO

The first query uses the WHERE clause to return all records that have an OrderQty greater than 20. The second query then uses the HAVING clause with the COUNT() function to further filter those results, returning only records that also have a total SalesOrderID greater than 9.

RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (
ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (
ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (
ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (
ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;

Built-in Functions - System Functions

CASE Function

CASE is typically classified as a system function; however, it could also be considered as a statement. There are two general uses of the CASE function. The first one is used to replace occurrences of one value with other values, as specified by the programmer. Syntax for this flavor of CASE is as follows:

1.SELECT column_name = CASE WHEN column_name = 'a' THEN 'b' ELSE 'c' END

For instance, the following query attempts to specify the salary level for each job title within Adventure Works DW database:

01.SELECT DISTINCT TITLE,

02.SALARYRANGE = CASE

03.WHEN TITLE LIKE 'Chief%' THEN 'unlimited'

04.WHEN TITLE LIKE '%manager%' THEN '100K to 250K'

05.WHEN TITLE LIKE '%assistant%' THEN '20K to 40K'

06.WHEN TITLE LIKE '%supervisor%' THEN '50K to 65K'

07.WHEN TITLE LIKE '%technician%' THEN '30K to 60K'

08.WHEN TITLE LIKE 'vice president%' THEN '250K to 500K'

09.ELSE 'unknown'

10.END

11.FROM   DIMEMPLOYEE

Results (abbreviated):

01.Title                                              SalaryRange 

02.-------------------------------------------------- ------------ 

03.Accountant                                         unknown 

04.Accounts Manager                                   100K to 250K 

05.Assistant to the Chief Financial Officer           20K to 40K 

06.Buyer                                              unknown 

07.Chief Executive Officer                            unlimited 

08.Chief Financial Officer                            unlimited 

09.Document Control Assistant                         20K to 40K 

10.Document Control Manager                           100K to 250K 

11.Engineering Manager                                100K to 250K 

The other variation of CASE, which is sometimes referred to as the searched CASE, evaluates a Boolean expression and returns different values accordingly. For instance, we could use the searched CASE to categorize the top internet customers within Adventure Works DW database as follows:

1.SELECT CAST(FirstName + ', ' + LastName AS VARCHAR(35)) AS FullName,  CustomerCategory =   CASE WHEN SUM(SalesAmount) < 12000 THEN 'SILVER'     WHEN SUM(SalesAmount) BETWEEN 12000 AND 13250 THEN 'GOLD'     WHEN SUM(SalesAmount) BETWEEN 13250 AND 15000 THEN 'PLATINUM'   ELSE 'CREAM OF THE CROP'   END,  SUM(SalesAMount) AS TotalOrders  FROM dimCustomer a INNER JOIN FactInternetSales b  ON a.CustomerKey = b.CustomerKey  GROUP BY FirstName + ', ' + LastName  HAVING SUM(SalesAmount) >=11000  ORDER BY 3 DESC

Results:

01.FullName                            CustomerCategory  TotalOrders 

02.----------------------------------- ----------------- --------------------- 

03.Jordan, Turner                      CREAM OF THE CROP 15999.0996 

04.Willie, Xu                          PLATINUM          13490.0596 

05.Nichole, Nara                       PLATINUM          13295.38 

06.Kaitlyn, Henderson                  PLATINUM          13294.27 

07.Margaret, He                        PLATINUM          13269.27 

08.Randall, Dominguez                  PLATINUM          13265.99 

09.Adriana, Gonzalez                   GOLD              13242.70 

10.Rosa, Hu                            GOLD              13215.65 

11.Brandi, Gill                        GOLD              13195.64 

12.Brad, She                           GOLD              13173.19 

13.Francisco, Sara                     GOLD              13164.64 

14.Maurice, Shan                       GOLD              12909.6682 

15.Janet, Munoz                        GOLD              12489.1696 

16.Lisa, Cai                           SILVER            11469.1882 

17.Franklin, Xu                        SILVER            11284.9707 

18.Lacey, Zheng                        SILVER            11248.4582 

19.Larry, Munoz                        SILVER            11068.0082

COALESCE Function

The COALESCE function returns the first value from a supplied list that is not NULL. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression. The syntax is:

1.COALESCE(expression1, expression2, expressionN)

All expressions must have compatible data types. For instance, you can't coalesce DATETIME and INTEGER, but you could coalesce VARCHAR(20) and CHAR(20).

For example, the following query will examine the parent account key column for each account within DimAccount table; if the value is NULL the query will return "none" as the parent account:

1.SELECT a.AccountDescription,   COALESCE(b.AccountDescription, 'None') AS ParentAccount   FROM dimAccount a  LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results (abbreviated):

01.AccountDescription                                 ParentAccount 

02.-------------------------------------------------- -------------------------------------- 

03.Balance Sheet                                      None 

04.Assets                                             Balance Sheet

05.Current Assets                                     Assets 

06.Cash                                               Current Assets 

07.Receivables                                        Current Assets 

08.Trade Receivables                                  Receivables 

09.Other Receivables                                  Receivables 

10.Net Income                                         None 

11.Operating Profit                                   Net Income 

12.Gross Margin                                       Operating Profit 

13.Net Sales                                          Gross Margin 

14.Gross Sales                                        Net Sales 

15.Intercompany Sales                                 Gross Sales 

16.Returns and Adjustments                            Net Sales 

17.Discounts                                          Net Sales 

18.Total Cost of Sales                                Gross Margin 

19.Statistical Accounts                               None 

20.Headcount                                          Statistical Accounts 

21.Current Installments of Long-term Debt             Current Liabilities 

22.Trade Sales                                        Gross Sales

COALESCE function is equivalent to the CASE function if it checks each expression for NULL values and returns the non-null expression, as in:

1.SELECT coalesce_equivalent =   CASE    WHEN (expression1 IS NOT NULL) THEN expression1    WHEN (expression2 IS NOT NULL) THEN expression2   ELSE NULL   END

ISNULL Function

The ISNULL deterministic function is similar to COALESCE, but accepts only two parameters. The first parameter will be checked, and if NULL value is found, it will be replaced with the second parameter. Furthermore, ISNULL requires that both parameters have the same (not just compatible) data type. For example, we can return 'none' if the parent account of the given account is NULL:

1.SELECT a.AccountDescription,   ISNULL(b.AccountDescription, 'None') AS ParentAccount   FROM dimAccount a  LEFT JOIN dimAccount b ON a.ParentAccountKey = b.AccountKey

Results would be identical to those results obtained with the COALESCE function.

NULLIF Function

The NULLIF deterministic function returns a NULL value if the two parameters it accepts are equivalent. NULLIF can be thought of as an opposite of ISNULL; for instance, we could substitute a NULL for number of employees if we find that number of employees for a particular reseller is 10:

1.SELECT  NumberEmployees,  NULLIF(NumberEmployees, 10) AS manipulated_number_of_employees  FROM dimReseller  WHERE NumberEmployees IN (10, 11)

Results (abbreviated):

01.NumberEmployees manipulated_number_of_employees 

02.--------------- ------------------------------- 

03.10              NULL 

04.11              11 

05.11              11 

06.10              NULL 

07.10              NULL 

08.10              NULL 

09.11              11 

10.11              11 

11.10              NULL

GETANSINULL Function

The GETANSINULL function provides a quick way of checking whether column nullability is determined according to the ANSI 92 standard. The function returns 1 if ANSI null standard is used for column nullability, otherwise zero is returned. This function takes a single argument of database name. If database name isn't specified the setting is returned for the current database. For example:

1.SELECT GETANSINULL('AdventureWorksDW')

Results:

1.------ 

2.1

CAST and CONVERT Function

The CAST and CONVERT functions are very similar: Both translate a value from one data type to another. Although their performance is also similar, their syntax and potential usage is slightly different. The syntax is as follows:

1.CAST(expression AS new_data_type)    CONVERT(new_data_type, expression, [style])

The expression must already have a data type that is translatable into the new_data_type. For instance, you can't convert an alphanumeric string into an integer.

Note: CONVERT has an optional parameter: style. This parameter is allowed only for cases when working with date and time values. SQL Server supports numerous formats for presenting date and time values; the style parameter is used to specify such format.

For example, suppose you want to retrieve dates from the dimTime table without returning the time portion. Either CAST or CONVERT function can be used as follows:

1.SELECT  TOP 1 CAST(FullDateAlternateKey AS VARCHAR(12))  FROM DimTime   

2.SELECT  TOP 1 CONVERT(VARCHAR(12), FullDateAlternateKey, 109)  FROM DimTime

Both return the same results:

1.------------ 

2.Jul  1 2001

If you needed to return a date value in which month, day, and year are separated by dashes you could use the CONVERT function with the style 110, as follows:

1.SELECT   TOP 1  CONVERT(VARCHAR, FullDateAlternateKey, 110)  FROM DimTime

Results:

1.------------------------------ 

2.07-01-2001

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

The @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() functions deal with IDENTITIY values or globally unique identifiers. SQL Server 2005 also supports the NEWSEQUENTIALID() function for default constraints of columns with the UNIQUEIDENTIFIER data type.

The NEWID() function could be used if you want to provide a default value for a column with the UNIQUEIDENTIFIER data type. This function can also be used to generate a new GUID in Transact-SQL. Columns with UNIQUEIDENTIFIER data type are often used as primary keys. Values generated by NEWID() function are impossible to predict and are not ordered, which can cause performance issues. For example, the following script creates a table and populates using NEWID() function for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are NOT sequential:

1.CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()  )   

2.INSERT test DEFAULT VALUES 

3.INSERT test DEFAULT VALUES 

4.INSERT test DEFAULT VALUES 

5.INSERT test DEFAULT VALUES   

6.SELECT * FROM test

Results:

1.test_column 

2.------------------------------------ 

3.B7C4B585-7DDC-41FA-8AED-8EDD9F8BA483 

4.6E88893F-D913-43E0-9503-7A7BE0B9FAF2 

5.352E9644-07CC-4324-9FCE-AD61DAD4001A 

6.848F80F9-4E20-4969-A331-436E669819A8

Fortunately SQL Server 2005 supports the NEWSEQUENTIALID() function that generates a sequential GUIDS which tend to perform better than unordered GUID. The NEWSEQUENTIALID() function can only be used in a DEFAULT constraint expression. Since values generated by NEWSEQUENTIALID() are ordered one can predict the value that will be generated next; therefore this function shouldn't be exposed to the users if data security is of concern. For example, the following script creates a table and populates it with sequential values for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are sequential:

1.CREATE TABLE test (  test_column UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()  )   

2.INSERT test DEFAULT VALUES 

3.INSERT test DEFAULT VALUES 

4.INSERT test DEFAULT VALUES 

5.INSERT test DEFAULT VALUES   

6.SELECT * FROM test

Results:

1.test_column 

2.------------------------------------ 

3.00700F04-4AB7-DA11-804B-006073E94311 

4.01700F04-4AB7-DA11-804B-006073E94311 

5.02700F04-4AB7-DA11-804B-006073E94311 

6.03700F04-4AB7-DA11-804B-006073E94311

The IDENTITY function has limited use; in rare cases, when you use SELECT INTO syntax you can supply identity values for the newly created table using the IDENTITY function. For instance, suppose we want to add an identity column to the sales table (within a temporary table). We could use the following statement to copy all rows from sales table into #new_sales and add an identity column, all in one shot:

1.SELECT IDENTITY(INT, 1,1) AS sales_key, *  INTO #new_sales  FROM sales

The other three IDENTITY-related functions deserve more attention. You will often need to populate multiple related tables, perhaps within a single transaction. For instance, you could be populating the order and order_details tables in one transaction. If the order table has an identity column, you'll have to look up the identity value just inserted into the order table before you can add a related record in order_details. The @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions help you look up the last identity value inserted, but their behavior is slightly different from each other, as follows:

• @@IDENTITY returns the last IDENTITY value inserted on the current connection. Suppose that you have an INSERT trigger on the order table that populates the audit_trail table, which also has an IDENTITY column. In such a case, the @@IDENTITY function will return the last identity value inserted, which would be the identity inserted in the audit_trail table instead of the identity value added to the order table. Therefore, if you try populating order_details with a value returned by the @@IDENTITY function, your data integrity will be compromised.
   
• The IDENT_CURRENT() function accepts a table name as the parameter and returns the last identity value generated in that table by any connection. If you were trying to populate order_details with the last identity value inserted into the order table, then IDENT_CURRENT('order') could work if you were the only user of the system; however, another user might have added a row to the order table a few milliseconds after you added the row to the same table. Therefore, using IDENT_CURRENT() in a multi-user system might also compromise your data integrity.
   
• The SCOPE_IDENTITY() function takes no parameters and returns the last identity value inserted within the current scope. So if an INSERT statement populating the order table executes a trigger and adds a row to the audit_trail table, SCOPE_IDENTITY() will return the last value added to the order table, whereas @@IDENTITY will return the last value added to audit_trail.

ISDATE Function

The ISDATE function determines whether the parameter passed is of a date and time data type. This function returns a BIT value, as in the following example:

1.SELECT  ISDATE('february 31, 2009') AS 'february 39, 2009',   ISDATE('January 1, 2009') AS '1/1/2009'

Results:

1.february 39, 2009 1/1/2009 

2.----------------- ----------- 

3.0                 1

ISNUMERIC Function

The ISNUMERIC deterministic function determines whether the parameter passed is of a numeric data type. This function returns a BIT value, as in the following example:

1.SELECT   ISNUMERIC('abc') AS 'abc',   ISNUMERIC('123.45') AS '123.45'

Results:

1.abc         123.45 

2.----------- ----------- 

3.0           1

CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP function works exactly the same way as GETDATE: It returns current date and time. For example:

1.SELECT CURRENT_TIMESTAMP

Results:

1.----------------------- 

2.2006-03-19 16:24:57.827

DATALENGTH Function

The DATALENGTH deterministic function is similar to the LEN function, which returns the length of a particular string expression. DATALENGTH returns the number of bytes used to represent an expression of any data type. For example, the following query returns the data length for currency which is stored as NCHAR(3) data type:

1.SELECT TOP 1  DATALENGTH(CurrencyAlternateKey) ,  CurrencyALternateKey  FROM dimCurrency

Results:

1.CurrencyALternateKey 

2.----------- -------------------- 

3.6           AED

@@TRANCOUNT Function

The @@TRANCOUNT function returns the number of open transactions on a particular connection. You can check the value of @@TRANCOUNT to troubleshoot blocking issues. @@TRANCOUNT can also be used for error handling; if @@TRANCOUNT returns anything other than 0, something must have gone wrong and you have uncommitted transactions on the current connection. This function does not take any parameters.

XACT_STATE Function

The XACT_STATE function is new with SQL Server 2005. It is similar to @@TRANCOUNT since it determines whether there are any uncommitted transactions on the current connection. The XACT_STATE function does not accept any parameters. Unlike @@TRANCOUNT the XACT_STATE function can also determine if the uncommitted transaction has been classified as an uncomittable transaction. This function can return one of the following values:

• 1 The session has an active transaction. The transaction can be committed.
   
• 0 The session has NO active transactions.
   
• (-1) The session has an active transaction; an error has occurred which classifies the active transaction as uncommittable. The transaction cannot be committed; neither can the session request rolling back to a savepoint. Rather the entire transaction must be rolled back. After the transaction has been rolled back the session can initiate a new transaction.

You can effectively use XACT_STATE function for error handling, as shown below:

1.IF (XACT_STATE()) = -1      BEGIN  PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'          ROLLBACK TRANSACTION;      END

@@ERROR Function

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. The @@ERROR function is used for error handling. With SQL Server 2000 and previous releases checking @@ERROR was the only way to diagnose and troubleshoot errors. SQL Server 2005 introduces TRY / CATCH syntax for error handling as well as several new functions: ERROR_LINE, ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, and ERROR_MESSAGE. Note that @@ERROR returns the error number returned by the last executed statement, so it's important to catch the error immediately after it occurs. For example, the following query catches the error because it checks for error immediately after the statement that encountered the error:

1.SELECT 1 / 0  SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

1.----------- 

2.Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.     

3.----------------------------------------------- 

4.error number is: 8134

However, the next example does NOT catch the error because it checks the @@ERROR function value too late, after a statement that completes successfully:

1.SELECT 1 / 0 

2.SELECT 'this is a successful statement. it resets @@ERROR to zero!' 

3.SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

1.----------- 

2.Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.     

3.----------------------------------------------------------- 

4.this is a successful statement. it resets @@ERROR to zero!     

5.----------------------------------------------- 

6.error number is: 0

@@ROWCOUNT Function

The @@ROWCOUNT function returns the number of rows affected by the last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If your query was supposed to update 15 rows but @@ROWCOUNT returns 10 then something must have gone wrong. This function is often used for error handling. Much like with @@ERROR it's important to get @@ROWCOUNT value immediately after the statement you want to examine. For example, the following query erroneously reports that total number of affected rows is one, even though your main query returned 10 rows, as desired:

1.SELECT TOP 10 * FROM dimCustomer 

2.SELECT 'this is a successfull statement. it resets @@ROWCOUNT to one!' 

3.SELECT 'number of rows affected is: ' + CAST(@@ROWCOUNT AS VARCHAR)

The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT data type instead of an INT data type.

Note: It's easy to confuse the functionality of ROWCOUNT and @@ROWCOUNT. The former advises SQL Server to affect only a specified number of rows (similar to the TOP keyword); the latter simply counts the number of rows affected, as shown here:

1./* first limit the output to 2 rows */ 

2.SET ROWCOUNT 2   

3./* this query will affect only 2 rows */ 

4.SELECT EnglishProductSubcategoryName FROM DimProductSubCategory   

5./* now use functions to count the number of  affected rows */ 

6.SELECT @@ROWCOUNT AS '@@rowcount_output',   ROWCOUNT_BIG() AS 'rowcount_big_output'

Results:

1.EnglishProductSubcategoryName 

2.-------------------------------------------------- 

3.Mountain Bikes  Road Bikes   

4.@@rowcount_output rowcount_big_output 

5.----------------- -------------------- 

6.2                 2

APP_NAME() Function

The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection and want to know which app initiated the offending process. For example:

1.SELECT APP_NAME()

Results:

1.------------------------------------------------- 

2.Microsoft SQL Server Management Studio - Query

UPDATE() Function

The UPDATE() function is only available within INSERT or UPDATE triggers and determines whether a value of a single column has been modified. You can use this function to execute certain logic within a trigger only if the value of a particular column has changed. The function accepts column name as the only parameter. For example, the following query rolls back the transaction within trigger if the value of AccountCodeAlternateKey column has been changed:

1.IF UPDATE(AccountCodeAlternateKey)  BEGIN   RAISERROR('key cannot be updated', 16, 1)   ROLLBACK  END

COLUMNS_UPDATED() Function

The COLUMNS_UPDATED() function works similarly to UPDATE() except it checks for multiple columns being updated by the same statement. Like UPDATE() function the COLUMNS_UPDATED() is only available within insert and update triggers. This function returns a bit pattern with VARBINARY data type showing which columns have been affected by the INSERT or UPDATE statement that invoked the trigger. The function does not accept any parameters. For example, the following query checks whether columns 2, 3 and 4 have been modified:

1.IF (COLUMNS_UPDATED() & 14) = 14  BEGIN   SELECT 'columns 2, 3, and 4 have been changed'  END

ERROR_LINE Function

The ERROR_LINE function returns the line number at which the error occurred which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the line number where the error occurred:

01.BEGIN TRY

02.SELECT 'empty string'

03. 

04.SELECT 1 / 0

05.END TRY

06. 

07.BEGIN CATCH

08.SELECT 'the error occurred at line ' + CAST(ERROR_LINE() AS VARCHAR)

09.END CATCH

Results:

1.------------ 

2.empty string     

3.-----------     

4.-------------------------------------------------------- 

5.the error occurred at line 3

ERROR_MESSAGE Function

The ERROR_MESSAGE function returns the text of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error text:

1.BEGIN TRY 

2.SELECT 1 / 0 

3.END TRY   

4.BEGIN CATCH 

5.SELECT 'the error was: ' + ERROR_MESSAGE() 

6.END CATCH

Results:

1.-----------     

2.-------------------------------------------------- 

3.the error was: Divide by zero error encountered.

ERROR_NUMBER Function

The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error number:

1.BEGIN TRY 

2.SELECT 1 / 0 

3.END TRY   

4.BEGIN CATCH 

5.SELECT 'the error number was: ' + CAST(ERROR_NUMBER() AS VARCHAR) 

6.END CATCH

Results:

1.-----------      -------------------------------------------------- 

2.the error number was: 8134

ERROR_PROCEDURE Function

The ERROR_PROCEDURE function returns the name of the stored procedure or trigger that encountered the error. This function does not accept any parameters and can be effectively called from CATCH block. For example, the following query creates a stored procedure that intentionally causes divide by zero error. Next the procedure is executed and the name of the erroneous stored procedure is returned:

01.CREATE PROCEDURE my_test_proc  AS   

02.SELECT 1 / 0 

03.GO   

04.BEGIN TRY 

05.EXEC my_test_proc 

06.END TRY   

07.BEGIN CATCH 

08.SELECT 'the erroneous procedure was: ' + ERROR_PROCEDURE() 

09.END CATCH

Results:

1.------------------------------------------ 

2.the erroneous procedure was: my_test_proc

ERROR_SEVERITY Function

The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error severity:

1.BEGIN TRY 

2.SELECT 1 / 0 

3.END TRY   

4.BEGIN CATCH 

5.SELECT 'the error severity was: ' + CAST(ERROR_SEVERITY() AS VARCHAR) 

6.END CATCH

Results:

1.------------------------------------------------------ 

2.the error severity was: 16

ERROR_STATE Function

The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error state:

1.BEGIN TRY 

2.SELECT 1 / 0 

3.END TRY   

4.BEGIN CATCH 

5.SELECT 'the error state was: ' + CAST(ERROR_STATE() AS VARCHAR) 

6.END CATCH

Results:

1.------------------------------------------------------ 

2.the error state was: 1