Help support Itzik's plea for T-SQL Windowing functions

One of our founders at Solid Quality , Itzik Ben-Gan is known to most in the SQL Community, particularly for his passion around the T-SQL language. He's recently written an amazing blog post that describes why these functions are needed, along with a plea for helping promote the ideas to the Microsoft product group via the Connect site. Anyone with an interest in T-SQL should read the post as, by itself, it provides great insights into the language and the possibilities that some subtle changes could...(read more)

T-SQL to get all the Error Messages and Error Codes
Technorati Tags: Error, Messages, Codes, SQL Server Sometimes We Tend to search for Error Codes and Error messages. We require this for trouble shooting. Here is a Small T-SQL which will get you all the Error Codes and the Respective Error Messages. Hope this Might Help us. SELECT message_id as Error_Code, severity, is_event_logged as Logged_Event, text as [Error_Message] FROM sys.messages WHERE language_id = 1033;

Which Cloud Are You Talking About?
In the early days of computing, IT delivered computing using huge mainframes, accepting input from users, crunching the numbers and returning results to a very thin client - just a green-screen terminal. PC's came into the mix, allowing users a local experience with graphical interfaces and even other larger PC's as "servers". And now comes the "cloud". It seems everything with a network card, or anything that uses a network card, is "cloud" or "cloud enabled". But what is the "real" definition of the "cloud"? In effect, it's just outsourced or virtualized IT.There are really three levels of this definition. The first is "Infrastructure as a Service" or IaaS. This means you just rent a server or storage on someone else's network. While this is interesting, you still have to figure out scale, management and so on. You're only relieved of the hardware costs and maintenance. It's interesting to IT because it buffers them against hardware costs and upgrades, at least directly. In fact, you're probably doing this yourself already, by using Hyper-V or VMWare in your datacenters. The next level is "Software as a Service", or SaaS. You can think of things like Hotmail or Expedia in this way. You log on to an application over the web, use it, and log off. Nothing to install, no upgrades, no management, no maintenance. This is also an interesting "cloud" offering, although the limitations are the same as the mainframe days - you can't easily make changes to the software, and in some cases, you just get what you get. Great for consumers, not as interesting to IT. In fact, for us IT folks, this is what we need to provide, not necessarily something we use.Finally there is the "Platform as a Service" or PaaS. In this model, you have a platform to write on, which relieves the hardware issue of IaaS, you can write code that delivers software as in SaaS, and provides a way to create, test and deliver both. You're insulated against hardware and even platform upgrades, and you're given a way to scale the application without caring where it really lives. This is the road that Microsoft takes with Windows and SQL Azure.Of course it isn't all rainbows and candy - there are things to think about, like security, uptime and more. There's a formal process for determining the applications that fit - which I'll write about later.

SQL Null: a set of posts on the null
Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. NULL is also an SQL reserved keyword used to identify the Null special marker. Null has been the focus of controversy and a source of debate because of its associated three-valued logic (3VL), special requirements for its use in SQL joins, and the special handling required by aggregate functions and SQL grouping operators. Although special functions and predicates are provided to properly handle Nulls, opponents feel that resolving these issues introduces unnecessary complexity and inconsistency into the relational model of databases. Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown. In my next few posts, I will explore the null.

Are you replacing Temp Tables with Snapshot Isolation?
My office is a cross-platform shop with both Oracle 10g and SQL Server 2000/5/8. Lately we've started doing some cross-training, so that all the DBAs, the theory at least, could function on either platform. The Oracle guys, I'm afraid, are way out ahead of me in this regard. In my defense, though, I have to manage more than 100 servers to their 5! Testing the waters with Oracle has been really interesting - I like looking at different technologies for comparison. One issue that we've had great discussion...(read more)

SQL Null: Grouping and sorting
Because SQL Server defines all Null markers as being unequal to one another, a special definition was required in order to group by Nulls together when performing certain operations.  SQL defines “any two values that are equal to one another, or any two Nulls”, as “not distinct”.  This definition of not distinct allows SQL to group and sort Nulls when the “GROUP BY” clause are used.  Let’s look at an example using group by and nulls.  You will see, that SQL Server does group them together by using the not distinct definition: DECLARE @table1 TABLE( ID INT, myStr VARCHAR(100)) INSERT INTO @table1( ID, myStr) Values (1, 'value a'), (2, 'value b'), (3, 'value a'), (4, 'value a'), (5, 'value b'), (6, null), (7, 'value b'), (8, null), (9, null), (10, 'value a'), (11, null), (12, null) SELECT * FROM @table1 SELECT myStr,       COUNT(1) as CountFROM   @table1GROUP  BY myStr The 1st select results show the raw values: ID myStr 1 value a 2 value b 3 value a 4 value a 5 value b 6 NULL 7 value b 8 NULL 9 NULL 10 value a 11 NULL 12 NULL The 2nd results show the group by: myStr Count NULL 5 value a 4 value b 3 As you can see, when SQL Server completes the group by clause it combines rows with NULL values.  When thinking about how it handles string concatenations, this is a little twist that can catch you off guard or make you think.  Distinct, same thing: SELECT distinct myStr FROM @table1 The result is 3 rows: NULL, value a, value b Hope this helps you when thinking about null values and grouping.  Try the example and look how SQL Server handles the default sort order for Nulls.

