ProgramGood.Net - SQLServer

SQL Index Defragging

Dave Mateer — Wed, 29 May 2013 13:44:44 GMT

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

then

EXECUTE dbo.dba_indexDefrag_sp

 @executeSQL = 1

, @printCommands = 1

, @debugMode = 1

, @printFragmentation = 1

, @forceRescan = 1

, @maxDopRestriction = 1

, @minPageCount = 8

, @maxPageCount = NULL

, @minFragmentation = 1

, @rebuildThreshold = 30

, @defragDelay = '00:00:05'

, @defragOrderColumn = 'page_count'

, @defragSortOrder = 'DESC'

, @excludeMaxPartition = 1

, @timeLimit = NULL

, @database = 'northwind';

Copy SQL Server Diagrams to another DB

Dave Mateer — Tue, 14 May 2013 11:37:52 GMT

SQL Server diagrams are simple and useful, but when backup and restore they don’t get coppied.

http://www.conceptdevelopment.net/Database/ScriptDiagram2008/

Add sproc to the DB you want to export diagram
Run sproc
Copy and paste generated SQL, and run it on new DB you want to import the diagram to

MSSQL Indexes

Dave Mateer — Wed, 08 May 2013 16:42:26 GMT

Part of tuning I want to update the indexes of a production DB to one with better indexes that has been worked on in dev.

Drop all Non Clustered indexes on the database we want to update

select 'DROP
index ' + i.name + '
on ' + so.name

from sys.indexes i

inner join sys.objects so ON i.object_id = so.object_id 

where i.type_desc = 'nonclustered'

AND so.type = 'u'

However I can’t do this as:

An explicit DROP INDEX is not allowed on index 'xxx'. It is being used for UNIQUE KEY constraint enforcement.

looks like I’ll need to drop the uniqueness eg
alter table tbl_Payroll_SubType drop constraint IX_tbl_Payroll_SubType

Can see UC’s here: (against the actual db – not master)

SELECT *
FROM sys.objects
WHERE type = 'UQ' ;

What Are Clustered Indexes?

http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

..Telling the database to store close values actually close to one another on the disk.

If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.

P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story.

Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.

Non Clustered Index

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Unique Non Clustered Index and Key

When want to enforce uniqueness eg:

CostCentreID

CostCentreCode ** on this column must be unique

we put on a nonclustered index which is unique called: UC_CostCentres_CostCentreCode

this appears in Keys too.

SQL Performance–Blitz

Dave Mateer — Tue, 07 May 2013 13:29:53 GMT

http://www.brentozar.com/blitz/

Good at analysing indexes.

SQL Server Version

Dave Mateer — Mon, 14 Jan 2013 13:49:58 GMT

select @@version

SSDT–SqlServer Data Tools

Dave Mateer — Fri, 05 Oct 2012 12:34:28 GMT

Installing this to get DB Functionality in VS2012

SQL–Delete all Tables, View, SP’s etc..

Dave Mateer — Mon, 24 Sep 2012 09:39:28 GMT

Handy script to delete if you don’t want to drop the db.

--EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" --DELETE all tables!

--this script cleans all views, SPS, functions PKs, FKs and tables.

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

MSSQL Row Count of Tables

Dave Mateer — Thu, 18 Aug 2011 07:51:26 GMT

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

From http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

This is approximate

SQL Selecting a Null Guid

Dave Mateer — Wed, 18 May 2011 08:36:30 GMT

CREATE FUNCTION Guid_Empty()

RETURNS UniqueIdentifier

AS


BEGIN


RETURN cast(cast(0 as binary) as uniqueidentifier)

END


GO








SELECT MerchantUID, EmailToSendReport1, EmailToSendReport2,
EmailToSendReport3, EmailToSendReport4 


FROM aspnet_Profile2 


WHERE UserType = 1 and 


-- where MerchantUID is not a null Guid.. uses function
Guid_Empty


ISNULL(MerchantUID, dbo.Guid_EMPTY()) != dbo.Guid_EMPTY()

Insert Many Dates into a Table

Dave Mateer — Tue, 18 Jan 2011 21:54:25 GMT

From http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26469215.html

DECLARE @dt datetime
SET @dt = '2007-01-01'
WHILE @dt <= '2017-01-01' BEGIN
INSERT INTO SomeTable (DateColumn) VALUES (@dt)
SET @dt = DATEADD(day, 1, @dt)
END

aspnetdb on Azure

Dave Mateer — Mon, 17 Jan 2011 00:34:27 GMT

http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=KB2006191&DownloadId=8227

Here are the scripts put together in 1 file:

file

Remember to point your web app to the database to populate it ie in Visual Studio, click on the icon at the top in solution explorer to launch the web app.

SQLCMD – Command Line SQL Server

Dave Mateer — Thu, 02 Sep 2010 02:28:12 GMT

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD--Part-I.htm

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

sqlcmd –S.\SQLEXPRESS –E -- E is windows auth

select @@version

seelct @@servername

sqlcmd –Smssql1.openhost.net.nz –Uuser –Psecret -- carefuly copying this from here.. I had to retype

sqlcmd –Smssql1.openhost.net.nz –Uuser –Psecret –i c:\dbtestbackup.sql –oc:\sqloutput.txt

Had a huge 800MB .SQL file. Notepad++ wouldn’t open after 10mins.

even gvim tried, but opened as junk text

Try SQL Server Import / Export now. This worked really well!

Login failed for NT AUTHORITY/NETWORK SERVICE

Dave Mateer — Mon, 09 Aug 2010 21:27:46 GMT

http://thedatafarm.com/blog/tools/asp-net-iis6-network-service-and-sql-server/

On the master database:

exec sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

Then go to Management Studio, and add in this user.

SQL Server 2008 Management Studio R2 and VMWare Player 3

Dave Mateer — Thu, 27 May 2010 06:28:07 GMT

using the Web Plaform Installer. installation of the Studio didn’t work

tried ccleaner which didn’t work.

http://goneale.com/2009/05/24/cant-install-microsoft-sql-server-2008-management-studio-express/

Trick is to get the DB Server and tools together: **No this is just SQL Server 2008, not R2**

https://www.microsoft.com/downloads/details.aspx?familyid=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en

But as I’d compressed my C:\ drive on WMWare Player it came up with an unusual error.

This is good:

Expand Boot Drive on VMWare Player3

Need VMWare Player 3.

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004047

Can simply edit the drive information in the player GUI, then goto Windows7 click on C:\ drive in admin tools and management, and then do expand volume.