The following will create a user defined function (in the master table), and will allow a user to essentially split an entire string into individual rows by a character.

Why use it? I found it to be useful for creating a translation/obfuscation results.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2014-04-07
 Description: Splits a string into a table
  Parameters:
    @Input - String to split into a table
     Returns: Table
                Id INT
                Value NVARCHAR(1)
*/

IF EXISTS (SELECT * FROM sys.objects WHERE name='udf_TextToTable' AND schema_id=SCHEMA_ID('dbo') AND type='TF')
BEGIN
    DROP FUNCTION dbo.udf_TextToTable
END
GO

CREATE FUNCTION dbo.udf_TextToTable
(
    @Input NVARCHAR(MAX)
)
RETURNS 
@Return TABLE (Id INT, Value NVARCHAR(1))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    INSERT INTO @Return
        SELECT  T.ID, SUBSTRING(@Input, T.ID, 1) Value
        FROM    master.dbo.Tally T
        WHERE   T.ID BETWEEN 1 AND LEN(@Input)

    RETURN
END
GO

SELECT * FROM dbo.udf_TextToTable('Welcome to Denver Code Monkey!')

The following script will drop all of the SQL Server objects.

PLEASE. USE. CAUTION.

DECLARE @n CHAR(1)
SET @n = CHAR(10)

DECLARE @stmt NVARCHAR(MAX)