Using Dynamic MDX in Reporting Services: Part 1
If you're using Analysis Services as a data source for Reporting Services reports, you can build a simple dataset using the graphical query designer, but you'll want to switch to the generic query designer to create the MDX query string manually when you have more advanced requirements. Using the generic query designer, you can:· Impose greater control over the sets that you want to add to the rows axis by using set functions.· Add query-scoped named sets to the query in addition to calculated members. (Calculated members can also be added in the graphical query designer, but not named sets.)· Build dynamic MDX queries.When would you need a dynamic MDX query? Whenever you want to modify the query based on a condition known only at run-time, typically based on a parameter value. If you're using the graphical query designer, you can auto-generate the report parameter's query for available values by selecting the Parameter checkbox. When the user selects a value during report execution, Reporting Services passes the unique name for the selection to the query and all is well. However, there might be situations when the user selection doesn't come from the cube, so you must find a way to convert the parameter value into a value that will work with the query.In a series of posts, I will explore the available options for working with dynamic MDX queries in Reporting Services. By dynamic MDX, I mean that the query can be different each time it executes. In this post, I cover the use of StrToMember() and StrToSet() functions in parameters.A very common scenario is the requirement to pass dates into a query. If you have a date filter for the report, do you really want users to navigate through a list of dates from the cube as shown below?This list of dates - even if it's arranged hierarchically by month, quarter, and year - is what you get when you build the parameter directly from the date hierarchy in the query designer as shown below.Wouldn't a more user-friendly experience allow the user to select a data from a calendar control? I can do this by changing the auto-generated report parameter's data type to a Date/Time data type and clear the "Allow multiple values" check box. I must also change the Available Values setting for the parameter to None. I can set the default value to "No default value" to force the user to make a selection, or do something nice like define an expression to set a date, like =Today().So far, so good. But the problem now is that the date data type returned by the calendar control cannot be used by the MDX query without some intervention. I need to change the Parameter Value mapped to the query parameter in the Dataset Properties to an expression, like this:="[Date].[Calendar].[Date].[" + Format(CDate(Parameters!DateCalendar.Value), "MMMM d, yyyy") + "]" The expression that you use to convert a date like 2/1/2008 to a valid unique name in your Date dimension might look different. My example is specific to the Adventure Works 2008 R2 cube, which requires the date member to look like this: [Date].[Calendar].[Date].[February 1, 2008].That's fine so far, but the result of this expression is a string and the MDX query requires a member or a set. The autogenerated query already makes this change for you fortunately. However, if you're creating your query manually, you should understand what it's doing, especially if you need to make changes to it.The autogenerated query looks like this before I make changes:SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) }DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWSFROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNSFROM [Adventure Works])WHERE ( IIF( STRTOSET(@DateCalendar, CONSTRAINED).Count = 1, STRTOSET(@DateCalendar, CONSTRAINED), [Date].[Calendar].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSI prefer to simplify the query as shown below - removing the text highlighted in red text above. The function does what it says - changes the string (represented by the parameter @DateCalendar) into a set object. I remove the WHERE clause from the query as the FROM clause adequately restricts the query results to cell values related to the selected date. If I need the dimension properties in the report to display something or if I need the cell properties for report formatting, I'll include only the ones I need, but for this example I have removed them all from the query.SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWSFROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNSFROM [Adventure Works])You could change the StrToSet() function to a StrToMember() function and get the same result. It's not harmful to leave StrToSet() as it is. It just returns a set of one member in this case-the date from the calendar control which is a valid set. The CONSTRAINED flag is used to prevent an injection attack and requires the expression to resolve to a valid member before the query executes.One challenge that often confounds people working with MDX queries in the generic query designer is the inability to copy and paste the query into Management Studio for testing when parameters are in the query as shown above. The MDX query editor doesn't support parameters. Teo Lachev (blog | twitter) posted some advice for working with parameterized MDX queries in Management Studio which I encourage you to check out.In my next post, I'll explain how to use the OLE DB for OLAP provider with dynamic MDX to create a dataset.

Just a Haircut?
This weekend was a little hurried and harried. I immensely enjoyed presenting at SQL Saturday #48 ! I got to meet one of my co-workers face-to-face for the first time and any time I get to spend some time with K. Brian Kelley ( Blog | @kbriankelley ) is always a treat. In the rush, I didn't have time for a haircut before I left for Waltham to deliver From Zero To SSIS in cooperation with Boston SQL Training - the first ever SSIS course written cover-to-cover by me! I went shopping earlier and while...(read more)

