Piotr Rodak: if datepart(dw, getdate()) in (6, 7) use pubs;

Moving blog… again

noreply@blogger.com (Piotr Rodak) — Sat, 21 Aug 2010 21:36:00 +0000

It’s been a while since I have written here. The main reason is that it is summer and I was on vacations, and not very keen on sitting in front of the computer.

The other reason is that I have decided to move the blog again. The visibility of this blog unfortunately is not great, even though I think I wrote a few posts which are pretty interesting – so say those who read them :). I made quite a bit of effort to increase the visibility of the blog with great help of Brent Ozar. Unfortunately, the main problem is still practical absence of my posts in the Google search results. This makes my posts life very short and I feel they could do better.

Thankfully Adam Machanic sent me email if I would consider setting up blog on the SQLBlog.com. I must say I found this very rewarding, because bloggers on SQLBlog.com belong to the finest experts of the SQL Server. I decided to move my blog to SQLBlog.com, I hope the content I will publish will serve more developers looking for knowledge. So please repoint your RSS readers to my new blog: http://sqlblog.com/blogs/piotr_rodak

See you there!

With head in the clouds

noreply@blogger.com (Piotr Rodak) — Thu, 22 Jul 2010 07:15:00 +0000

I write this post inspired by excellent article of Jeremiah Peschka (blog) who wrote about the future of databases. I think that this is a good topic to write something I hope meaningful about.

Jeremiah made several good observations about the nature of current database designs and technologies and what it seems to be an upcoming technology of future – the name-value stores.

I am sometimes faced with opinions of sort “Dude, these relational databases are sooo gone!”. Are they? Let’s see, what good “no sql”, cloud database should have:

Obviously trendy key-value collection. This is implemented usually using hash table, where key is transformed to certain numeric value, and this value is used to index table that holds actual data values. To add a value, you have to calculate hash of the key, resolve conflict and insert the value into appropriate index of the table. The other option is to use tree data structure, where nodes are balanced according to the key value, and each node contains actual data. To access data, you have to traverse tree based on the value of the key. When you insert a value, you have to rebalance tree (if you use balanced tree) to maintain predictable access time to the data.

In some cases you want to retrieve collection of objects within a specific order. This can be done in two ways – either you sort data each time the collection is requested, or you maintain doubly linked list of keys that enables you to move between nodes in specific order. Ah these old good *prev and *next pointers. The first option gives you overhead each time you retrieve the data, but doesn’t necessarily significantly consume memory. The second option requires additional memory and management for adding or removing values. If you change key value, the list has to be updated so the elements are returned in proper order.

Believe it or not, even clouds touch the ground sometimes. Even the cloud data have to be persisted sometimes. How do you do it? You can use either proprietary binary file structure for performance or xml, that’s pretty much it. When you write the data, you have to ensure that some other process is not writing it as well – concurrency control. You have to ensure that when data are written, other processes can read consistent view of data. There’s another thing – you don’t always write all data you have in memory. You should be able to persist only changes. To identify changed values on disk and update or insert or delete them accordingly. If you want to keep performance, these disk data structures have to be optimized for searching – here we have indexes.

All these points above are key factors of a good “no sql” database. All these points are also implemented in SQL Server. You have balanced trees for indexes, hash tables for plan cache, key indexed tables for row offsets on page level, linked lists of pages in indexes, sorting, data persistence technology optimized for concurrency and speed. You can, if you want to access data through OR mapper so you deal with collections, attributes etc.

So, what are the differences? The main and most important difference is the replication. SQL Server insists on storing changed data to disk first and then replicates it, the cloud data stores do the opposite – they replicate data to neighbouring nodes of the network before persisting it. This change of approach was made possible by development of technology, growing speeds of networks and capacities of memory installed on servers. It may be cheaper and faster to throw a few gigabytes over the fast network to the other side of the world than to store them in local SAN. The idea relies on the statistics – it is very unlikely that all of a sudden servers located in several places in the world will fail at the same time. SQL Server approach stems from old times, where servers were mostly standalone machines which had to ensure that no matter what, data will be available after disaster recovery.

The cloud applications often use traditional databases as their back ends in various locations for the reasons I described above. Traditional databases offer transactional and safe way of persisting data, what has to happen at some point of time.

I hope that this article will shed some light on internal implementation of cloud databases, especially the fact that all data structures and algorithms were invented may years ago, sometimes 50 years ago. It’s the power of technology and money what made cloud data stores possible, nothing else. There is nothing new under the sun.

Technorati Tags: cloud,cloud computing,SQL Server,algorrithms,data structures

Disabling audit triggers

noreply@blogger.com (Piotr Rodak) — Fri, 16 Jul 2010 20:38:00 +0000

This is a follow up to the posts about audit triggers. I wrote about them some time ago, on my previous blogging platform. The first post discussed COLUMS_UPDATED() function that is very useful in triggers if you want to find out which columns are affected by DML operation. Second post showed how you can create an audit framework that subsequently is used to log what happens to data when users modify it.

