SQL Table variables

How to use table variables (introduced in T-SQL in SQL Server 2000)

First, lets look at the reason we are using a non-permanent table in the first place.  Say I have a table with 100,000 rows.  I need to join that table to another and I will only find matches in a small number of records.  Additionally, I only need a work with a few columns of mylarge table.  Joining this large table to my query can severely degrade performance.  I can often overcome this issue and increase performance by creating a table on the fly in code, loading it with the records from the large table that I will actually be working with, and using that instead.

In prior versions of SQL, the only way to do this was with a temp table.

Here is a sample of a temp table declaration:
 CREATE TABLE #MYTEMPTABLE ([object_id] INT,[name] VARCHAR(128),max_length SMALLINT);

This creates a table in the temp database (on disk).  I can then load this table like so:
INSERT INTO #MYTEMPTABLE SELECT [object_id],[name],max_length FROM sys.all_columns WHERE max_length>2000;

Note that if I were to just use the all_columns table, my query would have to do a table scann through many thousands of records to find the ones with max_length>2000.  This way we will do that one time up front, before a join.

I can work with the temp table much like a normal table:
SELECT * FROM sys.columns C INNER JOIN #MYTEMPTABLE T ON C.[object_id]=T.[object_id];

I must remember to drop the temp table when I am done with it.  Otherwise it could cause object conflicts.
DROP TABLE #MYTEMPTABLE;

The down side to doing it this way is that the few records I am working with are still being stored in a physical database (tempdb), which is always slower than RAM (though not by much if the database is on an SSD).

This code can be made to run faster (sometimes by orders of magnitude) by using a table variable instead of a temp table. 
Here is the same functionality:
DECLARE @MYTEMPTABLE AS TABLE ([object_id] INT,[name] VARCHAR(128),max_length SMALLINT);

INSERT INTO @MYTEMPTABLE SELECT [object_id],[name],max_length FROM sys.all_columns WHERE max_length>2000;

SELECT * FROM sys.columns C INNER JOIN @MYTEMPTABLE T ON C.[object_id]=T.[object_id];

Advantages of table variables over temporary tables:
1. They have well-defined scopes. 
2. They're cleaned up automatically at the end of the stored proc in which they're defined
3. They tend to result in fewer recompilations
4. They result in less locking in stored procs and transactions
5. For recordsets smaller than 100,000 they are almost always faster than a temporary table.  In 12 years, I have never run into a real world instance where using the table variable was slower than the temporary table as long as the number of rows stored in the table variable was less than 100,000.  In cases where the dataset is larger than that, you typically get more benefit from being able to define indexes on the temporary table.

Did this help you?  If so, please leave a comment!

SQL Server Setup and Maintenance Tips

Server Maintenance
- Windows Updates

Run windows updates manually and frequently. Turning on automatic updates is not advised, based on the history of windows updates breaking certain products.  Before installing updates, check the internet for reports of issues it could cause with your applications.

- Virus Scanners

Exclude MDF, NDF, LDF, BAK, TRN and BCP file extensions and make sure schedules scans don;t coincide with other scheduled tasks.

- Backups

Perform a full system backup periodically and before and after installations are run.

- Defragmentation

SANs and SSDs are less likely to get fragmented in normal operation. If a drive is used only for database files, it is less likely to need defragmentation.  Drives that hold BAK files will need frequent defragmentation.

SQL Server Setup Best Practices
- Plan the data storage scheme before installing SQL

Disk 1:  OS, page file, SQL Server binaries
Disk 2 or LUN 1: MDF files (Except tempdb)
Disk 3 or LUN 2: LDF files
Disk 4 or LUN 3: tempdb files
Disk 5 or LUN 4: BAK files

- Check for Physical File Fragmentation before creating a database or log file
- Pre-size MDF and LDF files
- Do not set auto-growth of MDF or LDF files to a percentage.  Set it to a value in MB

Revisit periodically and reset initial size if necessary

- Plan disk sizes so that you can keep free drive space above 15%
- Turn on Instant File Initialization in SQL
- Turn on Auto Statistics

SQL Server Maintenance Best Practice for a Typical GP Instance

- Use Full backup model
- Weekly (or more frequent) Full database backup, transported off site.
- Nightly incremental database backup
- Periodic log backups throughout the day

