Bookmark : Backing up HyperV VMs via a batch file.

These solutions use Powershell and a Batch file.

The crux of this is being able to stop and start your VMs cleanly (the batch file can then be scheduled as necessary).

V1: http://www.sertec.ca/howtos/Howto-backup-hyperv-vm-batch-script.html
V2: http://www.bunkerhollow.com/blogs/matt/archive/2008/09/24/hyper-v-vm-backup-script-batch-file.aspx

Bookmarks : Stay Interview

An interesting concept. Linked from this morning's SSC email.
The 'stay' interview. Designed to prevent 'exit' interviews.

The Stay Interview

Index Fragmentation Status (includes Partitioned Tables/Indexes) Script

Published on SQLServerCentral.com today, another effort from myself....

SSC : Index Fragmentation Status (includes Partitioned Tables/Indexes)

July 2010 - Have updated to include a handy ALTER INDEX , REBUILD command...

SELECT   SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,CASE WHEN dmv.Page_Count <> 0 THEN p.rows / dmv.Page_Count ELSE 0 END AS Rows_Per_Page
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
            ,RebuildCommand = 'ALTER INDEX ['+I.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(o.object_id) + '] REBUILD WITH(ONLINE = ON);'
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
     SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

update!!!

A helpful forum participant has turned it into a much faster CTE!

;WITH IndexStats AS
(
        select 
                        object_id,
                        index_id,
                        partition_number,
                        Avg_Fragmentation_In_Percent,
                        Fragment_Count,
                        Avg_Fragment_Size_In_Pages,
                        Page_Count
                from 
                        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, N'LIMITED')
)
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
           , RebuildCommand = 'ALTER INDEX ['+I.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(o.object_id) + '] REBUILD WITH(ONLINE = ON);' 
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN IndexStats dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
Avg_Fragmentation_In_Percent DESC
            ,SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

DBA 101 : DBCC SHRINKFILE

WARNING : ONLY SHRINK NON LIVE DATABASES E.G TEST ENVIRONMENTS AND ONLY WHEN ABSOLUTELY NECESSARY E.G RESTORES FROM LIVE WHERE DATA SPACE IS PRE-ALLOCATED! 

The Shrinkfile screen shown above is familiar, but what do the options do???

1) Release unused space

This uses the TRUNCATEONLY option and releases unused space at the end of the file.
No reorganization of pages occurs, i.e if empty pages exist in the middle of the file , the space will not be reclaimed.

The size '0' is scripted by Management Studio but is ignored

-- Release unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , 0, TRUNCATEONLY)
GO

2) Reorganize pages before releasing unused space

Pages are reorganized to free up all available free space. This option can take some time
Update : See this tip for shrinking a datafile in stages

-- Reorganize pages before releasing unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , 3600)
GO

3) Empty file by migrating the data to other files in the same filegroup

If other files are assigned to a filegroup , pages are distributed between those files.

-- Empty file by migrating the data to other files in the same filegroup
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , EMPTYFILE)
GO

4) Not available from the interface - NOTRUNCATE

NOTRUNCATE is only available by TSQL.
Use it to reorganize the pages to the start of the file, but leave the datafile the same size.

-- DOES NOT Release unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , NOTRUNCATE)
GO

SSRS : Dynamically Changing Cell Colour

SSRS : Dynamically Changing Cell Colour

