Abbreviations for SQL Server Components when Installing with PowerShell

I have (finally) been introduced to installing SQL Server with PowerShell and it has made those installations much easier.  Each of my installs; however, may require different SQL Server components so one of the items in my configuration file I change is the Features= section of the INI file.  To help keep all this information in one place, here is a list of items you can choose to install.  Remember, most of these will require other parameters to be set–this is only for the Features= section.  Each feature should be separated by a comma.

SQLInstalFeatureNames

SQL Server Component Install Options

This list specifies the items you can choose to install.

Database engine = SQLENGINE
Replication = REPLICATION
Full-text and semantic extractions for search = FULLTEXT
Data quality services = DQ
Analysis services = AS
Reporting services – native = RS
Reporting services – sharepoint = RS_SHP
Reporting services add-in for sharepoint products = RS_SHPWFE
Data quality client = DQC
SQL Server data tools = BIDS
Client tools connectivity = CONN
Integration services = IS
Client tools backwards compatibility = BC
Client tools SDK = SDK
Documentation components = BOL
Management tools – basic = SSMS
Management tools – advanced = ADV_SSMS
Distributed replay controller = DREPLAY_CTLR
Distributed replay client = DREPLAY_CLT
SQL client connectivity SDK = SNAC_SDK
Master data services = MDS

Happy installing. :)

Updating Default Values for Columns

To make life a little simpler, a default value can be added to a column so if the information is not passed with the row data, SQL Server can plug in some information for you.  A common scenario is to add a default DateTime of the current system time.  This image show what that looks like when you design the table in SSMS.

DefaultBinding

What happens if you wanted to change the default value option for those tables?  For example, we are preparing to move to Azure SQL and our default options need to be GETUTCDATE() instead of GETDATE().  I put together this script to help me identify all the constraints in the database that need to be updated. This code has the new lines so you should be able to run the output. Don’t forget to update your data before you made the change. You can find that example here.

[sourcecode language=”SQL”]
SELECT
‘ALTER TABLE ‘ + t.name + ‘ DROP CONSTRAINT ‘ + dc.name + ‘
GO
‘ + ‘ALTER TABLE ‘ + t.name + ‘ ADD CONSTRAINT ‘ + dc.name + ‘ DEFAULT GETUTCDATE() FOR ‘ + c.name + ‘
GO’
FROM sys.default_constraints dc
INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE dc.parent_column_id = c.column_id
AND dc.definition = ‘(getdate())’
[/sourcecode]

Converting to UTC Time

In preparation for a migration to Azure SQL Database, we found we had to adjust our date formats to UTC time as this is the way dates are stored in azure because your databases could be all over the world.  For new systems, this is very useful; however, what about migrating data to Azure?  I have tables with dates based on a GETDATE() default and this time will be the time zone for the server where the record was created.  I wanted to convert my dates to UTC time and came up with this script.  It is based on servers in the EASTERN time zone.  As the date for daylight savings time changes each year, I used timeanddate.com for the historical data.

Suppose I have a table named Customers with a column called CreationDate and I wanted to update my times to UTC. I would apply this logic. As always, test before implementation. I put an ELSE statement at the end so I could check to see if any dates fell outside my range. This only goes back to 2009. You would have to consult timeanddate.com for more dates if you have older data.

[sourcecode language=”sql”]
UPDATE Customers
SET CreationDate = CASE
WHEN YEAR(CreationDate) = 2009 AND CreationDate BETWEEN ‘2009-03-08 02:00:00.0000000’ AND ‘2010-11-01 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2009 AND CreationDate NOT BETWEEN ‘2009-03-08 02:00:00.0000000’ AND ‘2010-11-01 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate BETWEEN ‘2010-03-14 02:00:00.0000000’ AND ‘2010-11-07 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate NOT BETWEEN ‘2010-03-14 02:00:00.0000000’ AND ‘2010-11-07 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate BETWEEN ‘2011-03-13 02:00:00.0000000’ AND ‘2011-11-06 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate NOT BETWEEN ‘2011-03-13 02:00:00.0000000’ AND ‘2011-11-06 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate BETWEEN ‘2012-03-11 02:00:00.0000000’ AND ‘2012-11-04 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate NOT BETWEEN ‘2012-03-11 02:00:00.0000000’ AND ‘2012-11-04 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate BETWEEN ‘2013-03-10 02:00:00.0000000’ AND ‘2013-11-04 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate NOT BETWEEN ‘2013-03-10 02:00:00.0000000’ AND ‘2013-11-04 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate BETWEEN ‘2014-03-09 02:00:00.0000000’ AND ‘2014-11-02 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate NOT BETWEEN ‘2014-03-14 02:00:00.0000000’ AND ‘2014-11-02 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate BETWEEN ‘2015-03-08 02:00:00.0000000’ AND ‘2015-11-01 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate NOT BETWEEN ‘2015-03-08 02:00:00.0000000’ AND ‘2015-11-01 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate BETWEEN ‘2016-03-13 02:00:00.0000000’ AND ‘2016-11-06 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate NOT BETWEEN ‘2016-03-13 02:00:00.0000000’ AND ‘2016-11-06 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
ELSE ‘2046-01-01 11:07:20.6500000’
END
FROM Customers
[/sourcecode]

