Dropping Multiple SQL Server Objects in Single Line

As humans we try to find a way to work faster and efficient. As data professionals, typing less probably is another thing we want. My SQL Server tip today is about dropping multiple objects (tables, views, stored procedures, and even databases) in single drop statement. This is powerful but can be dangerous in production so please use caution. Here is a script that creates couple of tables and then drops both tables in single drop statement.

Sincerely,

Bulent

-- CREATE TABLES

USE tempdb

GO

CREATE TABLE dbo.TableT1 (t1c1 TINYINT)

CREATE TABLE dbo.TableT2 (t2c1 TINYINT)

GO

INSERT INTO dbo.TableT1 VALUES(1)

INSERT INTO dbo.TableT2 VALUES(2)

GO

-- CHECK THE TABLES CREATED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

-- DROP BOTH TABLES IN SINGLE DROP STATEMENT

DROP TABLE dbo.TableT1, dbo.TableT2

GO

-- CHECK THE TABLES DROPPED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

More on SQL Server 2008 Variable Enhancements

Let's continue where we left last week. I want to point out another enhancement around assigning values to parameters in SQL 2008 and above. It's called compound assignment operator, starting with SQL 2008 we can declare a parameter and assign value and also we can do mathematical operations.

-- SQL Server 2008 and 2008 R2

DECLARE @DueAmount MONEY = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount *= 0.9

END

SELECT @DueAmount

GO

-- SQL Server 2005 and before

DECLARE @DueAmount MONEY

SELECT @DueAmount = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount = @DueAmount * 0.9

END

SELECT @DueAmount

If you pay attention how the value is being set inside the if logic you will understand how compound assignments works. Here is the link to msdn page for more detailed info around setting the values to variables. It does help with less coding in your T-SQL but if there is sql server 2005 or earlier versions exist in your environment be careful to remember which environments that statement will be used.

HTH,

Bulent

SQL Server 2008 Variable Declaration

Another day a developer approached me stating that the code works in local development server but it does not work when deployed to QA. I know that QA server is running SQL Server 2005. I heard that lately the development team was getting new workstations with new set up. So I was sure that they were running something other than SQL server 2005. I asked the developer if the sql server running on the workstation was SQL 2008. The answer was yes and I then asked how variables were declared and assigned the valued. It was just the first thing that came to my mind. And I was right.

So, starting with SQL Server 2008 Microsoft made improvements on declaring variables and assigning values to them. Here is an example for SQL Server 2008 and newer versions.

DECLARE @Startdate DATETIME = GETDATE()

DECLARE @AccountID INT = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

However if you run the above statement in SQL 2005 or earlier you will get an error message. The only way above statement will work on SQL 2005 and earlier versions is;

DECLARE @Startdate DATETIME

DECLARE @AccountID INT

SELECT @Startdate = GETDATE(),

@AccountID = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

As you see it is less of t-sql code for SQL Server 2008 and newer versions. There is some more enhancements but I will cover that in my next blog.

HTH,

Bulent

SQL Server Trimming miliseconds (Zero out miliseconds)

Hi,
Where I work we needed to zero out the milliseconds from a datetime field in one of the database. I found out that somebody created function below which returns the passed datetime with the milliseconds zero out. Instead of 2010-04-15 14:50:49.953 this time we needed to return 2010-04-15 14:50:49.000. When I looked at the function I thought there is just to much DATEPART, CONVERT and concatenation being used. And I asked myself If I could make it much shorter, cleaner. So I did come up with a result and it works. Here is the original function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMilliseconds] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CONVERT(datetime,
CONVERT(varchar(4), DATEPART(yyyy, @D)) + '-' +
CONVERT(varchar(2), DATEPART(mm, @D)) + '-' +
CONVERT(varchar(2), DATEPART(dd, @D)) + ' ' +
CONVERT(varchar(2), DATEPART(Hh, @D)) + ':' +
CONVERT(varchar(2), DATEPART(mi, @D)) + ':' +
CONVERT(varchar(2), DATEPART(ss, @D)))

return @Date
END
GO

Here is my updated version:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMillisecondsNew] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CAST(CONVERT(VARCHAR(19),@D,20) AS DATETIME)

return @Date
END
GO

As you see the second function is much cleaner and simpler. I only used convert and cast functions once to do the work. Less typing, less t-sql, work is done.