Tuesday, 29 March 2011

When you want to change the defintion of a sql programmability object in SQL2008 eg Trigger, Stored Procedure or Function using SQL Server Management Studio, you get to options when right clicking the object, Modify or Script..ALTER to.

I've never really understood the differences between the two as both appear to have the same behaviour of scripting out your object with an ALTER command. But there does appear to be a minor difference, one which i'm sure isn't by design, is only minor but definitely one to be careful of.

When using Modify to script out your object to a new window the GO separator is not included whereas it is included using the Script command. So if you happen to make a few modifications in the same window below the end of the definition of the object to test something "ad hoc", you'll need to remember to highlight only the part of the query window which forms the object. Otherwise, your testing statements will be included as part of object when you just hit F5 to persist the ALTER.

In the following screenshot, i've hit the Modify option and then written an adhoc query for a different purpose. If, I forget to remove the adhoc statement, forget to add a GO statement at the end of the procedure or don't highlight the procedure definition, this test query will be persisted as part of the procedure leading to some undesirable results.

Friday, 25 March 2011

A very common problem that developers have faced is the ability to pass in arrays to SQL Server and although there are various options available for editions up to SQL2005, the arrival in SQL2008 gave us Table Valued Parameters which allows a much neater way of passing in multiple values.

Here is an example using a simple c# console app to add multiple values to a database table. The key part to note here is that the input parameter needs to be set as READONLY and within the c# code you need to pass in a datatable as the parameter.

Monday, 21 March 2011

I often use Windows Explorer as my FTP client as its quick and simple to drag and drop files on to a server. However, recently something had changed on my system which meant that when using Explorer the mode wasn't interactive and I was unable to use it in the way I would expect:

After some digging around, I managed to stumble across this MS article which although relevant to Windows 2000 also seemed to work for me running Windows XP SP3.

Friday, 18 March 2011

I recently read in a MCTS training manual that from SQL2008 onwards, the datetime datatype was "mainly available for backwards compatibility" and that datetime2 should be preferred. This is also backed up by Books Online which gives the rather cute message, "Use the time, date, datetime2 and datetimeoffset data types for new work".

While I can't see a (date)time in the near future where datetime will be removed from the product, I thought i'd highlight the main differences between datetime and datetime2.

Precision

datetime only allows for precision up to every 3rd milisecond which essentially means that you can get rounding issues for times that are in fact different. Of course, in the majority of applications this level of accuracy isn't relevant but its an interesting point to note. Datetime2 addresses this by allowing you to specify the accuracy of seconds to 7 decimal places (sorry if thats the correct terminology!).

The syntax for datetime2 means you can specify the accuracy of your date and this also has storage implications. All datetime fields take up 8 bytes but by using datetime2, storing an identical date i.e specifying a precision level of 3, only takes up 6 bytes. As you get more accurate, the storage required increases.

From BOL:6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

So if you want to mimic datetime behaviour using datetime2, you need only to specify datetime2(3). One caveat to this is with regard to rounding as datetime is only accurate to every 3rd ms meaning the last digit will always end in 0, 4 or 7 so the following does not yield equal results:

Thursday, 17 March 2011

I blogged earlier this year that i'd endeavour to make more use of Powershell and here is my first implementation of a powershell script.

I wanted to copy my SQL Backups to an offsite location and thought i'd harness the power of Powershell to achieve this. I have a simple SQL Agent Job with a step which backs up my files to a drive. Once completed, the second step (a powershell step) copies the files to the remote server and a final step to remove files older than 5 days.

Step 1:

A SQL maintenance plan which backups the files but I won't be showing whats involved there. I really want to get down in text the content of my Powershell scripts:Step 2:

Tuesday, 15 March 2011

Something that i always forget when developing SSIS packages is how to use the watch list. Unfortunately, its not quite as simple as "right click" variable, "add to watch list".

It appears that you can only activate the watch list once you add some breakpoints to the packge and the package is debugging. In other words, you can't set this stuff up ahead of time. When you start debugging, the watch window shows in Visual Studio and you can drag the variables to this window.

Friday, 11 March 2011

A new feature introduced in SQL Server Denali is Sequences which are another method of autonumbering. These have been around in Oracle for a while and at first, I was a bit unsure as to what their benefits would be, as we already have the IDENTITY property in SQL Server.