A Solution: T-SQL Challenge 30
Check out the original post @ http://www.epperlys.com The Challenge The details of the challenge can be found on Beyond Relational’s website under TSQL Challenge 30 – A Matrix Transposition challenge in TSQL.  The challenge seemed to be targeting a couple of different skills: Effective use of the ROW_NUMBER() function Though not required, CTEs Pivoting and Unpivoting (at least conceptually) Keeping proper track of rows and columns Before we dive in, I should point out that I received the 7th highest score . . . not the greatest, but I was happy just to be on the list.  I tell you this not to boast, but to warn you that my solution has problems . . . but we’re also going to look at the steps (actually, one defining step) the winner took and why his solution was better. My Solution Let’s start with a view of the whole script and then attempt to unpack the thought processes. with m as ( select id, col1, ROW_NUMBER() over(partition by id order by col1) c from TC30_Metrics union all select id, col2, ROW_NUMBER() over(partition by id order by col2) c from TC30_Metrics union all select id, col3, ROW_NUMBER() over(partition by id order by col3) c from TC30_Metrics union all select id, col4, ROW_NUMBER() over(partition by id order by col4) c from TC30_Metrics union all select id, col5, ROW_NUMBER() over(partition by id order by col5) c from TC30_Metrics ) ,m2 as ( select id ,col1 ,c ,row_number() over(partition by id, c order by col1) as r from m ) ,m3 as ( select m2.id ,case m2.c when 1 then col1 end as col1 ,case m2.c when 2 then col1 end as col2 ,case m2.c when 3 then col1 end as col3 ,case m2.c when 4 then col1 end as col4 ,case m2.c when 5 then col1 end as col5 ,r from m2 ) select m3.id ,max(m3.col1) as col1 ,max(m3.col2) as col2 ,max(m3.col3) as col3 ,max(m3.col4) as col4 ,max(m3.col5) as col5 from m3 group by m3.id, m3.r order by id   New Column Assignment First, I wanted to tag the values with their appropriate columns.  As such, the goal of the first CTE was to put all of the values from the 5 columns into the same column of our first virtual table (effectively unpivoting).  Furthermore, we need to reorder with them, so we use the ROW_NUMBER() function, ordering by the respective column.  The “partition by id” portion of the OVER() clause is required because the challenge states that there may be multiple matrices that need to be transposed.  This will start the ordering process over for each new ID column.  The new column is simply named “c” to because it is the surrogate identifier for the columnar data.  Now the view of the data looks like: id col1 c -- ----------- -------------------- 1 1 1 1 3 2 1 7 3 1 8 4 1 9 5 1 1 1 1 3 2 1 4 3 1 5 4 1 8 5 1 0 1 1 1 2 1 2 3 1 2 4 1 5 5 1 1 1 1 1 2 1 3 3 1 4 4 1 6 5 1 3 1 1 4 2 1 5 3 1 6 4 1 7 5 Row, Row, ROW_NUMBER() The second step in my logic was to assign the columnar data to rows according to value order.  Again, simply performed by another ROW_NUMBER() function, partitioned by ID and c then ordered on the value.  I named the new virtual column “r” because it is the surrogate identifier for the row in which the value is to be placed.  This makes the data look like: id col1 c r -- ----------- -------------------- -------------------- 1 0 1 1 1 1 1 2 1 1 1 3 1 1 1 4 1 3 1 5 1 1 2 1 1 1 2 2 1 3 2 3 1 3 2 4 1 4 2 5 1 2 3 1 1 3 3 2 1 4 3 3 1 5 3 4 1 7 3 5 1 2 4 1 1 4 4 2 1 5 4 3 1 6 4 4 1 8 4 5 1 5 5 1 1 6 5 2 1 7 5 3 1 8 5 4 1 9 5 5 Put It Back Together This view of the data gives us the proper coordinates for constructing the final query.  I chose to do this in two steps, but the following could have easily been combined.  First, I performed the first step of a pivot so that the values got into the appropriate columns.  You can note, by the data below, that this causes NULL values to be inserted as any one combination from the data above renders a value in only one column at a time.  It’s kinda fun because we get this nice diagonal effect of the data: id col1 col2 col3 col4 col5 r -- ----------- ----------- ----------- ----------- ----------- -------------------- 1 0 NULL NULL NULL NULL 1 1 1 NULL NULL NULL NULL 2 1 1 NULL NULL NULL NULL 3 1 1 NULL NULL NULL NULL 4 1 3 NULL NULL NULL NULL 5 1 NULL 1 NULL NULL NULL 1 1 NULL 1 NULL NULL NULL 2 1 NULL 3 NULL NULL NULL 3 1 NULL 3 NULL NULL NULL 4 1 NULL 4 NULL NULL NULL 5 1 NULL NULL 2 NULL NULL 1 1 NULL NULL 3 NULL NULL 2 1 NULL NULL 4 NULL NULL 3 1 NULL NULL 5 NULL NULL 4 1 NULL NULL 7 NULL NULL 5 1 NULL NULL NULL 2 NULL 1 1 NULL NULL NULL 4 NULL 2 1 NULL NULL NULL 5 NULL 3 1 NULL NULL NULL 6 NULL 4 1 NULL NULL NULL 8 NULL 5 1 NULL NULL NULL NULL 5 1 1 NULL NULL NULL NULL 6 2 1 NULL NULL NULL NULL 7 3 1 NULL NULL NULL NULL 8 4 1 NULL NULL NULL NULL 9 5 Lastly, we have to get rid of the NULL values and make sure that each matrix is ordered together (via ID column).  This is accomplished by grouping on the ID and the surrogate row identifier (“r”) we established in the second step.  With that, we have our solution! What the Winner Did Better The winner of this challenge, Muhammad AlPasha, smoked us all with one major approach difference.  Looking back on the first thing I did for my solution (which EVERY other submission on the winners list did as well), unpivoting by using UNION ALL led to our demise.  Performing 5 SELECT statements on the same table with, though you end up with one result set, causes 5 reads on the base table.  Muhammad did it with one read (view his solution).  Instead of the unpivot, he tacked on the new column assignments for each respective value by create 5 new columns.  I think this approach feels antithetical to most of us out here in TSQL land because it feels like something a mainframe application would attempt to do.  But it was genius because the number of reads that this solution incurred would always be 1/5 of anything the immediate UNPIVITORs were going to do.  Below are the query plans of the new column id of the two solutions; the 5 reads from my solution is quite apparent.  I realize you can’t see the detail of the pictures, but you can make out the differences: The remainder of the mental gymnastics isn’t necessarily identical, but is quite similar.  The second CTE explodes the data so that it is similar to my first step.  It then continues to calculate the appropriate row and pivot the data back for the final result.  It took a little more code, but was the most definitely the superior plan.  Kudos, indeed. . . . complete my joy by being of the same mind, having the same love, being in full accord and of one mind.  Do nothing from rivalry or conceit, but in humility count others more significant than yourselves. Philippians 2:2-3 ESV