Reporting Services dynamic formatting is controlled by expressions. The SWITCH function is one method of implementing logic inside an expression (like TSQL's CASE statement).

To quote Books Online , SWITCH -

' Evaluates a list of expressions, and returns the value associated with the first condition that returns TRUE. Switch can have one or more condition/value pairs. '

So applying this in an expression to the BackgroundColor property of a cell  >

=Switch(First(Fields!Elapsed_Minutes.Value) < 30, "Green", First(Fields!Elapsed_Minutes.Value) < 60, "Yellow", First(Fields!Elapsed_Minutes.Value) >= 60, "Red", 1=1, "Transparent")

Breaking this example down, it uses the Elapsed_Minutes column and uses GREEN for values less than 30 minutes, YELLOW for values less than 60 minutes and RED for values over 60.
The 1=1 argument becomes true if none of the previous arguments are satisfied i.e Elapsed_Minutes is NULL / not present.

Bookmark : Index Defrag v4.0

Michelle Ufford (aka sqlfool) has released version 4.0 of her Index Defragmentation script.
Given the awesomeness of the previous versions, I'll be updating to this soon.

sqlfool : Index Defrag Script , v4.0

Sysinternals : Process Monitor

Off-topic, but I wanted to share what a great tool Process Monitor is.

The screen shot below shows me debugging the installer of the latest iTunes update which refused to go onto my laptop.

Sysinternals Suite : Process Monitor

Bookmark : Copy Table Structure Without Data Using 1 Line Query

Sometimes the simplest ideas are the best.
A brilliant script to quickly duplicate table structure on the fly!

This Copies the table structure , but not the data....

SELECT * FROM Person.Contact

SELECT * INTO Person.Contact_COPY FROM Person.Contact Where 1=2

SELECT * FROM Person.Contact_COPY

As does this version, which utilises TOP 0. Unusual, but it works....

SELECT * FROM Person.Contact

SELECT TOP 0 * INTO Person.Contact_COPY FROM Person.Contact

SELECT * FROM Person.Contact_COPY

This is very useful for creating temporary tables on the fly.

Things to note :

• The table is created on the default filegroup.
• Indexes/Constraints are not copied.
• Any Identity column will be reseeded.

SSC :
Copy Table Structure Without Data Using 1 Line Query

Indexes on computed Columns : Marking a CLR function as Deterministic

Came across this when trying to index a column.
This column is a little different in that it is defined as a computed column based on a common language runtime (CLR) function.

So, running >

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

gave me this>

Msg 2729, Level 16, State 1, Line 1

Column 'IPNumber' in table 'dbo.IPTest' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

So I need to create a 'deterministic' function.
Setting the function properties isdeterministic:=true and isprecise:=True do the trick.

Partial Public Class IPConverter
 _
Public Shared Function CLR_IPToInteger(ByVal Expression As String) As Long

If IsDBNull(Expression) = False Then

Try
Dim IPSplit() As String
IPSplit = Expression.Split(".".ToCharArray())
Expression = IPSplit(3) + "." + IPSplit(2) + "." + IPSplit(1) + "." + IPSplit(0)

Dim IPAddress As System.Net.IPAddress = System.Net.IPAddress.Parse(Expression)
With IPAddress
Return (System.Convert.ToInt64(.GetAddressBytes(3)) << 24) Or (System.Convert.ToInt64

(.GetAddressBytes(2)) << 16) Or (System.Convert.ToInt64(.GetAddressBytes(1)) << 8) Or System.Convert.ToInt64

(.GetAddressBytes(0))
End With

Catch ex As Exception
Return 0I
End Try

Else
Return 0
End If

End Function

So, having compiled the assembly and recreating the CLR assembly and function (a reminder here)
I can replace the computed column using the revised function like this >

ALTER TABLE dbo.IPTest 
DROP COLUMN IPNumber 

ALTER TABLE dbo.IPTest 
ADD  IPNumber AS Common.CLR_IPToInteger(IPString)

Once again, we'll try that Index

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

>

Msg 2798, Level 16, State 1, Line 1

Cannot create index or statistics 'ix_ipnumber' on table 'dbo.IPTest' because SQL Server cannot verify that key column 'IPNumber' is precise and deterministic. Consider removing column from index or statistics key, marking computed column persisted, or using non-CLR-derived column in key.

Nice, a helpful error message telling us EXACTLY what to try. Given I recreated my function using both 'isprecise' and 'isdeterministic' set, I'm missing PERSISTED, i.e. to make the calculated column physically part of the table.

ALTER TABLE dbo.IPTest 
DROP COLUMN IPNumber 

ALTER TABLE dbo.IPTest 
ADD  IPNumber AS Common.CLR_IPToInteger(IPString) PERSISTED

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

Command(s) completed successfully. (that's success to you & me)

Index Links :
MSDN : Creating Indexes on Computed Columns
MSDN : CLR, Computed Columns and Indexability

CLR Links :
Regular Expressions using .NET Common Language Runtime integration in SQL Server 2005
MSDN : CLR Scalar-Valued Functions
MSDN : Managed Data Access Inside SQL Server with ADO.NET and SQLCLR
Solace : Creating and Enabling a CLR Function
Solace : VB.NET / SQL CLR / Decode an encoded URL

Index Issue : Missing Statistics

Hovering over the estimated execution plan for a query, the yellow explanation mark alerted me to...

Warnings : Columns with no statistics  format : dbname.schemaname.tablename.columnname

2 steps to perform here -

1) DBCC SHOW_STATISTICS('processing.ipcitylookup','ixCityLookup')
2) UPDATE STATISTICS processing.ipcitylookup

