SQL Server: Updating varbinary fields

I'm working on a development database with a lot of varbinary fields - these are great once you have your admin screens but they're a pain in the initial development phase when there is no GUI for loading data. This little T-SQL snippet can be used to update varbinary fields - or adapt it to insert new data into varbinary fields.

update [schema].[table] set Column = (
 SELECT * 
        FROM OPENROWSET(BULK N'E:\Share\Temp\myfile.jpg', SINGLE_BLOB) AS BinaryFile
)

SQL Server: Finding tables by column

Assuming you use a decent naming convention in your database, it's sometimes useful to be able to find a list of tables with a common column - for example if you want to write a script to clean out all data related to a specific item, referenced by foreign key columns of the same or a similar name.

 
SELECT distinct
    c.table_name,
    c.table_schema,
    c.column_name
  FROM 
    information_schema.columns c
    INNER JOIN information_schema.tables t 
      ON c.table_name = t.table_name 
  WHERE
    c.column_name LIKE '%my_column_name%'
    AND t.table_type = 'BASE TABLE'
  ORDER BY 
    c.table_name,
    c.column_name

SQL Server: Currently Executing Queries

I found this great article by Ian Stirk on SQLServerCentral.com, regarding finding queries that are currently executing on SQL Server. The article contains the following VERY useful query:

    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
 , ecid
 , [Database] = DB_NAME(sp.dbid)
 , [User] = nt_username
 , [Status] = er.status
 , [Wait] = wait_type
 , [Individual Query] = SUBSTRING (qt.text, 
             er.statement_start_offset/2,
 (CASE WHEN er.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
  ELSE er.statement_end_offset END - 
                                er.statement_start_offset)/2)
 ,[Parent Query] = qt.text
 , Program = program_name
 , Hostname
 , nt_domain
 , start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

SQL Server: Changing Object Schema

I'd forgotten the syntax for moving an object to a different schema:

alter schema NewSchema transfer OldSchema.ObjectName

SQL Server: Finding and Killing Database Connections

This is a query I use often to check what active connections exist for a specified database:

select  spid, sp.cmd, sp.hostname, sp.loginame, sp.nt_domain, sp.nt_username, sp.program_name
from    master.dbo.sysprocesses sp
where   db_name(dbid) = 'mydatabase'
and DBID <> 0  
and spid <> @@spid 

If I want to take the database offline I'll then kill these processes using the spid.

I also found a great post today that had a nice way of killing ALL active connections, by running the following sql:

alter database dbName set single_user with rollback immediate  

This can then be reverted out with

alter database dbName set multi_user with rollback immediate 

Backing up and Restoring SQL Server Databases with .NET

I need to create a tool that would do some backing up and restoring of databases as part of a long-running job this week. I had heard it was fairly simple C# code, but I was pleasantly surprised when I realised just HOW simple it is.

The namespaces of the SMO libraries required changed between 2005 and 2008, so if you're using the SQL Server 2008 objects, you need to reference the following libraries (usually located in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\). If you're using SQL Server 2005 you only need the first two.

Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoExtended Microsoft.SqlServer.Management.Sdk.Sfc

Backing Up Code

SqlConnection conn = new SqlConnection("ConnectionString!");
Server dbServer = new Server(new ServerConnection(conn));
Backup backupMgr = new Backup();
backupMgr.Devices.AddDevice("E:\Backups\YourFile.bak", DeviceType.File);
backupMgr.Database = conn.Database;
backupMgr.Action = BackupActionType.Database;
backupMgr.SqlBackup(dbServer);

Restoring Code

SqlConnection conn = new SqlConnection("ConnectionString!");
Server dbServer = new Server(new ServerConnection(conn));
Restore restoreMgr = new Restore();
restoreMgr.Devices.AddDevice("E:\Backup\MyFile.bak", DeviceType.File);
restoreMgr.Database = conn.Database;
restoreMgr.Action = RestoreActionType.Database;
restoreMgr.SqlRestore(dbServer);

SQL Server Locking, Blocking and Waiting

Being able to see what is blocking or waiting on a SQL Server machine is essential for most developers these days. I find myself using the following queries almost daily at the moment, so I thought I'd post them here:

