SSMS: Beware of Modify

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.

T-SQL: Table Valued Parameters - "adios" to the Split function

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.


-- set up the objects
USE tempdb
GO
-- your table type
CREATE TYPE dbo.string_list_tbltype AS TABLE (nm NVARCHAR(255))
GO
-- your table
CREATE TABLE dbo.tblBeatles (Nm NVARCHAR(255), Dt DATETIME)
GO
-- the insert procedure
CREATE PROCEDURE dbo.AddBeatlesMembers
@Members dbo.string_list_tbltype READONLY
AS
INSERT INTO dbo.tblBeatles
SELECT nm, GETDATE()
FROM @Members
GO


And now the c# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Music
{
    class Program
    {
        static void Main(string[] args)
        {
            UseTVP();
        }

        public static void UseTVP()
        {
            DataTable BeatlesTable = new DataTable();
            BeatlesTable.Columns.Add("Name", typeof(string));
            
            // Fill with row
            BeatlesTable.Rows.Add("John");
            BeatlesTable.Rows.Add("Paul");
            BeatlesTable.Rows.Add("Ringo");
            BeatlesTable.Rows.Add("George");

            using (SqlConnection conn = new SqlConnection("Server=MyServer;Database=tempdb;Trusted_Connection=True;"))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "dbo.AddBeatlesMembers";
                SqlParameter param = cmd.Parameters.AddWithValue("@Members", BeatlesTable);

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }
    }
}

There is also an MSDN article that explains this in more detail.

FTP: Make Windows Explorer Interactive

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.

Essentially you need to edit a couple of options in IE - Tools, Internet Options, Advanced, Enabled Folder View for FTP sites.



A restart of Windows Explorer and a visit to my ftp site got me the much more user friendly:



I can only assume that some windows updates or IT security patch had reset these settings.

T-SQL: datetime2 vs datetime

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!).

So the example from BOL looks like:

SELECT CAST('2007-05-08 12:35:29.123' AS DATETIME) AS 'datetime' ,
    CAST('2007-05-08 12:35:29. 1234567' AS datetime2(7)) AS 'datetime2'


Storage

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:

SELECT CAST('2007-05-08 12:35:29.128' AS DATETIME) AS 'datetime' ,
    CAST('2007-05-08 12:35:29. 128567' AS datetime2(3)) AS 'datetime2'



One thing I did note was that GETDATE() and CURRENT_TIMESTAMP both return a datetime so it'll be interesting to see if MS look to change this in the future.

Powershell: Copy those backups

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:

copy-item "F:\SQLBackup\*" "\\MyRemoteServer\SQLBackups" -Force -Recurse

Step 3:

$files = gci \\MyRemoteServer\SQL
foreach ($file in $files)
{
   $datediff = ((Get-Date) - $file.CreationTime).Days
   if ($datediff -gt 5 -and $file.PsISContainer -ne $True)
       {
       $file.Delete()
       }
}



SSIS: Watch List, where are you?

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.

Denali T-SQL: Sequences, eh?

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 id
2) Client adds sends Items to the Application
3) Client receives confirmation that the items will be added along with referenceIds for each item
4) 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:

-- create a sequence
CREATE SEQUENCE dbo.ItemIDs    
AS INT    
   MINVALUE 1    
   NO MAXVALUE    
   START WITH 1;
GO

-- and a procedure to assign ids to the items which can be returned to the client
CREATE PROCEDURE dbo.GetItemIds
@Items XML
AS

DECLARE @TblItems TABLE (ItemID INT, Item NVARCHAR(255))

INSERT INTO @TblItems
SELECT NEXT VALUE FOR dbo.ItemIDs AS ItemID, tbl.cols.value('.', 'nvarchar(255)') AS Item
FROM @Items.nodes('//Item') AS tbl(cols)

-- i'll return this as XML and it can be passed to service broker
SELECT * FROM @TblItems
GO

DECLARE @Items XML = '<Item>Watch</Item><Item>Glasses</Item><Item>Ring</Item>'

EXEC dbo.GetItemIds @Items

GO

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.

Admin: Elevate Privileges with TRUSTWORTHY

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 slightly

USE [master]
GO
-- create a sysadmin login
CREATE LOGIN MySYSAdmin
WITH PASSWORD = '123';
GO
EXEC sp_addsrvrolemember 'MySYSAdmin', 'sysadmin';
GO
-- impersonate that login to create the database
-- and therefore be the owner
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
CREATE DATABASE TrustworthyTest
GO
USE [master]
GO
-- now create a test user who has full privileges
-- within that database
CREATE LOGIN MyTrustworthyTestDBOwner
WITH PASSWORD = '456'
GO
USE TrustworthyTest
GO
CREATE USER [MyTrustworthyTestDBOwner]
FOR LOGIN [MyTrustworthyTestDBOwner]
GO
EXEC sp_addrolemember 'db_owner', 'MyTrustworthyTestDBOwner'
GO
USE [master]
GO
-- drop out of the sysadmin context
-- and lets impersonate our new user
REVERT
GO
USE [TrustworthyTest]
GO
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- can we select from another database? No!!
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- 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.FetchFromAdventureWorks
WITH EXECUTE AS CALLER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO
-- 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 trustworthy
REVERT
GO
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
ALTER DATABASE TrustworthyTest
SET TRUSTWORTHY ON
GO
REVERT
GO
-- and again impersonate the user
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- we still can't access the other database
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- 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 procedure
EXEC dbo.FetchFromAdventureWorks
GO
-- 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.FetchFromAdventureWorks
WITH EXECUTE AS OWNER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO

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.

Admin: Useful Resources

Some fantastic tutorials here demonstrating some of the common DBA tasks.

http://sqlserverpedia.com/wiki/SQL_Server_Tutorials