Now, there are situations, when you want to make changes to a table, but you don’t want to have these operations logged. And I am not talking about criminal activities – but for example, a table is populated daily by ETL process which updates or inserts thousands of rows. Sometimes it doesn’t make sense to log all these operations – after all triggers incur some performance degradation on the database.

There are several ways to disable a trigger. First, you can use disable trigger statement This approach has one drawback – when you disable trigger, it is disabled for every user who modifies the data. So you may loose audit entries while your ETL is running. In some scenarios it’s not an option.

Alternatively you can implement trigger to check for certain condition and decide whether DML operation should be audited or not. There are several ways of doing this. Two are described by Itzik Ben-Gan in his excellent Inside SQL Server 2005 - T-SQL Programming. Triggers can check if a table with specific name exists in the temp database or use session context. The disadvantage of these options is that you have to modify code external to triggers, for example your stored procedures or batches.

You can check if there is certain entry in a configuration table. For example you can have table Audit.tIgnoreUsers which will contain user names for which you don't want to audit. Then, there’s a simple query to decide if the trigger should proceed or not:

   1: if(exists (select 1 from Audit.tIgnoreUsers where UserName = suser_name()))   2:     return;

Alternatively, you can check if particular user belongs to particular role. To do this, you have to create role first and add user that you want to exclude from auditing.

   1: if not exists (select 1 from sys.database_principals where name = 'ExcludeFromAudit' and type='R')   2:         exec sp_executesql N'create role ExcludeFromAudit authorization dbo'   3:     4: go   5:     6: exec sp_addrolemember 'ExcludeFromAudit', 'ETLLogin'   7:     8: go   9:    10: raiserror('Logins skipped in auditing: ', 10, 1) with nowait;  11:    12: exec sp_helprolemember 'ExcludeFromAudit'

Then, in the audit trigger you just add code somewhere on the top of the trigger:

   1: if(is_rolemember('ExcludeFromAudit') = 1)   2: begin   3:     --print 'Audit skipped for user ' + suser_name()   4:     return   5: end

And that’s it. I find this way easier to manage, as typically users do not have rights to modify roles. The good practice is to have separate login for ETL from other logins, so you can audit every action that for example front end users perform, but the ETL stays almost unaffected.

Please note that if you happen to be sysadmin and want to test the is_rolemember on a user defined role, it will always return 0. Quite confusing, I know. There is a Connect item for IS_MEMBER function which behaves in the similar way, by design.

Technorati Tags: SQL Server,tips and tricks,code,programming,T-SQL

INSTEAD OF triggers - Using views in SSIS

noreply@blogger.com (Piotr Rodak) — Wed, 07 Jul 2010 23:16:00 +0000

This is the last post in the series about updateable views. The previous posts (Updateable views – how to use them and INSTEAD OF triggers on views) described the mechanics of the views and triggers that you can use. I haven’t answered yet to question where views and triggers can be useful?

The common issue that developers meet when developing SSIS packs is that there is not ‘built-in’ destination transformation that would insert rows when they don’t exist in the destination table and update rows that exist. Usually developers use a lookup and split the dataflow to OLEDB destination and OLEDB command. Adam Haines (blog) wrote a very nice post about using INSTEAD OF triggers on views to improve this solution.

The other scenario I see quite interesting is logging rows that fail to be inserted into destination table. Usually failure occurs because of constraint violations – like invalid value of the column on which there is a CHECK constraint or NULL where it shouldn’t be.

BCP allows for output of invalid rows into a separate file. The same sometimes is used in SSIS solutions. You can create File destination to save rows which fail to be input into the table. I see a few issues with this approach. If you use CSV file, you loose distinction between NULL and empty fields. If you use raw binary file – you can’t really investigate its contents. There is also all this hassle with destinations, file locations, disk space, access etc.

I’ve been working with SSIS for a few years and in the past we came up with idea to create custom component, written in C# that would be used in script components of SSIS at the end of data flows, connected with final destinations with red arrows. This worked pretty well. In ProcessInputRow handler the row would be passed to the component which would create XML from it using reflection. The XML would subsequently be saved in a table in database, making it easy to query and troubleshoot.

Well, this approach has a few drawbacks as well: you need to deploy and maintain component’s code, the error handling is processed row by row, and reflection does not improve performance, especially when upstream data were totally wrong and all rows had to be redirected to the audit output.

I recently began new project, and had to approach the question of audit failed rows once more. I didn’t want to use the component due to these problems I wrote above. I realized that I can use SQL Server to convert the data from the pack into XML. Let’s look how it works.