PRINT 'Dropping Stored Procedures'
-- PROCEDURES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.procedures

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Check Constraints'
-- CHECK CONSTRAINTS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.check_constraints

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Functions'
-- FUNCTIONS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP FUNCTION [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.objects
WHERE   TYPE IN ( 'FN', 'IF', 'TF' )

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Views'
-- VIEWS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP VIEW [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.views

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Foreign Keys'
-- FOREIGN KEYS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.foreign_keys

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Tables'
-- TABLES
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP TABLE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.tables

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Types'
-- USER DEFINED TYPES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP TYPE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.types
WHERE   is_user_defined = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Schemas'
-- SCHEMAS
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP SCHEMA [' + name + ']'
FROM    sys.schemas
WHERE   name <> N'dbo'
  AND   principal_id = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

The following script will handle Full backups, Differential backups, and transaction log backups.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2013-12-26
 Description: Backup Databases
  Parameters:
    @DatabaseName   - Name of the database
    @BackupType     - F=full, D=differential, L=log
    @BackupLocation - backup directory location
*/ 
CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
    @DatabaseName NVARCHAR(100),
    @BackupType CHAR(1),
    @BackupLocation NVARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON; 
           
    -- Declare variables
    DECLARE @BackupName NVARCHAR(100),
            @BackupFile NVARCHAR(100),
            @SqlCommand NVARCHAR(1000),
            @Date NVARCHAR(8),
            @Time NVARCHAR(6)

    -- Provide the backup a name for storing in the media
    SELECT  @Date = REPLACE(CONVERT(VARCHAR, GETDATE(), 102), '.', ''), --YYYYMMDD
            @Time = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), --HHMMSS
            @BackupFile = @backupLocation + REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') +
                CASE
                    WHEN @BackupType='F' THEN '_FULL_' + @Date + '_' + @Time
                    WHEN @BackupType='D' THEN '_DIFF_' + @Date + '_' + @Time
                    ELSE '_LOG_' + @Date + '_' + @Time
                END + 
                CASE
                    WHEN @BackupType='F' THEN '.BAK'
                    WHEN @BackupType='D' THEN '.BAK'
                    ELSE '.TRN'
                END, --[c:\directory\][Database]_[LongType]_[Date][_Time].[Ext]
            @BackupName = REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') + ' ' +
                CASE
                    WHEN @BackupType='F' THEN 'full'
                    WHEN @BackupType='D' THEN 'differential'
                    ELSE 'log'
                END + ' backup for ' + @Date + '_' + @Time, -- [Database] [BackupType] backup for [Date]_[Time]
            @SQLCommand = 'BACKUP ' +
                CASE
                    WHEN @BackupType='F' THEN 'DATABASE'
                    WHEN @BackupType='D' THEN 'DATABASE'
                    ELSE 'LOG'
                END + ' ' +
                @DatabaseName + ' TO DISK = ''' + @BackupFile + ''' WITH ' +
                CASE
                    WHEN @BackupType='D' THEN 'DIFFERENTIAL, '
                    ELSE ''
                END + 'INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'

    -- Execute the generated SQL command
    EXEC(@sqlCommand)
END

Run it as the following

--Full Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'F', 'c:\temp\'

--Differential Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'D', 'c:\temp\'

--Transaction Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'L', 'c:\temp\'

The following Tally Table will help in future posts.

The Tally table is simply a table with an ID column, that will help in reducing most loops; a personal preference of mine.

CREATE TABLE dbo.Tally (
    ID INT NOT NULL
)
GO
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally
    PRIMARY KEY CLUSTERED ( ID )
GO

--2048 * 8 = 16384 Results
INSERT INTO dbo.Tally
    SELECT  a.number * 2048 + b.number + 1 ID
      FROM  [master].dbo.spt_values a,
            [master].dbo.spt_values b
    WHERE   a.[type] = 'p'
      AND   b.[type] = 'p'
      AND   b.number < 2048
      AND   a.number < 8
    ORDER BY a.number, b.number
GO

If you want more results, you will need to mess with the “a.number < 8" line in the where clause. Each increase will result in an extra 2048 entries. I find that 16k+ suits my needs just fine.

If you are anything like me, you want to know if your updates to your database have improved on a 0-60 basis or not.

Unfortunately, if you run the same query back-to-back, then you will often run into SQL Server caching the query and optimizing your results.

The following will clean out the cache for your testing and analysis purposes.

USE {DATABASE};
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Recently I was working on updating some tables, and the SQL that was generated for the update included some code that looked like this:

ALTER TABLE [schema].[table]
    DROP CONSTRAINT [DF__Table__Column__a789cv78s7s]

Well, it dawned on me that the various environments we have *COULD* have different constraint names.

Now, these constraints will be deleted when the table is dropped, so an explicit drop is not necessarily required.

However, if you actually name your default constraints, rather than utilize the shorthand in the table definition, then you may run into an issue of duplicate default constraint names.

This handy script will drop all of the defaults on a particular table.

DECLARE @SchemaName NVARCHAR(256),
        @TableName NVARCHAR(256),
        @SQL NVARCHAR(MAX),
        @NewLine CHAR(1)

SELECT  @SchemaName = N'dbo',
        @TableName = N'TheNameOfTheTable',
        @NewLine = CHAR(10)

SELECT  @SQL = ISNULL(@SQL + @NewLine, '') + 
            'ALTER TABLE [' + S.name + '].[' + T.name + '] ' +
                'DROP CONSTRAINT [' + D.name + ']'
FROM    sys.tables T
    INNER JOIN sys.default_constraints D
        ON D.parent_object_id = T.object_id
    INNER JOIN sys.columns C
        ON C.object_id = T.object_id
            AND C.column_id = D.parent_column_id
    INNER JOIN sys.schemas S
        ON T.schema_id = S.schema_id
WHERE   S.name = @SchemaName
  AND   T.name = @TableName

PRINT @SQL
EXECUTE (@SQL)

So I’m sure there are a ton of resources already out there regarding this, so this is more for me.

To use the BULK INSERT command, you’ll first need to create your data file.

ID|StateID|ZipCode|City|County|State|TimeZone|PopulationPercent|FIPS
1|33|00501|HOLTSVILLE|Suffolk|NY|EST|100|36103
2|33|00501|I R S SERVICE CENTER|Suffolk|NY|EST|100|36103
3|33|00544|HOLTSVILLE|Suffolk|NY|EST|100|36103
4|33|00544|IRS SERVICE CENTER|Suffolk|NY|EST|100|36103
5|58|00601|ADJUNTAS|Adjuntas|PR||100|43001
...

Then you’ll need to create the SQL statement.

BULK INSERT [Table]
FROM 'c:\Dir\File.txt'
WITH (
     DATAFILETYPE = 'char',
     FIELDTERMINATOR = '|',
     FIRE_TRIGGERS,
     MAXERRORS = 50,
     TABLOCK
     )

And there you go!

Much faster than a crap-ton of insert statements 🙂

This is pseudo code, but you should be able to get the idea from here…

Will develop more as requested…

DECLARE @t TABLE (Columns Def, repeat ...)

insert into @t
  select from flat_file

insert into parent
  select parent, fields from @t

insert into child
  select p.parentid, c.child, c.fields
  from	@t c
    inner join parent p
      on c.parentkey = p.parentkey