SQLBits VI

The sixth SQLBits conference was in London last Friday at Church House Conference Centre, a stones throw from Westminster.

Not being short of lieu time or holiday a Friday conference fortunately wasn't a problem for me.
The organisers had once again attracted sufficient sponsorship to keep the event free. Not only were the sessions free, but so was the food (sausage/bacon rolls for breakfast, a generous packed lunch, cakes both mid-morning and afternoon and finally pizza to accompany beer and games). Coffee to keep tired brains alert was flowing throughout the day too.

The theme for SQLBits VI was 'Performance and Scalability' and my first mission was to which sessions to attend. There were 24 sessions (6 time slots x 4 rooms) split across 3 tracks (dba, dev, bi). Given I haven't yet mastered being in 2 places at once I had to decide what to attend.

Like a festival, the headlining acts are on in different arenas at the same time. This makes deciding what to attend a little awkward. Just how do you chose? Focus on technical content you're using now?, stuff you want to use? , attend the sessions because a speaker is entertaining?

Fortunately I recalled that the SQLBits V sessions (videos | blog entry) made their way onto the website which took the pressure off. I won't really miss anything after all. With some sessions being content rich, I have found myself downloading and watching them a number of times, pausing, rewinding etc. This fact added a further tactic to assist me with my session choices, to save the 'level 400' ones for later.

In the end, I attended the following sessions (largely opting for Dev/BI choices) >

Design Patterns for SSIS Performance
Darren Green

Fast Track Foundations : Sequential IO
James Rowland-Jones  | Allan Mitchell

Performance & Scalability through normalisation & set theory
Tony Rogerson

Denormalisation - Having your cake and eating it
Mark Whitehorn  & Yasmeen Ahmed

Optimizing tempdb and temporary object use
Christian Bolton

Designing and Tuning High Speed Data Loading
Thomas Kejser

I enjoyed them all, and (as usual) made note of several subjects for further reading (Like my 'to-do' list needed lengthening). The first and last sessions I attended were of most immediate use for my SSIS endeavours. Like the other attendees, I'm eagerly awaiting the upload of the session videos (and not just to see if I'm in them - Hello mum!).

PS : Anyone reading this post who is thinking of coming to a SQLBits event, don't just think about it, DO IT! You'll learn nuggets of practical information in a single day direct from db/bi experts.

PPS : A big thank you to the team, the sponsors and for a certain competition prize ;)

Database Settings : Forced Parameterization

I originally looked at Forced Parameterization here.

By default Parameterization is SIMPLE, it can be set to FORCED however (in sql 2005+).

My reason for this is that developing stored procedures apparently isn't feasible for our current project. The web developers don't have the skills to maintain them and don't want to learn them. The 'increase in development time' is apparently not worth it. Similarly there is no love for table functions or even for sargable sql. Sql is (and will remain) embedded in the web application :(

With the speed at which decisions are made and the product changes direction I am reluctantly letting it go. I don't agree because (as a dba) I'd like to encourage every opportunity to optimise the database environment. Balancing my DBA role, SSIS development tasks and aspirations to persue the BI track, I have enough to think of.

Rant aside, Forced Parameterization will allow the creation of less query plans by parameterizing values within submitted sql queries. Later queries will be compared in their parameterized form against those in the plan cache.

For example;

With Parameterization = SIMPLE, the following plans could all exist >

SELECT Forename FROM Person.Contact WHERE ID = 1

SELECT Forename FROM Person.Contact WHERE ID = 3

SELECT Forename FROM Person.Contact WHERE ID = 6

SELECT Forename FROM Person.Contact WHERE ID = 13

With Parameterization = FORCED, only the 1 plan would be stored >

SELECT Forename FROM Person.Contact WHERE ID = (@P1)

I covered Viewing and clearing the plan cache here. It's a useful post for debugging query caching.

Setting Parameterization to FORCED >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

Returning Parameterization to SIMPLE >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