24 Hours of PASS for Latin America!
One of the biggest pushes I was trying to make while on the PASS board was to help grow the organization into other areas of the world, and probably a little out of the organization's comfort zone. We in western English-speaking countries often totally underestimate the passion for SQL Server and the number of professionals that work with it around the world. In my time on the board, I was both amazed and privileged to see the types of events already occurring and the vast number of attendees. Often...(read more)

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests
In this post we shall see how to incorporate a typical manual test in an automated test harness with minimal effort. Because our goal is to benefit from unit testing, and not to develop as many features as we can think of, we shall concentrate on the most common use cases. In fact, just one use case is so common that is happens more often all all others combined together, at least in my practice. Naturally this whole "the most common" thing is very subjective, and your perspective may be very different...(read more)

T-SQL joke
I was attending an ORM class for developers where I heard it said "Don't select star from a table". I've heard this many times and I'm sure you have, too. It makes for brittle code in case a column is added to the table. I started wondering if I could create an example that is an exception to the rule and came up with this: create table aTable ( star int ); select star from aTable ;...(read more)

Presenting at SQLLunch Today!
I'm honored and excited to present SSIS Design Patterns 1c: Variables, Events, and Automated Custom Logging Patterns for SQLLunch today (11 Oct 2010)! In this talk, I cover building a custom logging solution in SSIS. I hope to see you there! :{>...(read more)

