Over the last few years there have been scenarios that as a developer I’ve come across many times and today I thought I’d share some SQL specific scripts that I find myself needing every once in a while and I always have to go hunting for them. This post will consolidate some of the more commonly useful ones that I think will be valuable to other developers as well. 

  1. Identify/Delete Duplicate Records
    Here’s a script that will enable you to quickly identify and/or delete duplicate records from any SQL table:

    Select

    ;WITH cte AS (SELECT PersonID, ROW_NUMBER() OVER (PARTITION BY PersonID, OrganizationID ORDER BY isPrimary desc) RN FROM   Personstoorganizations)
    SELECT * FROM cte WHERE  RN > 1

    Delete

    ;WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID, OrganizationID ORDER BY isPrimary desc) RN FROM   Personstoorganizations)
    DELETE FROM cte WHERE  RN > 1

    You will notice that this snippet uses a CTE (Common Table Expression) in conjunction with the partition and row number feature available since SQL 2005. When using this snippet it is recommended to always use the Select before the Delete to ensure what is being returned “makes sense”. It’s also recommended to back up the database or run this snippet in a controlled development environment.

    The two import things in this snippet to adjust are the columns on which to partition again as well as the order by clause for said partition. In the example above the query is retrieving records where PersonID and OrganizationID in conjunction exist more than once in the database table and it is being sorted by the isPrimary flag to return the records that are “false”, since if any are true those we would like to keep over any that are duplicate and false.

  2. Reseed Identity Columns For An Entire DatabaseSometimes the seed key for identity columns gets out of whack (especially after using some data comparison tools). In those cases this little script is a life saver:
    EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 
    BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

    Please note that this script makes use of the undocumented stored procedure found in the master database called “sp_MSForEachTable”. Since it’s undocumented you will not want to rely on it for any regular jobs, SSIS packages, etc., but instead use it on an as needed basis.

  3. Finally See That Damn Restore Database Progress
    Have you ever started a database restore only to stare at that damn spinner for what seems like ages? Then being asked by upper management: “Where do we stand? When is this thing going to be back up?”? Well my friends you are not alone, behold:

    SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
    CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
    CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
    CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
    CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
    CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

    Mind Blown

  4. Re-Index An Entire Database
    This is a quick and simple script to re-index a database and also update statistics:

    USE MyDatabase
    GO
    EXEC sp_MSforeachtable@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
    GO 
    EXEC sp_updatestats
    GO
  5. Clear All Data
    Before going live sometimes you are in need to do a database “reset” to get rid of all that test data and I’ve found the following script to be particularly useful in those cases:

    --Disable Constraints & Triggers 
    EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
    EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 
    --Perform delete operation on all table for cleanup 
    EXEC sp_MSforeachtable 'DELETE ?' 
    --Enable Constraints & Triggers again 
    EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
    EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
    --Reset Identity on tables with identity column 
    EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Please remember that I/we do not offer any guarantees for these scripts and they are certainly not all compatible with every version of MS SQL Server, but hopefully somebody will find one of these as useful as I have found them in the past. Oh and don’t forget:

Being too busy to worry about backup, is like being too busy driving a car to put on the seatbelt.

— T.E. Ronneberg

As a web designer who works in Photoshop nearly every day, I’ve come to depend on the program for just about everything – creating website mockups, presentations, and editing photos, just to name a few. Once you’ve learned the complexity and tools wrapped into this powerful program, you can create just about anything, and even make a living with it.

But its main draw as a feature-filled program is also Photoshop’s Achilles heel: The program is slow to change to web trends and new workflows, as feature updates only come every several years or so. And some features that have existed in other Adobe programs – like Illustrator and InDesign – don’t make it into Photoshop.

In the two most recent updates to Photoshop, Adobe seems to be correcting both of those negatives. In versions 14.1 and 14.2 of Photoshop CC, Adobe has finally brought a modern way to export image assets and proper file linking to Photoshop.

Adobe Generator

In the past, if you wanted to get a logo image out of Photoshop, you’d have to use slices – a time consuming process of setting up “boxes” and exporting images to JPEG, GIF, or PNG file formats manually. If your layout had a major change, you’d have start all over and slice your document again. The proliferation of Retina devices – smartphones, tablet, and laptops – only compounded this process, as you might need a high resolution and normal resolution of each image too. Slicing became messy.

Photoshop’s new feature, called Adobe Generator, now makes slices a thing of the past, saving designers tons of time. Now all you need to do is name your layers in Photoshop in accordance to a set syntax, and Photoshop will automagically produce all of your image files for you. If you change a layer, it’s automatically re-exported for you in the background. To turn on Generator, all go to File > Generate > Image Assets.

For example, if you name your layer “200% Logo-Retina.png, Logo.png”, Photoshop will generate two PNG files – one that’s high resolution for Retina devices, and one that’s normal resolution for non-Retina devices. Optionally, you can tell Generator to export in GIF or JPEG formats, along with some options in scaling and quality for each. Set it and forget it!

Linked Smart Objects

The other useful feature just added to Photoshop is being able to link to Smart Objects – an evolution of “placing” files into a Photoshop document. Placing a file into Photoshop has never worked the way you wanted it to: if you have to update that placed file, you have to manually place and adjust in Photoshop again. What we’ve really wanted is the ability to link to files much like Illustrator and InDesign have been for years.

Now, with the latest update, if you link to a Smart Object, and you make a change to it later – say a color change – your Photoshop doc is updated accordingly. This saves tons of time and opens up more team collaboration, too. One designer could be working on a logo, and another could be working on a web layout – if the logo designer makes a color change to his document, the web designer’s document is updated automatically and seamlessly. As a designer I can definitely see how this is fitting in with Adobe’s Creative Cloud vision.

Resources

Adobe has nice video overviews of Generator and linking Smart Objects, so be sure to check those out to learn more.