Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

The BULK INSERT command is used widely for importing data into SQL Server tables. It is very handy when there is a requirement to import large amounts of data from a CSV or Text file. An example follows:-

BULK INSERT MyTable
FROM 'e:\mytextfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

However, there are certain scenarios when I do not want to import vast amounts of data into a table, but instead need to be able query the file directly. This can be achieved using OPENROWSET.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]')

It is then possible to join data from the file to tables within your database:-

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]') x
LEFT JOIN CustomerTable c
	on c.customerid = x.custid

Tags: , , , , , ,

The T-SQL script at the bottom of the page produces a simple data dictionary for a SQL Server Database, along with descriptions of its objects. It produces HTML output that can be uploaded to a web server, or you could copy and paste into Word. We all hate creating documentation, don’t we?! The example includes tables, columns and stored procedures, but it would be fairly simple to tweak the query to include additional objects such as views, users, primary and foreign keys etc.

Before running the query, it is highly recommended to add descriptions to the tables, columns and procedures. You can do this within SQL Server Management Studio (right click the object, select Properties and then the Extended Properties tab), but I find it easier to use the built-in sys.sp_addextendedproperty procedure.

Adding a Description to a Table:-

EXEC sys.sp_addextendedproperty 
@name=N'Description', 
@value=N'Add Your Description Here' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'Your Table Name Here'
GO

Adding a Description to a Column:-

EXEC sys.sp_addextendedproperty 
@name=N'Description', 
@value=N'Add Your Description Here' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'Your Table Name Here', 
@level2type=N'COLUMN',
@level2name=N'Your Column Name Here'
GO

Now we can generate the documentation….

--Ensure no additional lines are printed to the output
SET NOCOUNT ON

--Declare Variables
DECLARE @dbase varchar(50)
DECLARE @full_table_name varchar(100)
DECLARE @table_name varchar(100)
DECLARE @table_desc varchar(500)
DECLARE @table_date varchar(500)

-- Declare Variables for HTML Output
DECLARE @column_html varchar(max)
DECLARE @index_html varchar(max)
DECLARE @proc_html varchar(max)

--Set database name
SET @dbase = (SELECT DB_NAME() As 'Database Name')

-- Start HTML Generation
PRINT '<HTML><body>'
PRINT '<h1>' + @dbase + '</h1>'

-- Setup Table cursor
DECLARE cr_tables CURSOR READ_ONLY FOR 

select distinct t.name
from sys.TABLES t
	left join sys.extended_properties x
		on t.object_id = x.major_id
where t.name <> 'sysdiagrams' 
order by t.name asc

-- Open Cursor
OPEN cr_tables
FETCH NEXT FROM cr_tables
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

-- Select extended table properties
select @full_table_name = (s.name + '.' + t.name), @table_desc = convert(varchar(1000), x.value), @table_date =  modify_date
from sys.TABLES t
	left join sys.extended_properties x
		on t.object_id = x.major_id
	left join sys.schemas s
		on t.schema_id = s.schema_id
where t.name = @table_name
order by t.name asc

-- Print Table Properties
PRINT '<h2>' + @full_table_name + '</h2>'
PRINT '<b>Description:-</b> ' + @table_desc + '<br>'
PRINT '<b>Last Modified:-</b> ' + @table_date

-- Print HTML Table Columns
select @column_html = '<h3>Columns</h3><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr><td><b>Column</b></td><td><b>Description</b></td><td><b>Column Ordinal Position</b></td>
 <td><b>Data Type</b></td>
 <td><b>Max Length</b></td>
 <td><b>Precision</b></td>
<td><b>Nulls Allowed</b></td>
 <td><b>Identity</b></td></tr>'

-- Get SQL Columns
select @column_html = @column_html + '<tr><td>' + convert(varchar(100), c.name)  +'</td><td>' + ISNULL(convert(varchar(200), x.value), 'No description available') + '</td><td>' + convert(varchar(3), column_id) + '</td><td>' + ty.name + '</td><td>' + convert(varchar(4), c.max_length) + '</td><td>' + convert(varchar(4), c.precision) + '</td><td>' +
	convert(varchar(3), CASE c.is_nullable 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END) + '</td><td>' +
	convert(varchar(3),CASE c.is_identity 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END) + '</td></tr>'
from sys.columns c
	left join sys.extended_properties x
		on c.object_id = x.major_id
			and c.column_id = x.minor_id
	left join sys.tables t
		on c.object_id = t.object_id
	left join sys.types ty
		on c.system_type_id = ty.user_type_id 
	where type_desc = 'USER_TABLE'
		and t.name = @table_name
order by t.name, c.column_id asc

PRINT @column_html + '</table>'

-- Get Index Info
select @index_html = '<h3>Indexes</h3><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr><td><b>Index</b></td><td><b>Index Type</b></td><td><b>Is Primary Key</b></td>
 <td><b>Fill Factor</b></td></tr>'

select @index_html = @index_html + '<tr><td>' +  i.name + '</td><td>' + i.type_desc 
+ '</td><td>' +
	convert(varchar(3), (CASE i.is_primary_key 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END))
	+ '</td><td>' +
	convert(varchar(3), fill_factor) + '</td></tr>'
from sys.tables t
	right join sys.indexes i
		on t.object_id = i.object_id
where i.name is not null
	and t.name is not null
		and t.name = @table_name
order by t.name asc, Is_Primary_Key desc, i.name asc

PRINT @index_html + '</table><br>'

FETCH NEXT FROM cr_tables
INTO @table_name

END

