Saturday, March 11, 2017

COUNT without GROUP BY

I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.

The syntax is COUNT(*) OVER () AS [RecordCount]

I use it below to return the count of addresses in AdventureWorks.

SELECT DISTINCT 
      a.[City]
   ,sp.Name As StateProvince
   ,cr.Name AS CountryRegion
   ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
   ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
   ,COUNT(*) OVER () AS TotalAddresses 
  FROM [AdventureWorks2014].[Person].[Address] a
  INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
  ON a.[StateProvinceID] = sp.[StateProvinceID]
  INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE cr.Name = 'Germany'
  ORDER BY 1,2,3

Tuesday, June 28, 2016

CTE to create a sequence of numbers

DECLARE @start INT, @end INT, @increment INT
SELECT @start=0, @end=100000000, @increment = 50000
 
;WITH NumberList ( Number ) AS
(
    SELECT @start as Number
        UNION ALL
    SELECT Number + @increment
        FROM NumberList
        WHERE Number < @end
)
 
SELECT Number FROM NumberList OPTION (MAXRECURSION 0)

Tuesday, January 12, 2016

SQL Page Compression - Rollout script.

An update to my original script to apply PAGE Compression to all objects
Obviously, you need to weigh up the benefits of doing so first.

This version includes the rowcounts but orders the statements so you compress the table before it's associated indexes.
SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Table' AS ObjectType,1 AS ExecutionOrder,'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand]
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
UNION ALL
SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Index' AS ObjectType,2 AS ExecutionOrder,'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand] 
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
order by SCHEMA_NAME(schema_id),Name,ExecutionOrder