Use backup compression

- What should you back up?

master
msdb
DYNAMICS
All company databases
NEVER back up tempdb
Model only needs to be backed up when making changes to master (such as when upgrading or patching SQL Server)

- When backing up data, the tasks should be run in this order

1. Check DB
2. Reorganize indexes
3. Update Statistics
4. Backup Database

- When backup up logs, maintenance tasks are not necessary

SQL Server Manual Period Maintenance Tasks
(These tasks could be scheduled, but their results should be checked before allowing users back into the system)

- Index Maintenance

Add missing indexes
Remove unused and duplicate indexes
Monitor indexes for excessive fragmentation

- Msdb Maintenance

Periodically delete backup, restore, job, and maintenance plan history
sp_delete_backuphistory [oldest date]
Use log file viewer for others

- Shrinking

Always rebuild indexes after shrinking.  If you need to avoid growing the file with a rebuild after shrinking, then at least re-organize indexes.  Shrinking leaves indexes nearly 100% fragmented by design.
Never shrink tempdb
Shrinking should be a rare occurrence if the growth settings are tuned properly

- Monitor Drive free space

Keep more than 15% free

- Monitor SQL Server logs and Windows event logs for errors and warnings

Did this help you?  If so, please leave a comment!

SQL Decryption Paradox

In SQL 2000 and prior, an object that was decrypted using the 'with encryption' could easily be read out of the comments as clear text (many times with SQL profiler).

In SQL 2005, Microsoft removed this 'feature' by replacing the comment text with a null value and storing the encrypted SQL as an image value in sysobjvalues.  The text is just bit shifted and can be bit shifted back with a little effort.  After all, SQL has to decrypt it to re-compile it anyway.

If you spend any time at all supporting real-world environments, you will eventually find the need to debug or even repair an encrypted object that someone else wrote.  I found myself in this situation many times.  A quick google search turned up dozens of tools that promised to do this for SQL 2005 and SQL 2008.  I tried a couple and was disappointed with the results.  I also really needed something that I did not have to install on a customer's server.  Ideally, I would be able to connect to the customer's database, decrypt the proc without affecting any of their existing SQL objects, and copy paste the code into a local editor window to take a look.  I found several different discussions from which I gleaned an understanding of how to get the text back using nothing but SQL itself. (Again, logically you would expect this, since SQL decrypts the procs all the time in order to compile and run them).

Before I go further, let me state that I am not condoning stealing proprietary code.  I am also not condoning the use of SQL encryption to protect your proprietary code.

I feel that if your code is super-secret, you should use one of the following methods to protect it:

1. Put the logic in the application layer, rather than the data layer.
2. Host the databases for your customers so you can control access to the objects

Regardless of what you choose to do, above all, you should understand this: if you are not willing to support your code indefinitely (and no-one is), someone else *will* require access to it eventually, and they *will* find a way to get to it.

Ok, let me climb down off my soap box and show you what I came up with.
After some experimentation, I created a stored proc that will run on 2005 and 2008 and decrypt the majority of the stored procs I have pointed it at.  It is either all or nothing.  Either you get the entire stored proc or you get null back.

Edit: On very long procs I have noticed that it returns extra line breaks when reading across pages.  These can occur in the middle of words, so some clean up of these may be required if you want to execute the output.  Some day I may get around to fixing it programmatically.  If you get an itch and want to do it yourself, I would welcome you solution in the comments to this post.


As an additional learning experiment, I decided to encrypt the proc and create it so that it could decrypt itself.
You must use a dedicated admin connection or DAC to create and run the proc, since it requires access to sysobjvalues.

Edit:  For SQL 2012, you can connect to DAC by closing all connections and windows in SSMS, then click the Database Engine Query button on the toolbar.  Login as Admin:xxxxx using SQL authentication and sa user.

Edit: OK, so today I got tired of removing the extra line breaks after each block.  Not easy to fix.  So I added an easy to find and replace string that I can use to quickly remove those breaks.  In SSMS you can do a find/replace on the output using wildcards like this  \n|||remove|||\n  That will remove the extra line breaks and the script should compile.

--you must connect to Admin: SQLInstance

DROP PROC DecryptObject

