Saturday, April 30, 2011

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.

Tuesday, April 26, 2011

New Prices for Microsoft Certifications Exams

image

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

Sunday, April 24, 2011

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;

image

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;

image

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

Saturday, April 23, 2011

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.

Thursday, April 21, 2011

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 Smile.

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;

image

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.

image

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.

Sunday, April 17, 2011

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.

Ordering1

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.

Ordering2

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.

Tuesday, April 12, 2011

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;

Order

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

Order2

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