Waiting

select 
    tx.[text] as ExecutingSQL, 
    wt.session_id, 
    wt.wait_duration_ms, 
    wt.wait_type, 
    wt.resource_address, 
    wt.blocking_session_id, 
    wt.resource_description
from sys.dm_os_waiting_tasks wt
inner join sys.dm_exec_connections ec on wt.session_id = ec.session_id
cross apply 
(
    select * from sys.dm_exec_sql_text(ec.most_recent_sql_handle)
) as tx
where wt.session_id > 50 and wt.wait_duration_ms > 0

Blocking

select 
    Blocked.session_id as Blocked_Session_ID
    ,Blocked_SQL.text as Blocked_SQL
    ,waits.wait_type as Blocked_Resource
    ,Blocking.session_id as Blocking_Session_ID
    ,Blocking_SQL.text as Blocking_SQL
from sys.dm_exec_connections as Blocking
inner join sys.dm_exec_requests as Blocked on Blocked.blocking_session_id = Blocking.session_id
cross apply
(
    select * from sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
) AS Blocking_SQL
cross apply
(
    select * from sys.dm_exec_sql_text(Blocked.sql_handle)
) as Blocked_SQL
inner join sys.dm_os_waiting_tasks as waits on waits.session_id = Blocked.session_id

SQL Server IDENTITY Columns

IDENTITY columns can be a real pain in the ass when data gets out of sync. GUIDs are generally a much better option when it comes to replication, but you aren't always in control of data structures and sometimes you inherit old systems that weren't designed to be replicated.

That being said, here are some tips on how to get around problems with IDENTITY columns and getting data in sync:

IDENTITY_INSERT

You can temporarily turn identity insertion off with the following statement:

SET IDENTITY_insert <table> ON

You can now insert the identity values into your table. Don't forget to run the same statement on the table, substituting ON for OFF!

Reseeding the IDENTITY value

For example, if you want to reset the current identity value on a table to 100, you can use the following statement:

DBCC CHECKIDENT
(
 'schema.table', RESEED, 100
)

SQL Server Express Remote Connections

I've been doing some work with SQL Server replication over the last few days, and in order to acheive this I was replicating between one of our DB servers and a SQL Server Express instance on my desktop. To my surprise, setting up remote connections to Express is actually a major pain in the butt, so I thought I'd document it here.

Firewall

The first step is to ensure that remote connections are allowed through your firewall. I was using the default Windows firewall, so I set up a new exception: I called it "SQL Server", Port number 1433, Protocol TCP, and I changed the scope to be "My network (subnet) only"

SQL Server Configuration

There are some hidden settings that you need to enable before SQL Server express will allow remote connections. You will need to load up the SQL Server Configuration Manager and do the following:

• Under SQL Server 2005 Network Configuration, mark the TCP/IP status as Enabled
• Right-click TCP/IP and go to Properties
• Go to the IP Addresses tab, and scroll down to IP All
• Remove the value in TCP Dynamic Ports, and enter "1433" (same value as you used in firewall) in the TCP Port value

SQL Server

The next step is to ensure SQL Server itself allows remote connections. From within SQL Server, go to the SQL Server instance, click Properties, Connections, "Allow remote connections to this server"). Finally, restart the express service and you should be able to connect to your machine's SQL Server instance remotely.

SQL Server Query Plans

The caching of query plans in SQL Server is extremely important when it comes to application performance. The way this works is not very well understood - I still don't get all the intricacies of it but as a general rule of thumb, it's a good move to either:

1. Use stored procedures: these get pre-compiled and allow the re-use of execution plans. They allow for parameters, allowing for a "shared" execution plan
2. he use of stored procs is not possible or not part of your design, use sp_executesql - do NOT use EXEC when running dynamic sql. sp_executesql, unlike EXEC, can be parameterised and therefore also allows for "shared" execution plans.

You can analyse the cached execution plans on SQL Server with the following statement:

 
with CachedPlans as (
select top 100
    objtype,
    p.size_in_bytes,
    left([sql].[text], 100) as [text],
    usecounts
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
)
select * from CachedPlans
where text like '%Select * from MyTable%'
order by usecounts desc