SQL: Last backups for all databases

\ by 
-- Last backups for all databases 

SELECT 
    CAST(DATABASEPROPERTYEX([d].[name], 'Recovery') AS VARCHAR(10)) [RecoveryModel], 
    LEFT([d].[name], 50) [DatabaseName], 
    MAX([s].[backup_start_date]) [StartDate], 
    DATEDIFF(HOUR, MAX([s].[backup_start_date]), GETDATE()) [HoursAgo], 
    CASE [s].[type] 
    WHEN 'D' THEN 
        'Full' 
    WHEN 'I' THEN 
        'Differential' 
    WHEN 'L' THEN 
        'Log' 
    WHEN 'F' THEN 
        'File or Filegroup' 
    END [LastBackupType] 
FROM [master]..[sysdatabases] [d] 
LEFT OUTER JOIN [msdb]..[backupset] [s] 
    ON [d].[name] = [s].[database_name] 
JOIN ( 
    SELECT 
        [d].[name], 
        MAX([a].[backup_start_date]) [StartDate] 
    FROM [master]..[sysdatabases] [d] 
    LEFT OUTER JOIN [msdb]..[backupset] [a] 
        ON [d].[name] = [a].[database_name] 
    WHERE [d].[name] NOT IN ( 
              'tempdb', 'Northwind', 'pubs', 'model' 
          ) 
          AND [a].[type] = 'D' 
    GROUP BY [d].[name] 
) [x] 
    ON [x].[name] = [d].[name] 
WHERE [d].[name] NOT IN ( 
          'tempdb', 'Northwind', 'pubs', 'model' 
      ) 
GROUP BY [d].[name], 
         [s].[database_name], 
         DATABASEPROPERTYEX([d].[name], 'Recovery'), 
         [s].[type], 
         [x].[StartDate] 
HAVING MAX([s].[backup_start_date]) >= [StartDate] 
ORDER BY [d].[name], 
         MAX([s].[backup_start_date])
Posted in: SQL

REF: Compression and its Effects on Performance

\ by

I want to remember this study by Erin Stellato.

The summary:

If you’re in an OLTP system, you don’t want to return that many rows, so the tests here should give you an idea of how compression may affect performance. If you’re in a data warehouse, then you will probably see higher duration along with the higher CPU when returning large data sets. While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched.

Posted in: Performance, SQL

SQL: Count rows for each Range of rows

\ by 
/*

For a table with INT column count rows for each Range of rows. 

Set to 10M rows...
*/
SELECT
    MIN([KeyTable].[Key]) AS [MinKey],
    MAX([KeyTable].[Key]) AS [MaxKey],
    [KeyTable].[Range] * 10000000 AS [Range10M],
    COUNT(*) AS [RangeRows]
FROM (
    SELECT
        [Key],
        (([Key] - 1) / 10000000) + 1 AS [Range]
    FROM [dbo].[Detail]
) AS [KeyTable]
GROUP BY [KeyTable].[Range]
ORDER BY [KeyTable].[Range]

-- Test one.
SELECT COUNT(*)
FROM [dbo].[Detail]
WHERE [Key]
BETWEEN 150000001 AND 160000000
GO
Posted in: Coding, SQL

SQL What is the second Monday of this month?

\ by

What is the second Monday of this month?

;WITH [cteDate]
AS
 (
 -- @first + 7 * (@nth - 1) + (7 + @dow - DATEPART(WEEKDAY, @first + 7 * (@nth - 1))) % 7 -- Get @nth @dow 2 = Monday for us. Formula from Steve Kass.
 SELECT
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1)
      + (7 + 2 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1))) % 7 AS [SecondMonday])
SELECT
    [cteDate].[SecondMonday],
    DATENAME(WEEKDAY, [cteDate].[SecondMonday]) AS SearchWeekDay,
	DATEDIFF(DAY,GETDATE(),[cteDate].[SecondMonday]) AS DaysFromNow
FROM [cteDate]
Posted in: Coding, SQL, SQL Server

SQL: Fast Table RowCount

\ by

We aren’t looking for accuracy, so this works fine for us. The NOLOCKs helped e.g. when indexes were in the process of creating.

Fast way to count large tables.

SELECT
    SCHEMA_NAME([Tables].[schema_id]) AS [SchemaName],
    [Tables].[name] AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
FROM [sys].[tables] AS [Tables] WITH (NOLOCK)
JOIN [sys].[partitions] AS [Partitions] WITH (NOLOCK)
    ON [Tables].[object_id] = [Partitions].[object_id]
       AND [Partitions].[index_id] IN (
               0, 1
           )
WHERE [Tables].name = N'TRANSACTIONS'
--	and [Partitions].[rows] > 0
GROUP BY SCHEMA_NAME([Tables].[schema_id]),
         [Tables].[name];
Posted in: Coding, Performance, SQL

Dates Explained

\ by

Here are some of the ways dates can be output.