Moving A Database With Attach and Detach

I found myself doing several database migrations this weekend and because these databases were generally small (Less than 10 GB), I choose the route to detach the database, copy the files to the new location and re-attach.  To remove the databases, I detach them from the current instance.  I am not worried about backup history with this as the server will be decommissioned.

[sourcecode language=”sql”]EXEC master.dbo.sp_detach_db @dbname = N’MyDatabase’
GO [/sourcecode]

If you refresh your database list, you will no longer see the database listed; however, the files are still on the server.  You can now copy those to the new location and attach them. You need only pass in the name of the database and the location of the files and SQL Server will add the database and make it available to you.

[sourcecode language=”sql”]EXEC sp_attach_db @dbname = N’MyDatabase’,
@filename1 = N’E:MSSQLMyDatabase.mdf’,
@filename2 = N’F:MSSQLMyDatabase_log.ldf’; [/sourcecode]

Don’t forget to add your security and any jobs on the old server you need to copy over for this database.

We Can Pivot If You Want To

Screen Shot 08-01-14 at 10.59 PMSo I get that I am a little late to the party here; however, I find myself having to pivot from time to time and am always looking back for code as a refresher.  While pivot is pretty cool, it always takes me a second to connect all the dots so I hope this post help do that.

So, with Pivot, we want to take data that looks like the data on the right–there are multiple rows for each ProductId and would like to get each product ID on one line with the quantities from each Bin.

When we are finished, the data will look like this.

Screen Shot 08-01-14 at 10.59 PM 001

One row for each product id

In my example, I am using the [Production].[ProductInventory] table in the adventureworks2012 database.  To make a row become columns, we must know ALL possible values we want to make as a column.  We specify these in the FOR line of the pivot syntax.  This query will create the columns for each value I specified in the Shelf column

[sourcecode language=”sql”]SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]
FROM [Production].[ProductInventory]
PIVOT
(
SUM (Quantity)
FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L])
) AS PVT [/sourcecode]

Screen Shot 08-01-14 at 11.13 PMI should see something like this.  We are getting closer–we have our columns, but we still a line for each record.  We only want to see one line per product id.  This is where we put the columns we want from the table and the pivot syntax together in an almost sub-query like format and select the columns as we want them to display.

The syntax is something like this.  The first two rows are the columns I want, the second two are the columns I need from the table, and then add the pivot syntax followed by an order by and presto, you have your data the way you want it.

[sourcecode language=”sql”]SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]
FROM
(SELECT [ProductID], Shelf, Quantity
FROM [Production].[ProductInventory]) p
PIVOT
(
SUM (Quantity)
FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L])
) AS PVT
ORDER BY [ProductID]
[/sourcecode]

Finding Active Heap Tables

As part of a review of a system, I look for tables without clustered indexes; however, invariably I will provide the list of tables to a development team and they will say “These are old tables we don’t use anymore”, so I am forced to provide additional statistics about the number of times a heap table is used in the database.  Based on a check found in Brent Ozar’s sp_blitz script, I pull usage information about the heap tables.  If there is no usage, they are not reported.

[sourcecode language=”sql”]
EXEC dbo.sp_MSforeachdb ‘USE [?];
SELECT DB_NAME(), t.name as TableName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_scan
FROM [?].sys.indexes i
INNER JOIN [?].sys.objects o ON i.object_id = o.object_id
INNER JOIN [?].sys.tables t ON o.object_id = t.object_id
INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.databases sd ON sd.name = ”?”
LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id
WHERE i.type_desc = ”HEAP” AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL
AND sd.name <> ”tempdb” AND o.is_ms_shipped = 0 AND o.type <> ”S”’;
[/sourcecode]

Happy Hunting