However, after giving it a bit of thought I can see areas where this would definitely be useful and in this post, Aaron Bertrand provides evidence of performance benefits over IDENTITY.

Consider a scenario where there Items are added to a database using Service Broker. The process is as follows:1) Items are stored with a unique INT id2) Client adds sends Items to the Application3) Client receives confirmation that the items will be added along with referenceIds for each item4) Item is added to the database via Service Broker

The issue here is with step 3 as we are unable to get the next available Identity without hitting the Item table and using something such as SCOPE_IDENTITY() to get the next value. This though doesn't fit the asynchronous approach.

Using Sequences allows us to get the Ids before processing the items and return the references to the client:

Ok, so it may be a contrived example but I hope it illustrates the idea. A further example may be a multipage web registration form which adds data to multiple tables and you don't actually have to commit the INSERTs until the very end. Sequences would help here too.

Friday, 4 March 2011

The database scoped TRUSTWORTHY flag was introduced in SQL 2005 and . Its only a guess, but I dare say that its often turned on as its the path of least resistance without understanding its consequences. For example, when deploying CLR assemblies which require EXTERNAL_ACCESS its far easier to just set the TRUSTWORTHY flag than create an asymetric key and login specifically for that routine - this is something i'm often guilty of. In many scenarios, its probably not an issue as the databases do not hold sensitive data and/or all the databases on the server are managed by the same people so having access across them doesn't pose any threat.

There isn't actually a stack load of information out there on this, so I thought i'd demonstrate how the flag can allow users access to databases that perhaps they shouldn't. Essentially, if the login which is the owner of the database (with the trustworthy flag) has permissions in other databases, then a different user in the database can create modules which access other databases by using the EXECUTE AS OWNER clause.

Look at this example:NB: My example uses AdventureWorks but you can use any user database and modify the queries slightlyUSE [master]GO-- create a sysadmin loginCREATE LOGIN MySYSAdminWITH PASSWORD = '123';GOEXEC sp_addsrvrolemember 'MySYSAdmin', 'sysadmin';GO-- impersonate that login to create the database-- and therefore be the ownerEXECUTE AS LOGIN = 'MySYSAdmin';GOCREATE DATABASE TrustworthyTestGOUSE [master]GO-- now create a test user who has full privileges-- within that databaseCREATE LOGIN MyTrustworthyTestDBOwnerWITH PASSWORD = '456'GOUSE TrustworthyTestGOCREATE USER [MyTrustworthyTestDBOwner] FOR LOGIN [MyTrustworthyTestDBOwner]GOEXEC sp_addrolemember 'db_owner', 'MyTrustworthyTestDBOwner'GOUSE [master]GO-- drop out of the sysadmin context-- and lets impersonate our new userREVERTGOUSE [TrustworthyTest]GOEXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'GO-- can we select from another database? No!!SELECT *FROM AdventureWorks.HumanResources.EmployeeGO-- Msg 916, Level 14, State 1, Line 1-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- how about in a stored procedure?CREATE PROCEDURE dbo.FetchFromAdventureWorksWITH EXECUTE AS CALLERASSELECT *FROM AdventureWorks.HumanResources.EmployeeGOEXEC dbo.FetchFromAdventureWorksGO-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- ok, lets revert back and reimpersonate our -- sysadmin and make the database trustworthyREVERTGOEXECUTE AS LOGIN = 'MySYSAdmin';GOALTER DATABASE TrustworthyTestSET TRUSTWORTHY ONGOREVERTGO-- and again impersonate the userEXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'GO-- we still can't access the other databaseSELECT *FROM AdventureWorks.HumanResources.EmployeeGO-- Msg 916, Level 14, State 1, Line 2-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- nor through the stored procedureEXEC dbo.FetchFromAdventureWorksGO-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- BUT if we change the procedure to execute-- in a different context. WE CAN!!!ALTER PROCEDURE dbo.FetchFromAdventureWorksWITH EXECUTE AS OWNERASSELECT *FROM AdventureWorks.HumanResources.EmployeeGOEXEC dbo.FetchFromAdventureWorksGO

As I said, for many users of SQL Server having access across databases is not that big a deal and using the TRUSTWORTHY flag is preferable to creating keys and logins for CLR assemblies but its still important that you understand this behaviour.