LitKnd

SELECT
    sc.name + N'.' + so.name as [Schema.Table],
	si.index_id as [Index ID],
	si.type_desc as [Structure],
    si.name as [Index],
    SUM(stat.row_count) AS [Rows],
    SUM(stat.in_row_reserved_page_count) * 8./1024./1024. as [Reserved In-Row GB],
	SUM(stat.lob_reserved_page_count) * 8./1024./1024. as [Reserved LOB GB]
FROM sys.indexes as si
JOIN sys.objects as so on si.object_id = so.object_id

LitKnd

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
Copyright (c) 2018 SQL Workbooks LLC
Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom 

LitKnd

SET NOCOUNT ON;
GO

use master;
GO
IF DB_ID('CloneMe') IS NOT NULL
BEGIN
    ALTER DATABASE CloneMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CloneMe;
END

LitKnd

/* This doesn't cause a stack dump in SQL Server 2016 SP1-- looks like
it broke in 2012 and was fixed sometime before 2016 SP1

*/

Use AdventureWorks2012;
GO

CREATE INDEX ix_poorly_named_filtered_index on
Sales.SalesOrderHeader (OrderDate)

LitKnd

/* Sample code that explores index recommendations in BabbyNames */

USE BabbyNames;
GO

SET NOCOUNT ON;
GO

SELECT
    ref.FirstName,

LitKnd

DECLARE 
    @schemaname sysname = 'Sales',
    @tablename sysname ='Orders',
    @indexid TINYINT = 1;

/* Clustered index is always indexid=1 */

DECLARE @objectid INT;
SELECT @objectid=OBJECT_ID(CONCAT(QUOTENAME(@schemaname),'.', QUOTENAME(@tablename)));

LitKnd

DBCC TRACEOFF (4199, -1);
GO

IF @@TRANCOUNT > 0
    ROLLBACK;
GO
SET NOCOUNT ON;
GO

USE master;

LitKnd

/* 
All public gists https://gist.github.com/litknd
Copyright 2017, Kendra Little
MIT License, http://www.opensource.org/licenses/mit-license.php
*/

USE master
GO

/*******************************************************************

LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com

Setup:
    Download the database to restore from https://github.com/LitKnd/BabbyNames/releases/tag/v1.1
	Either the large or small database works in this case.
	(If you don't have the big one yet, use BabbyNames.bak.zip, it's only 41MB to download.) 
    You must restore to SQL Server 2016 or a higher version.

LitKnd

<#
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
#>


function LoopParameterizedQuery{
	param($Limit, $Connection, $FakeAppQuery, $Parameters=@{})