First, let’s create testing environment. We will create package that moves data from one database to another. The following snippet creates source and destination databases and table that contains the source data.

        1: use master   2: go   3: create database SSISTestSourceDB   4: go   5: create database SSISTestDestinationDB   6: go   7:     8: use SSISTestSourceDB   9: go  10: create table tSource(Col1 int, Col2 int null, Col3 varchar(20), Col4 datetime)  11: go  12: insert tSource(Col1, Col2, Col3, Col4)  13: values   14: (1, 1, 'Value1', CURRENT_TIMESTAMP),  15: (2, null, 'Value1', CURRENT_TIMESTAMP), --for null value error  16: (3, 3, 'DB', CURRENT_TIMESTAMP), --for min length check constraint violation  17: (4, 4, 'Value1', CURRENT_TIMESTAMP),  18: (5, 5, 'Value1', CURRENT_TIMESTAMP),  19: (6, 100, 'Value1', CURRENT_TIMESTAMP), --for out of range check constraint violation  20: (7, 7, 'Value1', CURRENT_TIMESTAMP),  21: (8, 8, 'Value1', CURRENT_TIMESTAMP),  22: (9, 9, 'Value1', CURRENT_TIMESTAMP),  23: (10, 10, 'Value1', CURRENT_TIMESTAMP), --two tens for pk violation on destination  24: (10, 11, 'Value1', CURRENT_TIMESTAMP),  25: (11, 12, 'Value1', CURRENT_TIMESTAMP)  26:    27: go

Now let’s create table in the destination database:

   1: use SSISTestDestinationDB   2: go   3: create schema Staging authorization dbo   4: go   5: create table Staging.tDestination    6: (   7:     Col1 int not null constraint PK__1 primary key clustered,   8:     Col2 int not null constraint CHK__Col2_Range check (Col2 < 30),   9:     Col3 varchar(20) not null constraint CHK__MinLenGreaterThan2 check (len(Col3) > 2),  10:     Col4 datetime not null  11: )   12: go

The Audit table will store the audit data:

   1: --table that will accept errors from the pack   2: create table Audit.tAuditETLErrors   3: (   4:     [ErrorCode] [bigint] NULL,   5:     [ErrorColumn] [bigint] NULL,   6:     [ErrorDescription] [nvarchar](1000) NULL,   7:     [ErrorData] [xml] NULL, --actual row data   8:     [UpdateDate] [datetime] not null constraint DF_UpdateDate1 default (getdate())   9: )  10: go

As you see, the table is pretty simple: ErrorCode and ErrorColumn are derived directly from data flow in the SSIS package, ErrorDescription is added to retrieve more meaningful error description and ErrorData is XML column that contains serialized audit row.

Now the main part of the idea: a view with an INSTEAD OF INSERT trigger on it.

   1: --create error handling view and trigger   2: create view Audit.vDestinationSSISError   3: as   4: select convert(bigint, 0) ErrorCode,    5:         convert(bigint, 0) ErrorColumn,    6:         convert(nvarchar(1000), N'') ErrorDescription,    7:         dst.*    8:     from Staging.tDestination dst   9:     where 1 = 0 --this view doesn't have to return any data  10: go  11: create trigger Audit.trgins_vDestinationSSISError on Audit.vDestinationSSISError  12: instead of insert  13: as  14: begin  15:     set nocount on  16:     --insert new row to audit table. actual data are converted to XML  17:     insert Audit.tAuditETLErrors (ErrorCode, ErrorColumn, ErrorDescription, ErrorData, UpdateDate)  18:     select ErrorCode, ErrorColumn, ErrorDescription,   19:     (select * from inserted   20:         for xml raw, elements xsinil, root('etl_error')), current_timestamp  21:     from inserted  22: end  23: go

The view doesn’t return any data, because in this case it is used only for trigger. The view has to expose ErrorCode, ErrorColumn and ErrorDescription columns, plus all columns of the destination table if you want to audit all columns.

The trigger uses select… for xml query to serialize incoming data into xml stream that is inserted eventually to ErrorData column.

The package is built in pretty simple way. There is OLEDB source, OLEDB destination – using ‘fast’ bulk load option. Then there is OLEDB ‘Normal’ destination – for batches which were rolled back because of errors in the ‘Fast’ destination.

Then, for rows that didn’t make it to the destination table, redirection to additional destination is made, to the view destination:

The script component before the final destination adds ErrorDescription to the data flow:

There’s a catch: you better use ADO.NET destination in the package because OLEDB destination is unaware of the triggers on the views. While you can configure OLEDB destination to use view, the rows will fail to be inserted because of the same reasons they weren’t inserted in the fist place. You can solve it by creating a table with exactly the same schema as the destination table but without any constraints. This approach is a bit awkward and you have to maintain tables in sync. I found it easier to use ADO.NET destination. If you use connection string stored in package configurations, you just have to be sure that you use connection string for .NET for this destination, otherwise it will not work.

You map the columns the normal way:

This is what you’ll see when you run the package:

From 12 rows read from the source, three were inserted using bulk insert, and nine went to the ‘Slow’ destination. There, rows were inserted row by row, and rows which failed were redirected to the error output – you see there were four of them. Then the script component adds ErrorDescription column and rows are inserted into the view. When you select from the Audit.tAuditETLErrors table you will see all four of them:

I find this setup pretty useful, I wonder what do you think.

I attach the test script and the test SSIS project so you can straight go to the fun part.

Technorati Tags: SQL Server 2008,SQL Server 2005,T-SQL,TSQL,SQL,programming,Tips and tricks

INSTEAD OF triggers on views

noreply@blogger.com (Piotr Rodak) — Sun, 27 Jun 2010 23:07:00 +0000

