Category: Database Development

SQL Last Day of Month

How do I get the last day of the month in a SQL query? Well it turns out that the release of SQL Server 2012 marked the release of the EOMONTH() function.

Prior to SQL Server 2012, this is what typical T-SQL to compute last day of the month would look like:

DECLARE @date DATE = '2017-12-15';
DECLARE @firstOfMonth DATE = DATEFROMPARTS(YEAR(@date), MONTH(@date), 1);
SELECT DATEADD(DAY,-1, DATEADD(MONTH, 1, @firstOfMonth));

With SQL Server 2012 or later, the EOMONTH() gives you a much more concise option which looks like:

DECLARE @date DATE = '2017-12-15';
SELECT EOMONTH(@date)
Advertisements

Using a Recursive CTE to Build Paths

Here’s the scenario. You have a hierarchical data set like categories, folders, etc. and you need to output the entire path for each.

Let’s say we take the category example and we have a table that looks like:

CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[ParentCategoryId] [int] NULL,
    -- normal PK script removed to keep this short
) 

To get the full path for each category, the SQL would look like:

WITH CTE_CategoriesWithPaths AS
( 
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId, 
        c.Name AS [Path] 
    FROM Category c 
    WHERE c.ParentCategoryId IS NULL 
    UNION ALL
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId,
        cte.[Path] + '/' + c.Name AS [Path] 
    FROM 
        Category c 
        INNER JOIN CTE_CategoriesWithPaths cte 
            ON c.ParentCategoryId = cte.Id
    WHERE c.ParentCategoryId IS NOT NULL 
)
SELECT * 
from CTE_CategoriesWithPaths

SQL If Exists Then Drop

This is one topic that I continually find myself refering to my notes and bookmarks on and I’ve finally decided to add this piece of very good reference information to my own blog.

Tables

Option 1:

IF OBJECT_ID('enterTableNameHere', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Option 2:

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterTableNameHere]')
         AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Views

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'enterSchemaNameHere' AND TABLE_NAME = 'enterViewNameHere'
)
BEGIN
    DROP VIEW [dbo].[enterViewNameHere]
END
GO

Stored Procedures

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterStoredProcedureNameHere]')
        AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
    DROP PROCEDURE [dbo].[enterStoredProcedureNameHere]
END
GO

User-Defined Functions

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE	ROUTINE_NAME = 'enterFunctionNameHere'
    AND ROUTINE_SCHEMA = 'dbo'
    AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
    DROP FUNCTION [dbo].[enterFunctionNameHere]
END
GO

SQL Server Foreign Keys and Indexes

This is another one of those cases that just beats it into your head to stop making assumptions.

My first delve into RDBMS’s was MySQL. When you create a Foreign Key in MySQL, it will create an Index for you. Unfortunately, I assumed that SQL Server would do the same. Then about a year ago, I was researching ways to speed up some of the queries in a customer’s application when I came across a thread on StackOverflow. What did I find? I was making the wrong assumption and you have to create both the Foreign Key and Index in SQL Server.

Free SQL 2008 R2 Ebook

Microsoft Press has released a free ebook entitled Introducing Microsoft SQL Server 2008 R2.

PART I Database Administration

CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2 Multi-Server Administration 21
CHAPTER 3 Data-Tier Applications 41
CHAPTER 4 High Availability and Virtualization Enhancements 63
CHAPTER 5 Consolidation and Monitoring 85

PART II Business Intelligence Development

CHAPTER 6 Scalable Data Warehousing 109
CHAPTER 7 Master Data Services 125
CHAPTER 8 Complex Event Processing with StreamInsight 145
CHAPTER 9 Reporting Services Enhancements 165
CHAPTER 10 Self-Service Analysis with PowerPivot 189

More information:
http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx