Database Option: CURSOR DEFAULT {LOCAL | GLOBAL} - Is CURSOR Global?

Setting the value of this option as GLOBAL has been understood wrongly by many developers. All you have to remember is, this “global” does not refer all connections, it refers the current connection. If either database option or at the declaration of cursor, GLOBAL is set, it means that scope of the cursor is global to the connection. Simply it can be referenced in any stored procedure or batch executed by same connection. If LOCAL is used, it is only for the batch which it was created. See the code below;

-- Parent SP with a cursor

CREATE PROC TestCursorParentSP

AS

BEGIN

    DECLARE @SalesOrderID int

    DECLARE cur CURSOR GLOBAL

    FOR

    SELECT SalesOrderID 

    FROM Sales.SalesOrderHeader 

    OPEN cur

    FETCH NEXT FROM cur INTO @SalesOrderID

    SELECT @SalesOrderID

    EXEC TestCursorChildSP

    CLOSE cur

    DEALLOCATE cur

END

-- Child SP that accesses cursor declared by parent

CREATE PROC TestCursorChildSP

AS

BEGIN

    DECLARE @SalesOrderID int

    FETCH NEXT FROM cur INTO @SalesOrderID

    SELECT @SalesOrderID

END

-- Executing parent sp

-- Get values from both SPs

EXEC TestCursorParentSP

If the cursor is declared as LOCAL, child SP will not be able to access it.

New Prices for Microsoft Certifications Exams

If you plan for doing MS exams, better hurry up. Following certifications are slated for a price increase:

• Microsoft Certified Technology Specialist (MCTS)

• Microsoft Certified IT Professional (MCITP)

• Microsoft Certified Professional Developer (MCPD)

• Microsoft Certified Desktop Support Technician (MCDST)

• Microsoft Certified Systems Administrator (MCSA)

• Microsoft Certified Systems Engineer (MCSE)

• Microsoft Certified Application Developer (MCAD)

• Microsoft Certified Solution Developer (MCSD)

• Microsoft Certified Database Administrator (MCDBA)

See more detail:
http://www.microsoft.com/learning/en/us/certification/cert-pricing-FAQ.aspx

TOP WITH TIES: Have you shown your TOP 10 Customers properly?

Think about a scenario where you have to show your top 10 customers based on their purchases. If the record set is as below;

and if you show first 10 records using TOP 10, have you shown all best customers? See the 11th record, it is same as 10th order, hence he should be added to top-10 list. In this case, top-10 list becomes top-11 list. But we cannot change the query as TOP 11 because it is not always guaranteed that 11th record is same as 10th record. In that case, what would be the way?

The solution is, TOP 10 WITH TIES. It adds additional rows to the resultset if any tied rows for 10th are there. See the code below;

Note that WITH TIES can be only used with SELECT statement and ORDER BY must be used too.

SQL Server 2008 R2 SP1–CTP is available for downloading

Microsoft announces the availability of Service Pack 1 as Community Technology Preview for SQL Server 2008 R2. Here are some of enhancements come with SP1;

• Dynamic Management Views for increased supportability
• ForceSeek for improved querying performance
• Data-tier Application Component Framework for improved database upgrades
• Disk space control for PowerPivot

Here is the link for downloading: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df&displaylang=en

Note that this is CTP, not for installing in live environment.

Does Referential Integrity improve performance?

I believe that everybody knows that Referential Integrity is for maintaining the relationship between two entities; Parent and Child. It makes sure that no child records is exist without a parent. Referential Integrity is implemented with Foreign Keys. The foreign key column can be set as Nullable, in that case it allows to have a child without a parent .

While I was going through my one of my old presentations, I found a slide related to this (yes, it is good to keep old presentations, human forgets things). Let me explain it with a code;

The above code is executed against AdventureWorks2008 database. Note that it gets records only from Detail table even though Header table is joined. You may say that this join is useless because it does not get data from Header. True, but see, though we have linked the header to detail, as per the plan, SQL Server does not access the header. It knows that referential integrity is implemented and existence of parent records is guaranteed, hence it does not need to access the header. Now let’s remove the relationship between header and detail, and execute the same.

Query is same but in this case, SQL Server scans the header table. The reason is it does not know whether records are exist in header for every child, it accesses the header though there is no request for values in header. Can you see, referential integrity is not only for making sure that database is consistence, it improves the performance too.

Ordering Attribute Members of Dimensions in Analysis Services

As each attribute in a dimension can have a key column and optionally name column, attribute can be sorted by either key or name column. Sorting is done with OrderBy property. Once the property is set, attribute is sorted in ascending when it is displayed. This is okay with most cases but in some cases sorting is needed in a different way. Examples cane be, sorting in descending order, sorting by not its key or name, by some other attribute. This post discusses these exceptional cases.

Sort by some other attribute
Good scenario for this is Month attribute in a Month dimension. Usually we set Month attribute’s Key Column as MonthKey and its Name Column as MonthName. If the sorting is set as Key with OrderBy property, months will be ordered chronologically only if you have inserted them in source table in an order. If they are not inserted in an order, months will not be ordered as we want. Again, if OrderBy property is set as Name, months will be displayed in alphabetical order which is not the way we usually want. See the image below.

Note that records are not inserted in an order. Month attribute’s key is set with MonthKey and its name is set with MonthNameWithYear. You can see how they are displayed with OrderBy set as key and name.

In order to have the proper ordering on Month attribute, we need an additional column to be used for sorting. Best candidate is MonthNumberOfYear column. It can be used as sorting key for Month. One requirement for doing this is, have a relationship between Month and MonthNumberOfYear, in other words, making MonthNumberOfYear as Related Attribute for Month. By default, when the dimension is created, wizard adds all the dimension’s attributes as related attributes of the key. In this case, it has been added too. If not, you need to manually add this through Attribute Relationships (a tab) in BIDS’s Dimension designer.

Once the column is set as a related attribute, change the OrderBy to AttributeKey and set OrderByAttribute as MonthNumberOfYear.

Once processed, months will be displayed in an order as we want. There are two things to be considered in this setting. If MonthNumberOfYear column is not required for querying, you can hide it by setting AttributeHierarchyEnabled property to false. Other thing is, Relationship Type between an attribute and related attribute. It can be set as either Rigid or Flexible. If the relationship is fixed over time, not going to change (Type 2), rigid is the best option. If the relationship can change over time (Type 1), type has to be set as Flexible.

Descending Order
Once the sorting is set for an attribute, it is sorted in ascending order. That is the only possible sorting order supported by Analysis Services. If descending order is required, just like the previous one, support is needed from another attribute that maintains value in descending order. For example, if months need to be sorted in descending, MonthNumberOfYear has to be filled with values 12 to 1, not 1 to 12.

Logical Query Processing Phases in SELECT statement

The SELECT statement can be constructed with various clauses as per the requirement. It can be as simple as just a set of columns from a table or as complex as a set columns from multiple tables with aggregate functions. Understanding how SQL Server executes added statements will be worthwhile for constructing the SELECT quickly and correctly as per the requirement. Here is the logical processing order in SELECT statement;

Note that all clauses have not been used with above code. Here is the order of all possible phases;

As you see, it is not the order we have written the query, hence keeping this order in mind is absolutely helpful.