GO

CREATE PROCEDURE DecryptObject (

     @schema nvarchar(255), 

     @ObjectName nvarchar(255))

with encryption

AS

BEGIN

     DECLARE @id INT,

          @TYPE NVARCHAR(50),

          @Statement NVARCHAR(4000),

          @IsEncrypted BIT

     --GET THE ID FOR THE OBJECT

     SELECT TOP 1 @id=object_id

     FROM sys.all_objects 

     WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF') 

          AND name = @ObjectName 

          AND SCHEMA_NAME([schema_id]) = COALESCE(@schema,'dbo') 

     IF @id IS NULL

     BEGIN

          PRINT @ObjectName + ' not found!'

          RETURN

     END

     --SEE IF IT IS ENCRYPTED

     SELECT @IsEncrypted=[encrypted]

     FROM syscomments

     WHERE [id] = @id 

          AND colid = 1

     IF @IsEncrypted = 0

     BEGIN

          PRINT @ObjectName + ' not encrypted!'

          RETURN

     END

     IF NOT EXISTS(SELECT 1 FROM sys.dm_exec_connections C INNER JOIN sys.endpoints E ON C.[endpoint_id]=E.[endpoint_id]     WHERE E.[name]='Dedicated Admin Connection'     AND C.[session_id] = @@SPID)

     BEGIN

          PRINT 'This procedure can only be run using a DAC Connection'

          RETURN

     END     

     --GET THE REST OF THE VALUES

     SELECT @ObjectName=[name],

          @TYPE=     CASE WHEN [type] = 'P' THEN N'PROCEDURE' 

                         WHEN [type] = 'V' THEN 'VIEW' 

                         WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' 

                         ELSE [type]

                    END,

          @Statement=CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' 

                         WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' 

                         WHEN [type] IN ('FN','TF','IF') THEN N' () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' 

                         ELSE [type]

                    END

     FROM sys.all_objects 

     WHERE object_id = @id

     DECLARE @BlockNumber INT,

          @Block NVARCHAR(MAX),

          @pos INT,

          @DummyBlock NVARCHAR(MAX),

          @DummyImage VARBINARY(MAX),

          @EncyptedBlock NVARCHAR(MAX),

          @EncyptedImage VARBINARY(MAX),

          @pos2 INT,

          @part1 NVARCHAR(MAX),

          @part2 NVARCHAR(MAX),

          @DummyImageSQL NVARCHAR(MAX),

          @NewText NVARCHAR(4000)

     --GET THE ECRYPTED IMAGE VALUE

     SELECT @EncyptedImage = imageval 

     FROM sys.sysobjvalues 

     WHERE [objid] = @id 

          AND valclass = 1

     BEGIN TRANSACTION

          --BUILD THE SQL FOR THE DUMMY IMAGE

          SELECT @pos2 = 1,

               @part1= N'ALTER ' + @TYPE + ' ' + ISNULL((@schema+'.'),'') + @ObjectName + ' ' + @Statement    

           

          --FILL THE REST OF THE STRINGS AND START THE SQL FOR THE DUMMY IMAGE

          SELECT @part1 = @part1 + REPLICATE('-',4000-LEN(@part1)),

               @part2 = REPLICATE('-',8000),

               @DummyImageSQL = N'EXEC(@part1'

          --PROCESS THE ENCRYPTED IMAGE ONE BLOCK AT A TIME

        WHILE @pos2 <= CEILING(DATALENGTH(@EncyptedImage) / 8000.0)

        BEGIN

               --BUILD THE SQL FOR THE DUMMY IMAGE               

               SET @DummyImageSQL=@DummyImageSQL + N'+@part2'

               SET @pos2 = @pos2 + 1

        END

                

          --FINISH THE SQL

          SELECT @DummyImageSQL = @DummyImageSQL + ')'                    

          --EXEC SQL TO CREATE DUMMY IMAGE

          EXEC sp_executesql @DummyImageSQL,

                    N'@part1 NVARCHAR(4000),@part2 VARCHAR(8000)',

                    @part1=@part1,

                    @part2=@part2

          --GO GET THE DUMMY IMAGE THAT WAS CREATED

          SELECT @DummyImage = imageval

          FROM [sys].[sysobjvalues] 

          WHERE [objid] = @id 

               and [valclass] = 1            

     ROLLBACK TRANSACTION

     SET @BlockNumber=1

     --PROCESS ONE BLOCK AT A TIME

     WHILE @BlockNumber<=CEILING(DATALENGTH(@EncyptedImage) / 8000.0)

     BEGIN

          --GET THE NEXT BLOCK FROM THE ENCRYPTED IMAGE AND THE DUMMY IMAGE

          SELECT @EncyptedBlock = SUBSTRING(@EncyptedImage, (@BlockNumber - 1) * 8000 + 1, 8000),

               @DummyBlock = SUBSTRING(@DummyImage, (@BlockNumber - 1) * 8000 + 1, 8000)

          IF @BlockNumber=1

          BEGIN          

               --BUILD THE CREATE STATEMENT HEADER

               SELECT @NewText = N'CREATE ' + @TYPE + ' ' + ISNULL((@schema + '.'),'') + @ObjectName + ' ' + @Statement + REPLICATE('-',4000)               

          END

          ELSE

          BEGIN

               --FILL IN HALF A BLOCK

               SELECT @NewText=REPLICATE('-', 4000)

          END                    

           

          --FILL HALF THE SIZE OF THE ENCRYPTED BLOCK

          SELECT @Block = REPLICATE(N'A', (DATALENGTH(@EncyptedBlock) / 2)),                        

               @pos=1

          --PROCESS ONE BIT AT A TIME

          WHILE @pos<=DATALENGTH(@EncyptedBlock)/2

          BEGIN

               --USE BITWISE OR TO DECRYPT THE BIT

               SELECT @Block = 

                    STUFF(@Block, @pos, 1, 

                         NCHAR(UNICODE(SUBSTRING(@EncyptedBlock, @pos, 1)) 

                              ^ (UNICODE(SUBSTRING(@NewText, @pos, 1)) 

                                   ^ UNICODE(SUBSTRING(@DummyBlock, @pos, 1))

                                   )

                              )

                         )

               --NEXT POSITION

               SELECT @pos = @pos+1

          END

          --PRINT THIS BLOCK BEFORE MOVING TO THE NEXT, THIS WILL HAVE THE EFFECT OF 

          --GENERATING ONE LONG STATEMENT IN THE RESULTS WINDOW

          PRINT @Block
  print '|||remove|||'
          SELECT @BlockNumber = @BlockNumber + 1

     END   