-- Generate HTML for Stored Procedures
select @proc_html = '<br><h2>Stored Procedures</h2><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr> <td><b>Stored Procedure</b></td><td><b>Description</b></td><td><b>Last Modified Date</b></td></tr>'

select @proc_html = @proc_html + '<tr><td>' + p.name + '</td><td>' + convert(varchar(100), x.value) + '</td><td>' + convert(varchar(50), p.modify_date) + '</td></tr>'
from sys.procedures p
	left join sys.extended_properties x
		on p.object_id = x.major_id
where p.is_ms_shipped = 0
	and p.name not like 'sp_%'

PRINT @proc_html
PRINT '</table>'

PRINT '</HTML></body>'

close cr_tables
deallocate cr_tables
SET NOCOUNT OFF

Tags: , , , , ,

The HAVING clause is used in conjunction with a GROUP BY so that aggregate functions can be used in a comparison. Think of it as a WHERE clause for groupings.

The following example should help to explain further.

The following example will fail because the WHERE clause is used to evaluate data on a row by row basis. The query is an attempt to limit the results returned to those customers with an order count greater than five.

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
WHERE COUNT(customer_id) > 5
GROUP BY customer_id

You will receive an aggregate related error, as the WHERE clause can only operate on a single row. To achieve the desired result, we remove the WHERE clause and add a HAVING clause after the GROUP By. Running the following query will now work:-

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
GROUP BY customer_id
HAVING COUNT(customer_id) > 5

Tags: , , , , ,

Locking occurs frequently within a database and is a desired feature for a relational database system. SQL Server places locks to allow the sharing of resources (tables, pages, rows etc) when multiple users attempt to access or alter the same data.

However, locking can become problematic when many users (or processes) are constantly trying to access the same data. This can lead to contention and deadlocking. The following DMV query can assist with the troubleshooting of such issues:-

SELECT  L.request_session_id AS 'SPID', 
        DB_NAME(L.resource_database_id) AS 'Database Name',
        O.Name AS 'LockedObjectName', 
        P.object_id AS 'LockedObjectId', 
        L.resource_type AS 'LockedResource', 
        L.request_mode AS 'LockType',
        ST.text AS 'SqlStatementText',        
        ES.login_name AS 'LoginName',
        ES.host_name AS 'HostName',
        TST.is_user_transaction as 'IsUserTransaction',
        AT.name as 'TransactionName',
        CN.auth_scheme as 'AuthenticationMethod'
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id;

The query outputs the SPID that obtained the lock, the database affected, the object (table), the resource affected (e.g. PAGE, KEY etc), the SQL statement that was executed, login name of the user, the host name, whether the query is a user statement and the authentication method. By identifying long running queries and data that is being constantly locked, problems can be understood and solutions can be found to improve performance.

Tags: , , ,

SQL Server Profiler is a useful tool for troubleshooting and monitoring database and server performance. Usually, a DBA has to manually setup a trace to monitor a specific area of interest (e.g. Deadlocks, Login audit). However, many are unaware that a default Profiler trace is permanently running on the majority of servers. It is possible to query the trace files from within SQL Server. The default trace captures information such as file growth, mirroring status, object creation/deletion/alteration and security auditing, amongst other items. If you decide that this is not valuable information then you can switch off the default trace.

To check to see whether a default trace is indeed currently running, execute the following:-

SELECT * 
FROM sys.configurations 
WHERE configuration_id = 1568

If the value is ‘1’ then the trace is running. Once you have established that the trace is enabled, you need to run a query to determine the most recent trace file. Trace files automatically rollover, so you need to know which one to query.

SELECT traceid, value 
FROM [fn_trace_getinfo](default) 
WHERE [property] = 2;

The full path and file name will be returned. You can then run the following query and include the correct path to the most recent file. You could of course setup a variable to automatically populate the path.

SELECT *  
FROM [fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_49.trc', DEFAULT) 
ORDER BY StartTime;

You can disable the trace by issuing the following commands. However, don’t expect a dramatic increase in server performance as the trace uses only minimal resources.

EXEC master.dbo.sp_configure 'allow updates', 1;
GO 
EXEC master.dbo.sp_configure 'show advanced options', 1;
GO 
EXEC master.dbo.sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'allow updates', 0;
GO

Tags: , , , ,

UK Postcodes can be difficult to handle in SQL, especially when trying to group results based on area code. Postcodes in the UK come in a variety of formats, the most common being AA9 9AA, A9 9AA, A99 9AA and AA99 9AA. Problems arise due to the variation in the total number of characters in a code, the different combinations of numbers and letters, and also the location of the space.

If you need to write a query to select the characters up to but excluding the space (e.g. AA9, A9, A99 or AA99), then the following approach can be used. It uses the LEFT command in conjunction with CHARINDEX to select the left side of the full code.

SELECT CompanyName, LEFT(Postcode, CHARINDEX(' ', Postcode) -1) AS 'Postcode'
FROM CompanyAddresses

However, if you need to select only the leading letters and not numbers (A or AA), then you can use a CASE statement:-

SELECT CompanyName, CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode'
FROM CompanyAddresses

Using either of the above two methods, you can successfully group results into Postcode areas. Use either method to group and select on Postcode.

SELECT CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode', COUNT(CompanyName) AS 'Companies in Postal Area'
FROM CompanyAddresses
GROUP BY CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END

Tags: , , , , , , , ,

The following script defragments all indexes in a database that are above a given fragmentation threshold. I can’t claim to have written this – this script can be found on the MSDN Website. I would suggest creating a stored procedure in your database and calling it from a scheduled SQL Agent job every evening. Be careful of running this on a database with many indexes or with large tables as it may take some time to complete.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Tags: , , , ,

%d bloggers like this: