Dynamic SQL - Drop Keys Truncate Tables and Add Keys

So I need to do the same routine when deploying to a test environment or a stage database when I add columns in the middle of a table or change the nullability of a column. Getting the column definition for a constraint can be a pain, so I wanted to record this here for reuse (thanks to Pinal Dave for keeping the old keys sys tables query online).


USE <DATABASE>


-------------------- BEGIN - Initialize System State Tables -------------------

DECLARE

@name VARCHAR(128),

@schema VARCHAR(128),

@tableSchema VARCHAR(128),

@tableName VARCHAR(128),

@sqlCommand NVARCHAR(1000),

@Rows INT,

@i INT ;

DECLARE @sysFKTable TABLE (RowID INT IDENTITY(1,1), ConstraintName sysname, SchemaName sysname, TableName sysname, ColumnName sysname, ReferenceSchemaName sysname, ReferenceTableName sysname, ReferenceColumnName sysname)

DECLARE @schemaTbl TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128), IndexName VARCHAR(128));

DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

DECLARE @t1 TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

PRINT 'Load @sysFKTable'

INSERT INTO @sysFKTable

SELECT f.name AS ConstraintName,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id



-- ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

PRINT 'Load @schemaTbl'

INSERT INTO @schemaTbl(SchemaName, TableName, IndexName)

select object_schema_name(object_id), object_name(object_id), name

from sys.indexes

where type > 0 AND object_schema_name(object_id) != 'sys'

PRINT 'Load @t for table trunc'

INSERT INTO @t(SchemaName, ObjectName)

SELECT

tc.TABLE_SCHEMA,

tc.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'FOREIGN KEY')

ORDER BY SUBSTRING(tc.CONSTRAINT_NAME,PATINDEX('%o[_]%',tc.CONSTRAINT_NAME)+2,100) DESC

PRINT 'Load @t1 for table trunc'

INSERT INTO @t1

SELECT t.TABLE_SCHEMA, t.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES t

WHERE t.TABLE_TYPE = 'BASE TABLE'

ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME DESC





PRINT 'This replaces merge statement as this targets sql server 2005'

INSERT INTO @t

SELECT

t1.SchemaName,

t1.ObjectName

FROM @t1 t1

WHERE

t1.ObjectName NOT IN (SELECT t.ObjectName FROM @t t)



-------------------- END - Initialize System State Tables -------------------

-------------------- BEGIN - Drop FKs ---------------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'ALTER TABLE [' + t.SchemaName + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ConstraintName + '];',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'DROP CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;



END



--------------------------- END - Drop FKs---------------------------------

--------------------------- BEGIN - TRUNCATE TABLES------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @t)

SET @i = 1;

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'TRUNCATE TABLE [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'TRUNCATE TABLE: [' + @schema + '].[' + @name + ']';

SET @i = @i + 1;



END

------------------------- END - Truncate Tables ------------------------------

------------------------- BEGIN - ADD BACK CONSTRAINTS -----------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

BEGIN TRY

SELECT @sqlCommand = 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] WITH CHECK ADD CONSTRAINT [' + ConstraintName

+ '] FOREIGN KEY([' + ColumnName + ']) REFERENCES [' + ReferenceSchemaName + '].[' + ReferenceTableName

+ '] ([' + ReferenceColumnName + '])',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'ADD CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END TRY

BEGIN CATCH

PRINT 'Error in creating constraints'

END CATCH



END

------------------------- END - Add FKs ----------------------------------------

------------------------- BEGIN - INDEX MGMT -----------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @schemaTbl)

SET @i = 1;

WHILE (@i <= @Rows)

BEGIN

 SELECT @sqlCommand = 'ALTER INDEX ' + t.IndexName + ' ON ' + t.SchemaName + '.' + t.TableName + ' REBUILD;',

@schema = t.SchemaName, @name = t.IndexName, @tableSchema = t.SchemaName, @tableName = t.TableName

FROM @schemaTbl t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'REBUILD INDEX: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END

GO ------------------------- END ------------------------------------------------

Powershell - File Date Change Routine

There are times you need to have files that are created and modified on a specific date for integration testing some file management or metadata gathering routine. This is a simple routine to assist in that.

$varFileName=read-host "Enter path and file name e.g. c:\dir\file.zip"
$varCreatedDateOffset=read-host "Enter the created date offset in days e.g. -7"
$varModifiedDateOffset=read-host "Enter the modified date offset in days e.g. 0"

    $change = Get-Item $varFileName
    $createDate = Get-Date
    $modDate = Get-Date
    $createDate = $createDate.AddDays($varCreatedDateOffset)
    $modDate = $modDate.AddDays($varModifiedDateOffset)
    $change.CreationTime = $createDate
    $change.LastWriteTime = $modDate
    Get-Item $change|Select-Object Mode,CreationTime,LastWriteTime,Length,Name

What happens when a transaction dies a terrible blocking death in SQL Server

USE <DATABASE>

DECLARE @UoWID uniqueidentifier
SET @UoWID = (
select TOP 1 req_transactionUoW as [UoW ID] from sys.syslockinfo where req_spid = -2)

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'KILL ''' + CAST(@UoWID AS VARCHAR(300)) + ''''

SELECT @SQL
EXEC sp_executesql @SQL

REFS:
http://www.sqlskills.com/blogs/paul/disaster-recovery-101-dealing-with-negative-spids-2-and-3/
http://www.eraofdata.com/orphaned-msdtc-transactions-2-spids/

Start a Peek at Clojure

Many tutorials are old and have outdated installation paths and environment hacks. I am sure these instructions will suffer no worse the ravages of time. However they worked at the time of this writing.


Terms
REPL
LEIN
MAP
VECTOR

Install
Clojure can be run in Windows (with or without Cygwin), OS X, and Linux. Do yourself a favor and create an Ubuntu partition on your machine. I am using Ubuntu 13.10.

• Update Ubuntu OS
• Install Current Java JRE
• Install Java Clojure library. I use apt-get leiningen to install clojure 1.4, lein, and Leiningen.
• Unfortunately 1.4 was not the current version of Clojure. So I needed to Download and unzip the Clojure jar. <<NEEDS WORK>> I opened a command line cd to the directory in which I have my clojure fines (cd /usr/share/maven-repo/org/clojure/clojure) expanded clojure jar 

  java -cp clojure-1.6.0.jar clojure.main

• Install Eclipse Java EE IDE. Using apt-get Eclipse was a terrible idea. The  version of the package available through apt-get is very old. Get the tar ball for Kepler or the highest version supported by the Clojure community.
• Make sure you can see the Eclipse Help menu. If you cannot, edit the Eclipse shortcut properties to use the command: env UBUNTU_MENUPROXY=0 /usr/lib/eclipse/eclipse
• Using the Eclipse Market place from the Help menu, install the counterclockwise extension. This provides a Clojure perspective.

Some people want to use emacs to code. Not really my style but here are some notes:

1. Pick an Emacs, install it. Everyone uses emacs
   
2. Install ELPA
   
3. Use ELPA to install the Emacs support for Clojure
   
4. Install Leiningen
   
5. Check project out of source control, use Lein to build it and run Swank, connect to that from Emacs.
   

Tutorial Listhttp://learn-clojure.com/clojure_tutorials.html
https://www.codeschool.com/code_tv/getting-started-with-clojure-part-2
http://doc.ccw-ide.org/documentation.html

References
Leiningen, a Clojure project build tool you will inevitably use to some extent.
http://code.google.com/p/counterclockwise/
https://github.com/technomancy/emacs-starter-kit
https://www.eclipse.org/downloads/

Don't use SourceForge nor Google Code

http://clojure.org/

Disorganized notes from JP meeting
Nike uses scala?!

(def tax-payer(

:first "Eric"

:last "Ald"

))

Jvm RUNNING THE CLOSURE source generates classes at run time.. Or as read eval print loop (REPL) can modify code as being executed?

Keyword type , starts with : make good keys on maps. A kind of function that can make all kinds of inputs and reduces the potential outputs…

Nil in closure = null in c#

[1,2,3, "word", :base] this  is a vector, like anarray or tuple…

Supports dynamic types..

Get - get values out of a map

Idiomatic closure (how to pass key words and map as function..also pass map object as function)

'(1,2,3)

See that single quote, it makes this just a data set not evaluated

Pst - REPL stack trace

You can filter the exception report by writing your own library

Network REPL in production using a TCP/IP connection to allow real time live debugging!

Operator precedence rules…none in clojure as there are no operators because all is functions

Defn define new function

Sample map

{

:rank :ace

:suit :club

}

(defn points [rank]

(case))

(points (:rank flip-card))

Thread first macro….

(--> flip-card

:rank

points)

Thread first/thread last macros

Let word..local symbols/variables

First, rest, next

First - first in collection

Rest remaining exclude nil

Next remaining or nil

Clojure conventions (isTrue? Is acceptable name)

#(= first-suit %)

Cond  - eval a condtion, like an if/then

Empty list is a false value as does nil, but nil and empty list are different values so you need to test for both conditions

Iterate - take a function set and create new sets from them

Iterate first (1,2,3)

first (1,2,3)

first (2,3)

first (3)

first ()

Does this work like it did with iterate rest?

Macros and functions live in the same name space during execution, macros are extensions

Mapping functions over collections

Macro expansion expands macro code down to byte code…special forms

Macros take parms

What does ~ represent in a macro..instance of a parm t be used later

Self Defined macros are increase complexity of debugging code

Clojars and leiningin --- distro

Lighttable IDE

Nightcode---mac only?

4clojure learning site vs clojure koans

Cognicast broabcast learning

Look at datonic non sql database that goes with this

Ring compojure - web crap

Code.test specj , clojure.test- testing frameworks

TSQL - Indexing Notes

These are a few ideas I wanted to jot down while designing a snowflake data mart.

A clustered index determines the physical order of data in a table. Ordered values received non-contiguously can cause fragmentation.

A clustered index is efficient when included columns are searched for ranges of values, or when finding a specific row when the indexed value is unique.

There can only be one clustered index to rule the table.

A clustered index cannot be applied to a table variable.

Recall that all columns in the clustered index are always included in all other indexes on that table. The columns defined in the clustered index are appended to the end if the other indices.

The PK creates a clustered index by default, as long as no other clustered index resides on the table at the time of the PK creation. You can use the NONCLUSTERED key word in the PK definition to override this.

In a snow-flake data mart, the fact PK can be comprised of the combination of dimensional FKs. A fact can have up to 20 dimensions generally. You would not want to have the PK clustered in this case, as it would be too wide. It would take too much space and may limit other indexing strategies for other query requirements.

Create the PK as such

CONSTRAINT [PK_FacilityFact] PRIMARY KEY NONCLUSTERED ([ClientID], [EfftiveDate], [FacilityID], [AddressID], [PatientID])

And add the clustered index as such

CREATE CLUSTERED INDEX [IX_FacilityFact_DateClient] ON [dbo].[FacilityFact] ([EffectiveDate], [ClientID])

Avoid adding the columns in the clustered index to additional indices.
--will include [EffectiveDate], [ClientID] from clustered index
CREATE INDEX [IX_FacilityFact_Address] ON [dbo].[FacilityFact] (AddressID)

Basic Fact and Dimension Guidelines

I find it difficult to locate a concise definition of what differentiates a fact and a dimension.

Fact Tables
􀂃 For every business process there should be at least one fact table. make a list of nouns describing the process or interaction.
􀂃 Each item in a fact table should have a default aggregation (or derivation) rule--e.g., sum, min, max, semi-additive, not additive. Any complexities in the aggregation method must be documented. Enter the aggregation/derivation information at the end of the definition. Begin a new paragraph and use the following format:
􀂃 The grain, or granularity, of the fact table should be at the lowest level for which a need has been identified and a requirement approved. Performance and storage constraints must also be considered.
􀂃 The grain of all items in the fact table should be the same. If there is a need for aggregation at more than one level, a separate fact table for each level of aggregation may be needed. Note: Aggregation tables are either (a) transparent to the user, such that all SQL is written to go against the lowest level of granularity, or (b) explicit--i.e., seen and queried by the user. Since the dimensional model is, among other things, a tool for communication with the user, it is preferable to include only those tables that the user will see.
Attributes describing the fact should be put in dimensional tables.
Dimension Tables
􀂃 Each dimension table has one and only one lowest level element, called the dimension grain.
􀂃 Dimension tables that are referenced or are likely to be referenced by multiple fact tables are "conformed dimensions." If conformed dimensions already exist for any of the dimensions in the model, their reuse is expected. If new dimensions with potential for usage across the agency are being developed, the design must support anticipated cross-agency needs.
􀂃 Each non-key element should appear in only one dimension table.
􀂃 Most models should have at least one period or time dimension. There may be more than one period dimension. Date and time may be split into two separate dimensions, especially if time is being captured at the hour or minute level.
􀂃 If a dimension table includes a code, in most cases the code description should be included. For example, if branch locations are identified by a branch code, and each code represents a branch name, both the code and the name should be included. An alternative is to include the description and omit the code--e.g., State = California, Status = Active.
􀂃 Generally, there should be no more than twenty dimension tables per fact table; the designer should provide justifications if more than twenty dimension tables are required.
Keys
􀂃 The primary key of a dimension table should be a surrogate key. A source system production key should not be used as a primary key.
􀂃 The primary keys of the dimension tables should be included in the fact table as foreign keys. Together these (and only these) foreign keys make up the fact table primary key (in the logical view).

Reporting: Summary Data in Detail View

Ever need to have a summary column for the whole record set in each line of a detail record set? If so, here is a way to provide that in a view or report.


DECLARE @TblDetail TABLE
(DetId INT Identity (1,1), SumID INT, Descr Char(2), Num SmallInt)
DECLARE @TblSum TABLE
(SumId INT Identity (1,1) , Descr Char(2))
INSERT INTO @TblSum
(Descr)
VALUES
('AA'),
('BB')
INSERT INTO @TblDetail
(SumID, Descr, Num)
VALUES
(1,'AB',2),
(1,'AC',32),
(2,'CA',2);
WITH SumDetail (SumID, SumNum)
AS(
SELECT
ts.SumID,
SUM(td.num)
FROM @TblSum ts
join @TblDetail td on ts.Sumid = td.sumid
GROUP BY
ts.SumID)
SELECT
td.*,
sd.SumNum Summary
FROM @TblSum ts
join @TblDetail td on ts.Sumid = td.sumid
join SumDetail sd on sd.sumid = td.sumid