declare @date datetime
set @date = getdate()
select 
	@date	as SampleDate,
	datepart(year,@date)	as 'year',
	datepart(month,@date) 	as 'month',
	datepart(day,@date) 	as 'day',
	datepart(week,@date) 	as 'week',
	datepart(quarter,@date) as 'quarter',
	datepart(dy,@date) 	as 'day of year',
	datepart(weekday,@date) as 'weekday',
	datepart(hour,@date) 	as 'hour',
	datepart(minute,@date) 	as 'minute' ,

	datename(month,@date) as 'month name',
	datename(weekday,@date) as 'weekday name',

	'fiscal year' =   
	case            
		when datepart(month,@date) >= 7
		then datepart(year,@date) + 1
		else datepart(year,@date)
	end,
	CONVERT(varchar(12), getdate() ,112) 	as 'today date as integer',
	CONVERT(varchar(12),DATEADD(d, -7 ,getdate()),112) 	as 'lastweek date as integer',
	CONVERT(varchar(12), getdate() ,108) 			as 'time',
	REPLACE(CONVERT(varchar(12), getdate() ,108),':','') 	as 'time as integer',
	dateadd(d,-1, cast(month(dateadd(mm,1,getdate())) as varchar) + 
		'/01/' + cast(year(getdate()) as varchar)) as [Last day of month],
	DATEADD(D,1, 
		cast(month( getdate() ) as varchar) + '-' +
		cast(day( getdate()) as varchar) + '-' +
		cast(year(getdate() ) as varchar) ) as [Midnight Tonight]

declare @tim varchar(8)
set @tim = REPLACE(CONVERT(varchar(12), getdate() ,108),':','')
select @tim,
case when len(@tim) >= 5 then
	substring(@tim,len(@tim)-5,2 ) + ':' +
	substring(@tim,len(@tim)-3,2 ) + ':' +
	substring(@tim,len(@tim)-1,2 )
else
	@tim
end
Posted in: Coding, SQL

Where are tables located in database files?

\ by

This uses indexes to locate the files.


SELECT  [o].[crdate],
        DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
        [o].[name] AS [Tablename],
        [i].[indid],
        [i].[name] AS [IndexName],
        [i].[rowcnt],
        [i].[groupid],
        [f].[name] AS [FileGroupName],
        [d].[file_id],
        [d].[physical_name],
        [s].[name] AS [DataSpace]
FROM    [sys].[sysobjects] [o]
JOIN    [sys].[objects] [so]
ON      [so].[object_id] = [o].[id]
JOIN    [sys].[sysindexes] [i]
ON      [i].[id] = [o].[id]
JOIN    [sys].[filegroups] [f]
ON      [f].[data_space_id] = [i].[groupid]
JOIN    [sys].[database_files] [d]
ON      [f].[data_space_id] = [d].[data_space_id]
JOIN    [sys].[data_spaces] [s]
ON      [f].[data_space_id] = [s].[data_space_id]
WHERE   [is_ms_shipped] = 0
        AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
        [IndexName],
        [d].[file_id]
Posted in: Coding, Configuration, SQL

SQL Metadata load all databases all column data into a table

\ by

We had the idea to put all column data into a table for code review comparisons etc. Chris Trump came up with this. Thanks, Chris!

EXECUTE master.sys.sp_MSforeachdb @command1 = N'
USE [?];
 
INSERT INTO dbo.Database_DataDictionary (DATABASE_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION)
SELECT  
        "?" as DATABASE_NAME,
        t.TABLE_NAME,
        c.COLUMN_NAME,
        c.ORDINAL_POSITION,
        UPPER(c.DATA_TYPE) as DATA_TYPE,
        ISNULL(c.CHARACTER_MAXIMUM_LENGTH,'''') AS CHARACTER_MAXIMUM_LENGTH,
        ISNULL(c.NUMERIC_PRECISION,'''') AS NUMERIC_PRECISION,
        ISNULL(c.NUMERIC_SCALE,'''') AS NUMERIC_SCALE,
        ISNULL(c.DATETIME_PRECISION,'''') AS DATETIME_PRECISION
 
FROM         
        INFORMATION_SCHEMA.COLUMNS AS c 
        INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON 
                t.TABLE_NAME = c.TABLE_NAME
WHERE         
        t.TABLE_TYPE = ''Base Table'''
 

SELECT * FROM dbo.Database_DataDictionary
Posted in: Coding, SQL

TSQL: Find characters in one string missing from another

\ by

I have a set of characters, all of which must match the characters in another set.

e.g. All characters in “RW” must exist in “RWCE”.

There’s probably an easier way, but here’s one solution.

  1. Create a CTE table of the string to test, converting a set of letters to rows.
  2. Return any rows of just the missing or failed characters
  3. If a row EXISTS we want to report the error.

DECLARE @vcNewPermissions VARCHAR(128)
SELECT @vcNewPermissions = 'RW'
--
IF OBJECT_ID('tempdb.dbo.[#IllegalPermissions]') IS NOT NULL
    DROP TABLE [#IllegalPermissions]
--
;WITH    [mycte]([x])
          AS (
              SELECT    SUBSTRING([a].[FindString], [v].[number] + 1, 1)
              FROM      (
                         SELECT @vcNewPermissions AS [FindString]
                        ) [a]
              JOIN      [master].[dbo].[spt_values] [v]
              ON        [v].[number] < LEN([a].[FindString])
              WHERE     [v].[type] = 'P'
             )
    SELECT  [x]
    INTO    [#IllegalPermissions]
    FROM    [mycte]
    WHERE   CHARINDEX([x], 'RWCESVXT') = 0

IF EXISTS ( SELECT  *
            FROM    [#IllegalPermissions] )
    RAISERROR('ERROR: permission not found',16,1)
Posted in: Coding, SQL