Last time I wrote about updateable views – a feature that is not considered by many database architects and developers when they implement data interface. The updateable views have some limitations what makes them useful in only specific situations. Two most important limitations in my opinion are that you cannot update columns from more than one table in single update statement and that any derived or calculated columns cannot be directly updated.
Luckily, there is a way to bypass these limitations by using INDSTEAD OF triggers on views. The triggers are fired as the name implies instead of DML statement, so you have to implement effective operation within the trigger. If the trigger is empty, no update, insert or delete will be made.
Let’s look at Person.vContact2 view again. The view returns columns ContactId, NameStyle, Full Title, FirstName, MiddleName, LastName and several others. The Full Title column is a column that returns custom information depending on the underlying Title column from the Person.Contact table. You cannot update the Full Title column directly, because it doesn’t exist in the Person.Contact. If you want to change the information returned by the view, you have to modify the Title column in the table. Of course, you can update the table directly or via stored procedure and in most cases this would be the preferred way, but in some cases updating the view has its benefits. You may also have a requirement that the tables must not be updateable directly and the views are the only interface you can use to modify the data.
So, to change the Full Title column in the view, you have to modify the Title value appropriately. As you remember, the definition of the view is as follows:

   1: ALTER view [Person].[vContact2]

   2: as

   3: select ContactID, NameStyle,

   4: case when Title = 'Mr.' then 'Very long title 1'

   5:     when Title = 'Ms.' then 'Very long title 2'

   6:     else 'Other title' end [Full Title],

   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate

   8: from Person.Contact

Lines 4-6 contain definition of the Full Title column. In my AdventureWorks database, ContactID has ‘Other title’ returned by the view. I want to change it to ‘Very long title 1’.

You can create triggers for INSERT, UPDATE and DELETE statements. One of the ways is to create one trigger for all three types of operation, the other is to create a separate trigger for each of types separately. The latter approach simplifies logic within the trigger a bit, on the expense of maintainability – you have three database objects to worry about instead of one.

This is sample trigger that is fired for every DML operation against the view:

   1: create trigger Person.trgVContact2 on Person.vContact2

   2: instead of insert, update, delete

   3: as

   4: begin

   5:     print 'Trigger called'

   6: end

Now let’s call an update on the view:

   1: select * from Person.vContact2 where ContactId = 4

   2: update Person.vContact2 set LastName = 'NoName' where ContactId = 4

   3: select * from Person.vContact2 where ContactId = 4

Nice. The trigger was fired as you can see in the output. But, if you look at the LastName column, it was not changed. This is because this is an instead of trigger and it replaces the original operation. To actually update the column, you have to implement the trigger in more useful way:

   1: if exists (select 1 from sys.objects where object_id = object_id('Person.trgVContact2'))

   2: drop trigger Person.trgVContact2

   3: go

   4: create trigger Person.trgVContact2 on Person.vContact2

   5: instead of insert, update, delete

   6: as

   7: begin

   8:     if (@@rowcount = 0)

   9:     begin

  10:         print 'No rows matching criteria'

  11:         return

  12:     end

  13:     if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

  14:     begin

  15:         update contacts set LastName = inserted.LastName

  16:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId

  17:     end

  18:     else if exists(select 1 from inserted) --insert operation

  19:     begin

  20:         return; --TODO: implement with useful logic

  21:     end

  22:     else --delete operation

  23:     begin

  24:         return; --TODO: implement with useful logic

  25:     end

  26: end

As you see, this trigger allows for updating only LastName column. This column this column is exposed by the view without any modifications. On a side, did you know that triggers are fired even when no rows are affected? This was a surprise for me a few months ago. You can prevent potentially costly code from running if you check if there is anything to process. Lines 8-12 show how it can be done.

Ok, what about Full Title and other columns? This is the modified code for update operation only:

   1: if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

   2:     begin

   3:         if update(PasswordHash) or update(PasswordSalt)

   4:         begin

   5:             print 'Password data changes are not permitted'

   6:             return

   7:         end

   8:         update contacts

   9:             set NameStyle = inserted.NameStyle,

  10:                 Title = case inserted.[Full Title]

  11:                             when 'Very long title 1' then 'Mr.'

  12:                             when 'Very long title 2' then 'Ms.'

  13:                             else contacts.Title --no change in this case

  14:                         end,

  15:                 FirstName = inserted.FirstName,

  16:                 MiddleName = inserted.MiddleName,

  17:                 LastName = inserted.LastName,

  18:                 Suffix = inserted.Suffix,

  19:                 EmailAddress = inserted.EmailAddress,

  20:                 EmailPromotion = inserted.EmailPromotion,

  21:                 Phone = inserted.Phone,

  22:                 ModifiedDate = current_timestamp

  23:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId

  24:     end

Triggers offer you opportunity to validate what columns are updated. In certain scenarios, you don’t want users to be able to modify sensitive data. This can be achieved using triggers for example. In lines 3-7 of the above script you can see code preventing update operation on PasswordSalt and PasswordHash operations.