Enforcing naming conventions using database unit testing
My naming convention obsession Anyone that has ever worked with me will tell you that I am a stickler for naming conventions. I have a somewhat obsessive reputation for it; I can’t help it – I seem to have a deep seated uncontrollable desire to ensure that every object in my database(s) is/are named consistently (is there anyone else out there equally as obsessive?). I have tried various techniques down the years to try and enforce naming conventions but none of them really worked. I’ve got scripts that alter object names (such a script is in my script library in fact) but these are only any use if they actually get run, they don’t actually enforce the conventions – that’s a manual step. I’ve thought about using Policy-Based Management (PBM) to enforce naming conventions but given I’m a developer and not a DBA that’s not something that is readily available to me and besides, using PBM to enforce naming conventions is reactive rather than proactive if you are developing the code on a machine where the policies are not enforced. Another option I looked into using was Team Foundation Server (TFS) check-in policies; these are policies that can be applied to artefacts when they get checked-in to TFS’s source control system. This option really appealed to me because the naming conventions could be enforced during check-in (i.e. very very early) and didn’t require DBA intervention. In practice though enforcing naming conventions using TFS check-in policies has a few sizable issues: Its not easy. It would require you to parse the file that was getting checked-in, decide what sort of object is defined in the file, and then check the name of the object based on things like object name, schema, etc... TFS check-in policies are not installed on the TFS server, they are installed on the development workstations. This means there is a dependency and, even though the source code for the check-in policies can be distributed with your application source code, I didn’t really like this. You’re relying on each developer to enforce the check-in policy and with the greatest will in the world….that aint gonna happen. Its too easy to turn them off. There is the obvious dependency on using TFS, not something every development shop uses even in the Microsoft space. Database unit testing to the rescue No, a better solution was needed and I came up with one in the shape of automated database unit testing. I have spoken recently about how I have become a big fan of database unit testing (see my post Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution) and being able to enforce naming conventions is one very good reason for that. Enforcing naming conventions using automated unit tests has a number of advantages: They can be written against the metadata of the objects themselves (i.e. by querying SQL Server’s system views) so there’s no parsing that needs to be done. They can be employed as part of a Continuous Integration (CI) process and run as a build verification test (BVT). Someone checks-in an object that violates the naming convention? Bang: broken build! Developers can’t circumvent the tests. Nothing needs to be installed on the development workstations. The tests live wholly as part of your source code. Not dependent on use of a particular source control system Hence I have written some unit tests that enforce the following naming conventions: Check constraints must be of the form CK_<schemaName><tableName>_XXX Column names must begin with a capital letter Column names cannot contain underscores Default constraints must be named DF_<schemaName><tableName>_<ColumnName> Foreign keys must be of the form FK_<parentObjectSchema><parentObject>_REF_<referencedObjectSchema><referencedObject>XXX Non-unique clustered keys must be of the form IXC_<schemaName<TableName>_<Column><Column><Column>… Non-unique non-clustered keys must be of the form IX_<schemaName><TableName>_<Column><Column><Column>... Unique clustered keys must be of the form IXUN_<schemaName><TableName>_<Column><Column><Column>… Unique non-clustered keys must be of the form IXUN_<schemaName><TableName>_<ColumnColumnColumn>... Primary keys must be of the form PK_<schemaName><tableName> Stored procedure names should not contain underscores Stored procedure names must begin with a capital letter Table names must not contain underscores Table names must begin with a capital letter I’m not stating that you should agree with these naming conventions (I don’t necessarily agree with them myself – they were defined before I arrived on my current project), the point here is that all of these rules can be enforced and its very easy to do it. Here’s the code for the unit test that enforces the  primary key naming convention:    /*PK name is PK_<schemaName><tableName>*/    SET NOCOUNT ON    DECLARE @cnt    INT;    SELECT  *    INTO    #t    FROM    (            SELECT  OBJECT_NAME(c.[parent_object_id]) AS [TableName],OBJECT_SCHEMA_NAME(c.[parent_object_id]) AS [SchemaName],c.*            FROM    [sys].[key_constraints] c            INNER JOIN [sys].[tables] t                ON  c.[parent_object_id] = t.[object_id]                  LEFT OUTER JOIN sys.extended_properties ep                ON  t.[object_id] = ep.major_id                AND ep.[name] = 'microsoft_database_tools_support'            WHERE ep.[major_id] IS NULL            AND c.[type] = 'PK'            )q    WHERE   [name] <> N'PK_' + [SchemaName] + [TableName]    SET     @cnt = @@ROWCOUNT;    IF  (@cnt > 0)    BEGIN            DECLARE @msg NVARCHAR(2048);            SELECT  @msg = '%d Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):' + STUFF((SELECT ', ' + [name] FROM #t a FOR XML PATH('') ),1,2,'')            FROM    (                    SELECT  1 AS [Id],*                    FROM    #t t                    )q            GROUP   BY q.[Id]            SELECT @msg            RAISERROR(@msg,11,1,@cnt);    END Essentially all it does is pull all of the primary keys out of [sys].[key_constraints], checks to see what the name should be, then if it finds any that violate the naming convention raise an error containing the names of all the primary keys in question. Here’s the error obtained when running the test against [AdventureWorks] (I’ve highlighted the pertinent bit): Test method Prs.SchemaTests.NamingConventions.PrimaryKeys threw exception: System.Data.SqlClient.SqlException: 70 Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):PK_ErrorLog_ErrorLogID, PK_Address_AddressID, PK_AddressType_AddressTypeID, PK_AWBuildVersion_SystemInformationID, PK_BillOfMaterials_BillOfMaterialsID, PK_Contact_ContactID, PK_ContactCreditCard_ContactID_CreditCardID, PK_ContactType_ContactTypeID, PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode, PK_CountryRegion_CountryRegionCode, PK_CreditCard_CreditCardID, PK_Culture_CultureID, PK_Currency_CurrencyCode, PK_CurrencyRate_CurrencyRateID, PK_Customer_CustomerID, PK_CustomerAddress_CustomerID_AddressID, PK_DatabaseLog_DatabaseLogID, PK_Department_DepartmentID, PK_Document_DocumentID, PK_Employee_EmployeeID, PK_EmployeeAddress_EmployeeID_AddressID, PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID, PK_EmployeePayHistory_EmployeeID_RateChangeDate, PK_Illustration_IllustrationID, PK_Individual_CustomerID, PK_JobCandidate_JobCandidateID, PK_Location_LocationID, PK_Product_ProductID, PK_ProductCategory_ProductCategoryID, PK_ProductCostHistory_ProductID_StartDate, PK_ProductDescription_ProductDescriptionID, PK_ProductDocument_ProductID_DocumentID, PK_ProductInventory_ProductID_LocationID, PK_ProductListPriceHistory_ProductID_StartDate, PK_ProductModel_ProductModelID, PK_ProductModelIllustration_ProductModelID_IllustrationID, PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID, PK_ProductPhoto_ProductPhotoID, PK_ProductProductPhoto_ProductID_ProductPhotoID, PK_ProductReview_ProductReviewID, PK_ProductSubcategory_ProductSubcategoryID, PK_ProductVendor_ProductID_VendorID, PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID, PK_PurchaseOrderHeader_PurchaseOrderID, PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID, PK_SalesOrderHeader_SalesOrderID, PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID, PK_SalesPerson_SalesPersonID, PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate, PK_SalesReason_SalesReasonID, PK_SalesTaxRate_SalesTaxRateID, PK_SalesTerritory_Territor... I am currently including these tests inside a C# test project inside Visual Studio 2010. Visual Studio has a rather nice feature that allows you to link to artefacts in other projects and hence we can host our single test class containing all of these tests in one place and link to it from whichever test project we want (typically you will have a test project per database) thus following the DRY principle. Here I show the dialog that demonstrates adding a link to an existing test class: And how it appears in the project. Note that NamingConventions.cs exists in both test projects but one is just a link to the other: Wrap-up I’m not sure my colleagues are too happy about these new tests given that they’re now breaking the build more often but nonetheless I think they realise the value (I guess I’ll find out tomorrow when they read this!!!) All-in-all its working very well for us and I’m now a very happy bunny knowing that naming conventions are being enforced and will continue to be so with zero effort from here on in. I have made the test class that contains all of the tests that I detailed above available on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20101005/MIControlTest.zip. If you want to use it you should simply be able to drop it into an existing C# database test project and away you go (change the tests to suit your naming conventions of course though). Hope this helps. If it does please let me know, I’d really love some feedback on this. @Jamiet

