sqldeployhelmet

/* What movies have been nominated for 1, 2 or 3 Oscars? */
SELECT name, releaseDate, oscarNoms
FROM films
/* if any one of the following conditions is true for a row it will be returned */
WHERE oscarNoms = 1 
	OR oscarNoms = 2 
	OR oscarNoms = 3;

/* These can more easily be written with the IN clause */
SELECT name releasedDate

sqldeployhelmet

/* films with exactly 2 oscar nominations */
SELECT name, releaseDate
FROM films
WHERE oscarNoms = 2;

/* films with more than 4 oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms > 4;

sqldeployhelmet

SELECT 'MyData';

/* 	
	Best practices of course would be to include the specific columns 
	in your select list so the SQL query engine can optimize the return
	of the necessary data. Selecting * from the table is a lot like
	eating an entire pizza because you wanted a single slice.
*/
SELECT * 
FROM myTable;

sqldeployhelmet

$servername = 'myserver'

$key = "Software\\Microsoft\\Microsoft SQL Server"

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server)
$regKey = $reg.OpenSubKey($key)

$keys = $regKey.GetSubKeyNames()

$v = 0

sqldeployhelmet

/*
Script to remove empty partitions older than 15 months (except partition 1 because we need at least one empty partition)
*/
DECLARE @mergedate DATETIME2 
    , @sqlcmd VARCHAR(MAX);

CREATE TABLE #mergedates ( m_date DATETIME2 );

INSERT INTO #mergedates ( m_date )
SELECT CAST(sprv.value AS DATETIME2)

sqldeployhelmet

/*
Script to identify the oldest partition w/ data and swap it out
</code><code>
1. identify partition
2. script out target table (1 time?)
3. ALTER TABLE audit.ServiceUserAction SWITCH PARTITION # TO
3. truncate table
*/

DECLARE @part_num INT 

sqldeployhelmet

/* Split upcoming partitions (work up to 3 months ahead of current month) */
DECLARE @nextPart DATE 
    , @curDate DATE =  DATEADD(MONTH, 3, GETDATE()); -- we'll extend our partitions out to this date</code>

SELECT  @nextPart = DATEADD(MONTH, 1, CAST(sprv.value AS DATETIME2))
FROM    sys.partition_functions AS spf
    INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
WHERE   spf.name = N'pf_MonthlyWindow'
ORDER BY sprv.boundary_id DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY;

sqldeployhelmet

ALTER TABLE dbo.superbusytable DROP CONSTRAINT PK_BestKeyEver
GO

ALTER TABLE dbo.superbusytable
    ADD CONSTRAINT PK_BestKeyEver
    PRIMARY KEY CLUSTERED (id, insertdate ) ON ps_MonthlyWindow(insertdate); -- insertdate is an ever increasing date, we'll never be inserting "old" records
GO

sqldeployhelmet

USE [VeryBusyDatabase];
GO

CREATE PARTITION FUNCTION [pf_MonthlyWindow] ( DATETIME2(7)) -- This data type must match your partitioning column data type exactly!
    AS RANGE RIGHT FOR VALUES ( N'1900-01-01T00:00:00.000'
    , N'2016-08-01T00:00:00.000'
    , N'2016-09-01T00:00:00.000'
    , N'2016-10-01T00:00:00.000'
    , N'2016-11-01T00:00:00.000'
    , N'2016-12-01T00:00:00.000'

sqldeployhelmet

EXEC sp_estimate_data_compression_savings ''
    , ''
    , NULL
    , NULL
    , '';