Ok, so far, so good. Triggers on views are one of the methods of implementing logic required to update data. However usually it is better to do it using stored procedures. There are scenarios though when triggers on views give you quite interesting ways of implementing ETL. More about this in next post.

Technorati Tags: sql server,programming,trigger,DDL,DML,t-sql,code

Updateable views – how to use them

noreply@blogger.com (Piotr Rodak) — Mon, 21 Jun 2010 22:29:00 +0000

This post is first part of miniseries that discusses ways of updating data in views. In this post I will discuss updateable views, in the second part there will be short overview of INSTEAD OF triggers and interesting implications they offer.

Note: All examples in this post are made using AdventureWorks sample database, which you can download from CodePlex page.

With simple views selecting just from single table, with no derived columns the query engine knows how to translate insert or update statement on view to appropriate operation on underlying tables, and no trigger is required. The following script shows an example of such behaviour:

   1: select * from Person.vContact1 where ContactID = 1

   2: update Person.vContact1 set MiddleName = 'S.' where ContactID = 1

   3: select * from Person.vContact1 where ContactID = 1

The query engine translates update statement against the view to an update statement against the underlying table, and if you look at the execution plan of the update statement, you’ll see there is no difference to an update statement against the bare table:

If the view is more complex, the query engine doesn’t know how to execute the operation and the update fails. There may be several different error messages, depending on the type of the error. For example, if you have calculated column in your view, you cant update this column and obviously you can’t insert rows into the view. The following view returns more or less the same information as the Person.vContact1, but it replaces original value of MiddleName column with custom string:

   1: ALTER view [Person].[vContact2]

   2: as

   3: select ContactID, NameStyle,

   4: case when Title = 'Mr.' then 'Very long title 1'

   5:     when Title = 'Ms.' then 'Very long title 2'

   6:     else 'Other title' end [Full Title],

   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate

   8: from Person.Contact

If you try to update the Full Title column, the update will fail:

   1: select * from Person.vContact2 where ContactID = 1

   2: update Person.vContact1 set [Full Title] = 'Some other title' where ContactID = 1

   3: select * from Person.vContact2 where ContactID = 1

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Full Title'.

This is obviously reasonable: Full Title column is calculated at runtime, when the view is executed and cannot be altered in any way because there is no storage related with this column – this column doesn’t exist in the table. You can however update other columns in this view, because they are bound directly to columns in the table.

   1: select * from Person.vContact2 where ContactID = 1

   2: update Person.vContact1 set MiddleName = 'D.' where ContactID = 1

   3: select * from Person.vContact2 where ContactID = 1

In some cases views return data from more than one table. Let’s examine such case. The following script updates City name in one of the tables that comprise the Purchasing.vVendor view:

   1: select * from Purchasing.vVendor where VendorId = 1

   2: select * from Person.Address where AddressLine1 = '683 Larch Ct.'

   3: update Purchasing.vVendor set City = 'Buenos Aires' where VendorId = 1

   4: select * from Person.Address where AddressLine1 = '683 Larch Ct.'

   5: select * from Purchasing.vVendor where VendorId = 1

When you run the above script you’ll notice that query engine was smart enough to update only appropriate row in the Person.Address table. Note also that the view,although joins eight tables, uses inner joins only and the query engine is able to determine exact range of rows to update in each participating table.

There are some other conditions that have to be met to make a view updateable, for example, when you run an update query, you have to modify only columns from one base table at a time. The following query fails:

   1: update Purchasing.vVendor

   2:     set City = 'Buenos Aires',

   3:     MiddleName = 'M.'

   4:     where VendorId = 1

Msg 4405, Level 16, State 1, Line 1
View or function 'Purchasing.vVendor' is not updatable because the modification affects multiple base tables.

Other conditions include (after MSDN):

The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
- An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
- A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

This will conclude first part of the series. In the next post I will write about triggers on views and their possible usage.

Technorati Tags: TSql,t-sql,SQL Server,CREATE VIEW,INSTEAD OF,AdventureWorks,Execution plan,code,programming,DDL

Things I like – generate DROP .. CREATE scripts

noreply@blogger.com (Piotr Rodak) — Wed, 16 Jun 2010 19:53:00 +0000

I will be writing about things I like from time to time. That is SQL Server things - other than beer, good books and company.
For today, one small thing but how useful. In SQL Server Management Studio 2008 you can script objects as DROP and CREATE in one go:

This is very useful for us, as we store all database objects in our SVN as DROP..CREATE scripts. Using this feature saves quite a bit of time each time we want to update the source control.
I noticed an interesting behavior - if you script your table this way, all constraints defined in the table are scripted with drop statements before the actual DROP TABLE statement is included. This is quite handy as you can easily modify this script to change constraint names or definitions without having to script them separately.

Technorati Tags: SQL Server,tsql,t-sql,code,tips and tricks

Coding practices - Call your constraints

noreply@blogger.com (Piotr Rodak) — Sun, 13 Jun 2010 22:59:00 +0000