END

GO

exec DecryptObject 'dbo','DecryptObject'

Did this help you?  If so, please leave a comment!

Script to update WSRepts table for SRS

SELECT *
FROM WSRepts
UPDATE WSRepts
SET ReportLocation='http://xpsql08:8080/ReportServer/TWO/WennSoft Service/Service Call Work Order'
where ReportReference='SV_Workorder_1'

Did this help you?  If so, please leave a comment!

Script to view index fragmentation

--Use a script like this to monitor index fragmentation:
SELECT d.name,
s.OBJECT_ID,
s.index_id,
s.index_type_desc,
s.avg_fragmentation_in_percent,
s.avg_page_space_used_in_percent
FROM sys.databases AS d
INNER JOIN sys.dm_db_index_physical_stats(NULL, NULL, NULL,
NULL, 'SAMPLED')
AS s ON d.database_id = s.database_id
WHERE d.NAME = 'DYNAMICS'
ORDER BY s.avg_fragmentation_in_percent DESC



Did this help you?  If so, please leave a comment!

Script to Move tempdb database

--To move tempdb database:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = [logicalname], FILENAME = 'V:\tempdb\tempdb.mdf' )
ALTER DATABASE [mydb] MODIFY FILE ( NAME = [logicalname], FILENAME = 'V:\tempdb\templog.ldf' )
ALTER DATABASE [mydb] SET ONLINE

Did this help you?  If so, please leave a comment!

SQL Scripts from Synergy - Example of cross join

--A LONG RUNNING QUERY
SELECT 'LETS CREATE A CARTESIAN PRODUCT'
GO  --WITHOUT THE GO, IT RUNS AS A SINGLE BATCH

SELECT *
FROM SV00400
CROSS JOIN SV00500


Did this help you?  If so, please leave a comment!