Setting all dbs on a server to FORCED parameterization >

exec sp_msforeachdb @command1= 'ALTER DATABASE ? SET PARAMETERIZATION FORCED

Links :
SQL Authority
How SQL Server 2005 "Forced Parameterization" cut ad-hoc query CPU usage by 85%
Strictly Software : Optimizing a query with Forced Parameterization

SSIS : OLEDB Destination and Maximum Insert Commit Size

SSIS : OLEDB Destination and Maximum Insert Commit Size

Default is 0 in SSIS 2005.

Default is 2147483647 in SSIS 2008.

Source : SSIS Junkie : Default value for OLE DB Destination FastLoadMaxInsertCommitSize in SQL Server 2008

"Using 0 (2005) or 2147483647 (2008) gives basically the same behavior - all records are committed as a single batch. Depending on exactly what you are doing, you may get better performance by setting MICS to a value between 10,000 and 100,000 - particularly if you have a clustered index on the table you are loading "
from - MSDN SSIS forums

Further reading -
Making Fast Load really fast on clustered indexed tables with SSIS

SQL Server Integration Services (SSIS) - Best Practices

Using Maximum Insert Commit Size with FAST LOAD on the OLE DB Connector, and a Warning

Column Size Checker

This is a utility script I wrote to show how well your column sizes suit your data (or vice versa)..
It has been published on SQLServerCentral.com  today.

SSC : Column Size Checker

March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script    : Column Size Checker
Version   : 1.0 (March 2010)
Author    : Richard Doering
Web       : http://sqlsolace.blogspot.com
*/

SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = ''
SET @TABLE  = ''

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @COLUMNMINSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     TABLE_ROWS      BIGINT,
     MAX_LENGTH      INT,
     DATA_MIN_LENGTH INT,
     DATA_MAX_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           TABLE_ROWS,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      R.SUMROWS   AS TABLE_ROWS,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
INNER JOIN (SELECT OBJECT_ID, SUM(ROWS) AS SUMROWS FROM SYS.PARTITIONS WHERE INDEX_ID IN (0,1) GROUP BY OBJECT_ID) R
  ON R.OBJECT_ID = O.OBJECT_ID
      
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = CASE WHEN @TABLE = '' THEN OBJECT_NAME(O.OBJECT_ID) ELSE @TABLE END
AND SCHEMA_NAME(O.SCHEMA_ID) = CASE WHEN @SCHEMA = '' THEN SCHEMA_NAME(O.SCHEMA_ID) ELSE @SCHEMA END
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0     
SET @COLUMNMINSIZE = 0  
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMIN = MIN(LEN([' + COLUMN_NAME + '])) ,@COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK)'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT,@COLUMNSIZEMIN INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
                          , @PARAMETER 
                          , @COLUMNSIZEMIN = @COLUMNMINSIZE OUTPUT   
                          , @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    DATA_MAX_LENGTH = ISNULL(@COLUMNMAXSIZE,0)  , DATA_MIN_LENGTH = ISNULL(@COLUMNMINSIZE,0)   
WHERE  UNIQUEROWID = @CURRENTROW                               

SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
        ,TABLE_NAME
        ,TABLE_ROWS
        ,COLUMN_NAME
        ,COLUMN_TYPE
        ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
        ,DATA_MIN_LENGTH 
        ,DATA_MAX_LENGTH 
FROM @TABLEDETAILS 
ORDER BY 1, 2, 3

PHP / FreeTDS / ANSI NULLS

Came across a problem today where default database settings for ANSI NULLS came into play.

Developers running queries from the application recieved application errors yet the same queries in management studio ran fine.

Have set database defaults according to the screenshot below now (the PHP app communicating via FreeTDS didnt set any connection properties).

Cannot create a filtered index on a computed column

Wanted to attempt a filtered index today, but got stopped dead in my tracks by this...

Filtered index 'ix_IPs_1' cannot be created on table 'schemaname.tablename' because the column '_lookupchannel' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

Msg 10609, Level 16, State 1, Line 10