I wrote a few weeks ago about unit tests and good practice of naming your constraints. I think that most of the developers are used to call primary keys and unique constraints, sometimes foreign keys. But in most of the code I’ve seen the CHECK and DEFAULT constraints remain unnamed. You might wonder, why bother? The answer is that as long as you don’t have to drop the constraints, everything is OK – apart from cryptic error messages if the constraints are violated. The problem begins if for some reason you need to drop the constraint – for example to change range of the allowed values or default value of a column. The other scenario is also painful – if you want to compare two databases and you script out objects with constraint names. If you let SQL Server to name your constraints, each database you create from your scripts is different. And you have different error messages in production than in UAT for example. Not a very good idea for troubleshooting. Let’s suppose you create a table for storing order summaries.

   1: create table OrderSummary

   2: (

   3: OrderID int not null,

   4: OrderValue decimal(10,2) not null,

   5: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

   6: OrderDate datetime not null default getdate()

   7: )

Looks nice. But to drop the constraint on the OrderCurrency column, you have to use dynamic sql:

   1: select * from sys.check_constraints

   2: declare @sql nvarchar(200)

   3: select @sql = N'alter table OrderSummary drop constraint ' +

   4:         quotename((select name from sys.check_constraints

   5:             where parent_object_id = object_id('OrderSummary')))

6:

   7: --print @sql

   8: exec sp_executesql @sql

Please note that the above script works only if you have one unnamed check constraint. If you have more constraints, you have to write more complex code. Not a very nice option if you want to have some control on which constraints you want to drop and which are to stay. Now if you run the following script, you will see that each time you create the table, names of constraints are different:

   1: use master

   2: go

   3: create database devDB

   4: go

   5: use devDB

   6: go

   7: --simulate various test objects creating and dropping in DEV database

   8: create table OrderSummaryTest

   9: (

  10: OrderID int not null,

  11: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  12: OrderDate datetime not null default getdate()

  13: )

  14: drop table OrderSummaryTest

15:

  16: go

17:

  18: create table OrderSummary

  19: (

  20: OrderID int not null,

  21: OrderValue decimal(10,2) not null,

  22: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  23: OrderDate datetime not null default getdate()

  24: )

  25: go

26:

  27: select convert(varchar(10), db_name()) [test],

  28:     name from sys.check_constraints

  29:     where parent_object_id = object_id('OrderSummary')

30:

  31: go

32:

  33: create database prodDB

34:

  35: go

36:

  37: use prodDB

38:

  39: go

40:

  41: create table OrderSummary

  42: (

  43: OrderID int not null,

  44: OrderValue decimal(10,2) not null,

  45: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  46: OrderDate datetime not null default getdate()

  47: )

48:

  49: go

50:

  51: select convert(varchar(10), db_name()) [test],

  52:     name from sys.check_constraints

  53:     where parent_object_id = object_id('OrderSummary')

54:

  55: go

56:

  57: ----cleanup

  58: use master

  59: go

  60: drop database devDB

  61: drop database prodDB

  62: go

As you see, the constraint names are different and UGLY. Note that I simulated object creation and destruction in devDB, because if you comment lines 7 – 14, you may get the same object ids for the constraints. But this never happens in real life. If you want to avoid such problems and make your and your dba’s life easier, give your constraints names, like in the following script:

   1: create table OrderSummary

   2: (

   3: OrderID int not null,

   4: OrderValue decimal(10,2) not null,

   5: OrderCurrency char(3) constraint CHK_OrderCurrencyRange check (OrderCurrency in ('EUR', 'USD')),

   6: OrderDate datetime not null constraint DF_OrderDate default getdate()

   7: )

This way, to drop the default constraint you just have to call:

   1: alter table OrderSummary drop constraint [DF_OrderDate]

No dynamic SQL required, and code is 100% same in production and in DEV. To sum it up, if you get the habit of assigning names to ALL your constraints, your code will look better and others will be able to understand it faster, what is important when troubleshooting production issues.

Technorati Tags: tsql,SQL Server,t-sql,programming,script,tips and tricks

sp_addrolemember – implicit create user with problems

noreply@blogger.com (Piotr Rodak) — Thu, 10 Jun 2010 00:04:00 +0000

This post was to be about an interesting issue I encountered a few days ago. I will write about this issue in the future, but I tried to reproduce it on my home laptop and while doing so, I came across behaviour I wasn’t aware of.
As you may know, using application roles simplifies your life. To add an user to application role you use sp_addrolemember procedure. Working on the script to reproduce my permissions issue, I created user in database and assigned it to custom role. When you create user explicitly, the default schema of the user will be dbo.

   1: use master

   2: go

   3: create database TestSchemaDb

   4: go

   5: create login frank with password='StrongestPassword', check_policy=off, check_expiration=off

   6: go

   7: use TestSchemaDb

   8: go

   9: create role WrapRole

  10: exec sp_addrolemember 'db_ddladmin', 'WrapRole'

  11: exec sp_addrolemember 'db_datawriter', 'WrapRole'

  12: exec sp_addrolemember 'db_datareader', 'WrapRole'

  13: grant execute to WrapRole

14:

  15: go

  16: ---create user and add him to the role

  17: create user frank from login frank

  18: go

  19: exec sp_addrolemember 'WrapRole', 'frank'

  20: go