Which Azure Cloud Storage Model Should I Choose for my Application?
Most applications have four parts – input, computation, storage and output.  I’ll write about all of these over time, but today I want to focus on how to choose the storage part of the equation. This won’t be a full tutorial, full of detail and all that, but I will put forth some “rules of thumb” that you can use as a starter. I’ll also try and include some good pointers so you can research more.   NOTE: Utility Computing, or “the cloud”, or platform/software/architecture as a service, is a young discipline, and most certainly will change over time. That’s its advantage – that it can change quickly to meet your needs. However, that means information (like this blog entry) can be out of date. Make sure you check the latest documentation for Azure before you make your final decision, especially if the date on the post is older than six months or so. I’ll try and come back to update them, but check them nonetheless. Always start your search on the official site: http://www.microsoft.com/windowsazure/   Let’s start out with your options. You have four types of storage you can use for your applications: ·         Blobs ·         Tables ·         Queues ·         SQL Azure databases   Here are some rules of thumb for when you use each – and again, these are only guidelines. I’ll point you to some documentation for more depth.   Blobs: Use these for binary data (in other words, not text), and think of them like files on your hard drive. There are two types – block and page. Use block blobs for streaming, like when you want to start watching a movie before it even completes the download. You can store files up to 200GB at a pop. And they parallelize well. Use page blobs when you need a LOT of storage – up to a terabyte – and pages are stored in 512KB, well, pages. You can access a “page” directly, with an address.   Tables: Massive text blocks accessed using Key/Value pairs. If you’re used to “NoSQL”, you have the idea. You get one index on that pair, so choose the sort or search wisely. Not relational, but large, and fast.   Queues: This storage is used to transfer messages between blocks of code. If you think of the stateless-programming web-world, you need a way to tell one application something that isn’t event-based. This is how you do that. Since it’s a queue, it helps you with something called “idempotency”, which means that a single message on the queue will get processed once, and only once.   SQL Azure Databases: If you need relational storage, want to leverage Transact-SQL code you already have, or need full ACID, this is for you. There are size restrictions here, but I’ll not detail them so this information lives a little longer. Check out http://microsoft.com/sqlazure for specifications, whitepapers, the lot.   OK – I’ll end with a chart. This has some more information that you might find useful in your decision process:   If you need: Blob Table Queue SQL Azure Structured Data   Y   Y Relational Database       Y Server Side Processing       Y Access from outside Azure Y Y Y Y Messaging Infrastructure     Y   Persistent Storage Y Y   Y Size Limit 200GB/1TB 100TB 100TB 50GB   More info on Azure Storage: http://msdn.microsoft.com/en-us/library/ee924681.aspx http://www.azurejournal.com/2008/10/windows-azure-data-storage/   Many thanks to my teammates, Stephanie Lemus and Rick Shahid for help with the information in this post. Thanks for the help!

SQL Server High Availability: Quick view of Database Mirroring session between partners
From SQL Server 2005 version onwards database mirroring has been a good resource to provide High Availablity for the systems without having special hardware requirement. While I was assisting at the SME lounge within Database Platform area in Tech-Ed North America conference, I had few interesting questions from the users asking about various methods to monitor database mirroring environment. Database Mirroring Monitor is the tool which is a first hand resource to see what is happening within the partners, also the other methods such as query against DBM metada in catalog views, use of dynamic management views, PERFMON counters for DBM, DBM related event notifications and server events using WMI provider. To go further about catalog views and DMVs to view the mirroring information you can get information for each mirrored database from the instance. Here is a little TSQL to obtain commonly used mirroring metadata: SELECT d.name, d.database_id, m.mirroring_role_desc,                                m.mirroring_state_desc, m.mirroring_safety_level_desc,                                m.mirroring_partner_name, m.mirroring_partner_instance,                                m.mirroring_witness_name, m.mirroring_witness_state_desc FROM   sys.database_mirroring m JOIN sys.databases d ON     m.database_id = d.database_id WHERE  mirroring_state_desc IS NOT NULL The above query uses the sys.database_mirroring & sys.database catalog views, which can be used on principal side or mirror side instance to obtain the information and further to get witness related information you an use sys.database_mirroring_witness catalog view. A sample TSQL to get list the corresponding principal and mirror server names, database name, and safety level for all the mirroring sessions for which this server is a witness. SELECT principal_server_name, mirror_server_name,                                database_name, safety_level_desc FROM   sys.database_mirroring_witnesses   ENDPOINT is the key in database mirroring, as the connection management between SQL Server instances (since 2005 version) is based on endpoints. BOL defines it as "An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires its own, dedicated database mirroring endpoint. All mirroring connections on a server instance use a single database mirroring endpoint. This endpoint is a special-purpose endpoint used exclusively to receive database mirroring connections from other server instances".  As the endpoint uses a specific port the usual catalog view of sys.database_mirroring_endpoints will not maintain the information, it is in sys.tcp_endpoints view, so here is another TSQL to join both of these catalog views to obtain information about endpoints: SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,                                t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,                                e.connection_auth_desc FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON     e.endpoint_id = t.endpoint_id   These a just monitoring aspects of a database mirroring pair, but there is much to monitor the performance of database mirroring, SQL Server provides a System Monitor performance object (aka PERFMON) for SQLServer:Database Mirroring related counters on each partner (principal and mirror). The Databases performance object provides some important information as well, such as throughput information (Transactions/sec counter). Here is the reference from Technet article about the important counters that are need to watch if you feel that performance has been degraded withint your DBM enviornment. On the principal: ·         Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second. ·         Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server. ·         Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0. ·         Transactions/sec: The transaction throughput of the database. This counter is in the Databases performance object. ·         Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance. This counter is in the Databases performance object. ·         Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks. On the mirror: ·         Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second. ·         Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward. ·         Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror.   Lastly you can take help of event notifications (trace) by using SQL Trace which helps recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools by accessing SQLtrace or SQL Profiler. The 2 events are important for DBM related environment such as Database Mirroring state change which indicates the mirroring state of mirrored database changes and Audit Database Mirroring login event class which reports the audit messages related to DBM transport security. Later on this series I will cover the troubleshooting aspects of database mirroring environment.    

Find out parameters of the stored procedure
The following methods can be used to know the parameter informations of the particular stored procedure Let us consider the following procedure create procedure testing ( @id int, @user_name varchar(100), @join_date datetime ) as select @id as userid, @user_name as username,@join_date as join_date To know the parameter informations, use one of the following methods Method 1 : Use sp_help system procedure exec sp_help testing Look at the second resultset Parameter_name Type Length Prec Scale Param_order Collation ------------------ ------- ----------- --------- --------- ----------- ---------- @id int 4 10 0 1 NULL @user_name varchar 100 100 NULL 2 SQL_Latin1_General_CP1_CI_AS @join_date datetime 8 23 3 3 NULL Method 2 : Use information_schema.parameters view select * from information_schema.parameters where specific_name='testing' Method 3 : Use sp_sproc_columns system procedure exec sp_sproc_columns testing Execute methods 2 and 3 also and see the resultset.

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way
Well-written unit tests should succeed only when the module being tested meets the requirements completely.If the test fails, it can do much better than just indicate failure. If the test has managed to provide a clear and comprehensive report of what is wrong, that can save us a lot of time troubleshooting. We shall see a few examples soon. This post continues the series on unit testing, the previous posts are How to Benefit from Unit Testing T-SQL: choosing what not to test How to Benefit from...(read more)

Working with the SQL Server Powershell Provider and Central Management Servers from Powershell
I am a big fan of the Central Management Server setup that exists in SQL Server 2008, as well as how you can leverage this for the Enterprise Policy Management Framework that Laura Rubbelke ( Blog | Twitter ) created on Codeplex.  Today one of my DBA’s asked a question about how to leverage the list of servers in the CMS through PowerShell and I had to double back on my own code because in PowerGUI the SQL Server Powershell provider is not natively registered so I had to write some additional...(read more)

TSQL Beginners Challenge 17- Working with Employee hierarchical structures
Submit A Solution This challenge is more about processing HIERARCHIES. The problem is all about identifying all the employees directly or indirectly to whom the given Employee reports to. The challenge is to write a query that can take a Employee Id as a parameter and list all the employees to whom the Employee is reporting, directly or indirectly. Source Data EmpRecord ---------------------------------------------------------------------------- <Employees> <Employee EmpId="1" EmpName="Niladri Biswas" /> <Employee EmpId="2" EmpName="Arina Biswas" ReportsTo="1" /> <Employee EmpId="3" EmpName="Deepak Kr. Goyal" ReportsTo="1" /> <Employee EmpId="4" EmpName="Sachin Srivastav" ReportsTo="1" /> <Employee EmpId="5" EmpName="Ranjit r" ReportsTo="2" /> <Employee EmpId="6" EmpName="Priyanka Sarkar" ReportsTo="2" /> <Employee EmpId="7" EmpName="Priyanka Das" ReportsTo="5" /> <Employee EmpId="8" EmpName="Ravi Varman" ReportsTo="5" /> <Employee EmpId="9" EmpName="Jiya Devi" ReportsTo="3" /> <Employee EmpId="10" EmpName="Sharmistha Saha" ReportsTo="3" /> </Employees> If the parameter contains 9 the query should return: Expected Results Level Path Hierarchy ----- ----------------------------------------- ----------------- 0 Jiya Devi/Deepak Kr. Goyal/Niladri Biswas Niladri Biswas 1 Jiya Devi/Deepak Kr. Goyal Deepak Kr. Goyal 2 Jiya Devi Jiya Devi Your query should ideally look like the following: DECLARE @EmpID VARCHAR(20) SELECT @EmpID = 9 -- Your query here: Script Use the following script to generate the sample data DECLARE @Employees TABLE(EmpRecord XML) INSERT INTO @Employees SELECT '<Employees> <Employee EmpId = "1" EmpName = "Niladri Biswas"/> <Employee EmpId = "2" EmpName = "Arina Biswas" ReportsTo="1"/> <Employee EmpId = "3" EmpName = "Deepak Kr. Goyal" ReportsTo="1"/> <Employee EmpId = "4" EmpName = "Sachin Srivastav" ReportsTo="1"/> <Employee EmpId = "5" EmpName = "Ranjit r" ReportsTo="2"/> <Employee EmpId = "6" EmpName = "Priyanka Sarkar" ReportsTo="2"/> <Employee EmpId = "7" EmpName = "Priyanka Das" ReportsTo="5"/> <Employee EmpId = "8" EmpName = "Ravi Varman" ReportsTo="5"/> <Employee EmpId = "9" EmpName = "Jiya Devi" ReportsTo="3"/> <Employee EmpId = "10" EmpName = "Sharmistha Saha" ReportsTo="3"/> </Employees>' SELECT * FROM @Employees Notes The solution should be a single query that starts with a "SELECT", "WITH" or ";WITH" . The program should run in SQL SERVER 2005+. Column names should respect the desired output shown. Result must be sorted in ascending order of Level. Hierarchy column should display parents starting from the topmost position to the child sought for. Use this forum to discuss your questions related to TSQL Beginners Challenge 17. To be able to post questions in the forum, you need to be a member of the group TSQL Beginners Challenges. Click here to subscribe to the group. Submit A Solution About the author

New util proc: sp_tableinfo
Some of you might use my sp_indexinfo to easily get lots of details about the indexes in a database. I added an sp_tableinfo with info rolled up to table level. This allow to quickly see the largest tables in a database and things like that. Check it out at http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp ....(read more)

Twitter Input Adapter
We are delighted to see the StreamInsight developer community shaping up. Johan Åhlén, one of the most active members of this group, has published a Twitter input adapter last week, featured on SQLServerCentral. It uses the Twitter API to receive near-real time updates from the “sample method” and “filter method” APIs (the full firehose API of all public status updates is only accessible to users with high access levels). Converting tweets into point events, the adapter opens up a wide range of semantically rich analytics over Twitter data, expressed in declarative StreamInsight LINQ. Johan has published the sources of his adapter on the StreamInsight CodePlex site. Keep up the great work, Johan! SQLServerCentral article (free account required) CodePlex download Regards, The StreamInsight Team

SQL Server 2008 R2 Idea: Data Profiling of any table in SSMS
I love the data profiling task that was added to SSIS in SQL Server 2008. It allows you to get a profile of the data in a table - this includes things like what are potential candidate keys, what length are the data values in each column, what percentage of rows in a table are null in each column, what is the distribution of values in the data, etc. etc. If you haven't used it, I'd suggest taking a look at it. It's a real hidden gem in the product. However, I think they missed the #1 place that this...(read more)

Recap Share- & PerformancePoint First Deployment
Happily, at my office we just rolled out our first MS/BI-stack dashboard project last week, using SharePoint 2010, PerformancePoint, Analysis Services, and SQL Server 2005 and 2008, on Windows 2008 R2. It was quite bumpy - needlessly so in some spots - but it works, looks great and offers excellent information to the business. Most important to me personally is that we now have all the infrastructure in place for the delivery of content through this channel moving forward, and it overcame a failed...(read more)