So Filtered Indexes cannot be defined on computed columns. Not even PERSISTED ones :(

Microsoft Connect Suggestion to be able to define Filtered Indexes on computed columns in a future edition (please vote!)

Links :
Greg Low : Odd that you can't...
Little Techie
Solace : Filtered Indexes

Character Padding Function

This is a function I've written to align fixed character strings to a fixed size.output.It has been published on SQLServerCentral.com  today.

SSC : Character Padding Function

March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script  : Character Padding Function
Version : 1.0 (April 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
CREATE FUNCTION dbo.CharPad (
  @Input VARCHAR(255)
 ,@OutputWidth INT
 ,@OutputAlign VARCHAR(5)
 ,@PadCharacter CHAR(1) )
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Output VARCHAR(255)
DECLARE @InputWidth INT

SET @InputWidth = LEN(@Input)

IF @InputWidth > @OutputWidth
 BEGIN 
  IF @OutputAlign = 'LEFT'
   BEGIN
   SET @Output = LEFT(@Input,@OutputWidth)
   END
  IF @OutputAlign = 'RIGHT'
   BEGIN
   SET @Output = RIGHT(@Input,@OutputWidth)
   END
 END

IF @InputWidth < @OutputWidth 
 BEGIN 
  IF @OutputAlign = 'RIGHT'
   BEGIN
   SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input
   END
  IF @OutputAlign = 'LEFT'
   BEGIN
   SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth )
   END
 END

IF @InputWidth = @OutputWidth 
 SET @Output = @Input


RETURN (@Output)
END
GO

Bookmark : Database Refactoring

An excellent article on Simple Talk by Nick Harrison...

A Developers' Guide to Refactoring Databases

Bookmarks : Company size , the pros & cons

Another link from SSC I'm filing here so I find it again.

Buck Woody discusses the pros and cons of small, medium and large companies in 'Your next IT job'.

Bookmark : DB Instrumentation

Was sent this by a colleague...

4 ways that instrumentation is like sex

GO n

Never knew this!
If you place a number (n) following the GO statement, Management Studio loops, running the preceding SQL statement n times. e.g....

CREATE TABLE dummy (ID INTEGER IDENTITY(1,1), data UNIQUEIDENTIFIER)
GO

INSERT INTO dummy (data) VALUES (NEWID())
GO 500

SELECT ID, data FROM dummy

Observation : Definining a Column Default

If a DATETIME column allows NULLS and subsequently has a  Default Value (e.g. GETUTCDATE() assigned to it, the default constraint does not function.when new rows enter the table.

The solution is to populate the column with DATETIME values (incrementally if necessary) and then ALTER the table to disallow NULL values on the column.

The default constraint will then function.

Default Columns :http://www.blackwasp.co.uk/SQLDefaultColumns.aspx


Much easier of course, is to define the column and default at the same time e.g.
(If you are the developer/architect of your application that is...)

ALTER TABLE Common.ipAddresses ADD ipEntryDate DATETIME2(0) NOT NULL
CONSTRAINT DateTimeRightNow DEFAULT GETDATE()

TSQL Division

I've lost count of the number of times I've revisited this, so let's document it.

Dividing numbers in sql >

SELECT 73345888 / 394239526

This returns 0. The result has been rounded.

I'll try CASTing the result as a float...

SELECT CAST(73345888 / 394239526 AS FLOAT)

This also returns 0.

If we play with the precision of either the divisor or the dividend, we get what we want...

SELECT 73345888 / 394239526.0
SELECT 73345888.0 / 39423952

Both of these return 0.18604397368

As does casting them before division...

SELECT CAST(73345888 AS FLOAT) / CAST(394239526 AS FLOAT)

or the long way...

DECLARE @numeric1 FLOAT
DECLARE @numeric2 FLOAT
SET @numeric1 = 73345888
SET @numeric2 = 394239526
SELECT @numeric1 / @numeric2

Link : http://www.codeguru.com/forum/showthread.php?t=458369

More SSIS Obscurity (SSIS error 0xC0010009)

Another long winded, difficult to decipher error message from SSIS today..

[Pass Maximum ID Back as variable [378]] Error: System.Runtime.InteropServices.COMException (0xC0010009): Exception from HRESULT: 0xC0010009
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)

Posting this incase anyone else (like me) finds themself googling SSIS error 0xC0010009.
It translates to 'Item in a collection not found' and in my case was caused by me using camelCase variable names in my SSIS package and attempting to reference them in lowercase. Argh!!!! Note to self, variable names are CASE SENSITIVE!
 
http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0010009.html