You can verify this with the following script:

   1: ---create schema, frank

   2: execute as user='frank'

   3: select user_name() as [I am]

   4: go

   5: ---create table, frank

   6: create table tTable (a int, b int)

   7: go

   8: insert tTable(a, b) values(1, 2)

   9: select * from tTable

  10: ---but, where is the table?

  11: select object_schema_name(object_id('tTable')) as [Table in this schema]

  12: go

  13: revert

  14: select user_name() as [I am]

  15: go

The select statement in line 11 will return dbo schema name.

I noticed that when you don’t create an user but instead call sp_addrolemember, the user will be created in database. I thought that this was always true until yesterday, when I found out that this is the case only if you have a Windows login and create an user with the same name as the login. So for user frank, if we drop him from the database and try to create him using sp_addrolemember, we’ll receive an error:

   1: ---now second scenario

   2: drop user frank

   3: drop table tTable

   4: go

   5: --nothing returned

   6: select * from sys.database_principals where name='frank'

   7: go

   8: --this fails, frank doesn't exist in the database

   9: exec sp_addrolemember 'WrapRole', 'frank'

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'frank' does not exist in this database.

For user that we add from a Windows login, everything works, at a first glance:

   1: ---third scenario

   2: create login [Amilo\Ola] from windows

   3: go

   4: --don't create user in database

   5: go

   6: -- call sp_addrolemember

   7: exec sp_addrolemember 'WrapRole', 'Amilo\Ola'

   8: go

I tried to connect as this user:

   1: ---create schema

   2: execute as user='Amilo\Ola'

   3: select user_name() as [I am]

   4: go

And I got error message:
Msg 916, Level 14, State 1, Line 3
The server principal "Amilo\Ola" is not able to access the database "TestSchemaDb" under the current security context.

Ok, so I granted the connect right to the user:

   1: grant connect to [Amilo\Ola]

The MSDN Help says that “if the new member is a Windows-level principal without corresponding user, new user is created, but may not be fully mapped to the login”. I wish I knew what “not fully mapped” means, besides that the user is created, but it doesn’t have the CONNECT right.
Now I was able to run the following script:

   1: ---create schema

   2: execute as user='Amilo\Ola'

   3: select user_name() as [I am]

   4: go

   5: ---create table

   6: create table tTable (a int, b int)

   7: go

   8: insert tTable(a, b) values(1, 2)

   9: select * from tTable

  10: ---but, where is the table?

  11: select object_schema_name(object_id('tTable')) as [Table in this schema]

  12: go

  13: revert

  14: select user_name() as [I am]

  15: go

If you run it, you will notice that the result of the query in line 11 will return Amilo\Ola equivalent for login you use for testing. The table will be, if you don't explicitly specify schema, located in default schema of the user. So, if you happen to create user with sp_addrolemember, the objects the user creates may not be in expected place. This may lead to additional lookups performed by db engine for queries which don’t explicitly specify schemas and possibly to access exceptions if for example procedure created by one user accesses table created by another.
There is another caveat of this behavior: If you try to drop user who owns a schema, you will get an exception:

   1: drop user [Amilo\Ola]

   2: go

Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.

You have to drop the schema first, and to do this, you have to transfer all objects in the schema to other location:

   1: drop schema [Amilo\Ola]

Msg 3729, Level 16, State 1, Line 2
Cannot drop schema 'Amilo\Ola' because it is being referenced by object 'tTable'.

To transfer objects to different schema you use ALTER SCHEMA statement.

   1: go

   2: alter schema dbo transfer [Amilo\Ola].[tTable]

   3: go

   4: drop schema [Amilo\Ola]

   5: go

   6: drop user [Amilo\Ola]

   7: go

Ok, this would conclude this post. In summary, you should check your scripts and databases and verify that the users are created with CREATE USER and with proper default schemas – it doesn’t always have to be the same schema as the users’s name.
Avoid implicit creation of users with sp_setapprole – this works only for Windows logins and you have to explicitly grant CONNECT right. Don’t use sp_adduser either – it will be removed in future version of SQL Server.

Objects created without explicit schemas will be created in user’s default schemas – and they may have pretty ugly names [Domain\Username].

You will run into various security issues when using these objects and when you try to drop users from database.

For completeness of the scripts on this page, here is the cleanup snippet:

   1: ---cleanup

   2: use master

   3: go

   4: drop database TestSchemaDb

   5: drop login frank

   6: drop login [Amilo\Ola]

   7: go

Technorati Tags: tips and tricks,SQL Server,T-SQL,programming

How to prepare sample data

noreply@blogger.com (Piotr Rodak) — Sun, 06 Jun 2010 22:50:00 +0000

Recently someone on one of SQL Server forums had a question how to prepare sample data so users can work on database restored from production. The requirement was that testers must not know the real data, names, addresses, emails and so on. On the other hand, users find very hard to work with totally random strings, like xwzr as first name, for example.
There are several tools on the market that help to prepare sample data. One of them is Visual Studio Database Edition, the other is SQL Data Generator from RedGate. I am sure there are many more.
The drawback of the above two excellent products is that they are not free. In some cases this is a major obstacle, because bosses are somehow not as likely to spend money for your tools (and toys) as you would like them to be.
So sometimes you have to implement sample data generator yourself. As it turns out, it is not too complicated (especially for simple cases).
Let’s assume that we want to replace all names in Person.Contact table in AdventureWorks database.
There are 19972 rows in this table in my database. I would like to replace all FirstName, MiddleName and LastName values with values that I prepare. Obviously, it is not so easy to come up with 20000 other names which don’t belong to Klingon language.
But if you think about this, 20000 is 50 x 40 x 10. If you have one fifty first names, forty last names and ten middle names, you can create 20000 unique combinations of these. You can easily find lists of names if you search, but for example this page contains all names that we might need to populate 20000 rows.
For this example, I created three tables, each for separate list of names:

   1: create table #RandomLastNames(RandomName nvarchar(50))

   2: create table #RandomFirstNames(RandomName nvarchar(50))

   3: create table #RandomMiddleNames(RandomName nvarchar(50))

I populated them using the names I found on the page I linked before. For middle names, I just typed in several initials and a NULL value, as NULLs are present in MiddleName column in Person Contact.

   1: insert #RandomMiddleNames (RandomName)

   2: select N'J.' union all

   3: select N'T.' union all

   4: select N'R.' union all

   5: select N'A.' union all

   6: select N'C.' union all

   7: select N'M.' union all

   8: select N'W.' union all

   9: select N'D.' union all

  10: select N'S.' union all

  11: select N'K.' union all

  12: select N'Z.' union all

  13: select NULL

To get number of combinations of the data you just select count from cartesian product of all three tables:

   1: select count(*) [No of combinations] from

   2: #RandomFirstNames cross join

   3:     #RandomLastNames cross join

   4:         #RandomMiddleNames

The rest is easy. To preview how data you have will be replaced you run this query:

   1: select cn.ContactID, cn.FirstName + N' ' + isnull(cn.MiddleName + N' ', N'') + cn.LastName OriginalData,

   2: (select top 1 RandomName from #RandomFirstNames where cn.ContactId = cn.ContactId order by newid()) FirstName,

   3: (select top 1 RandomName from #RandomMiddleNames where cn.ContactId = cn.ContactId order by newid()) MiddleName,

   4: (select top 1 RandomName from #RandomLastNames where cn.ContactId = cn.ContactId order by newid()) LastName

   5: into #DataMapping

   6: from Person.Contact cn

To actually update the data just run this update:

1:

   2: update Person.Contact set

   3: FirstName = b.FirstName,

   4: MiddleName = b.MiddleName,

   5: LastName = b.LastName

   6: from Person.Contact a inner join

   7:         #DataMapping b on a.ContactId = b.ContactId

And that’s it! You can modify the script I attach to suit your needs, you can modify other data the same way, like emails, address lines etc.

Sample code download here.

Technorati Tags: SQL Server,programming,code,tips and tricks

Save the time – use roles!

noreply@blogger.com (Piotr Rodak) — Wed, 02 Jun 2010 20:08:00 +0000

Some time ago I wrote about coding practices. I would like to follow this topic with a quick post about making the life easier.
If you happen to be a developer who also is responsible for managing development databases and access of other team members to the environment, you might find this tip useful.

Create role for developers and assign rights to it.

Usually you would want developers to be able to run DDL commands and also execute procedures and write and read data. I’ve seen many times that developers were given the db_owner role. This is in most cases too much. If you have specific custom role, you can easily adjust the security settings without having to browse through all users.

   1: use TestDB   2: go   3: create role [DBDeveloper] authorization dbo   4: go   5: grant execute to DBDeveloper   6: go   7: exec sp_addrolemember 'db_ddladmin', 'DBDeveloper'   8: exec sp_addrolemember 'db_datawriter', 'DBDeveloper'   9: exec sp_addrolemember 'db_datareader', 'DBDeveloper'  10: go

As you see above, I create role DBDeveloper and grant several rights to the role. Next, I just need to add users to the role to give them what they need.

   1: use master   2: go   3: create login [frank] from windows with default_language=us_english    4: go   5: use TestDB   6: go    7:    8: create user [frank] from login [frank] with default_schema=dbo   9: go  10: exec sp_addrolemember 'DBDeveloper', 'frank'  11: go

This little trick saves a lot of time and hassle, and if you save these scripts carefully, you can easily restore security on a database refreshed from production for example.

Technorati Tags: SQL Server,T-SQL,tips and tricks,programming

Hey there!

noreply@blogger.com (Piotr Rodak) — Sun, 30 May 2010 23:13:00 +0000

Hi everyone :)
This is my first post here, though not first post in my life. My old blog is here: http://sqlblogcasts.com/blogs/piotr_rodak/

I decided to move my blog to have more flexibility and perhaps slightly better blogging platform. I am going to continue writing about SQL Server, problems that I faced and sometimes solved, tips and tricks, coding practices and a lot more - we'll see what future will bring.

I hope that you will find this blog interesting :)