Reduce index size in SQL

----------------Analyze-------

create table #t

(

  name nvarchar(128),

  rows varchar(50),

  reserved varchar(50),

  data varchar(50),

  index_size varchar(50),

  unused varchar(50)

)

declare @id nvarchar(128)

declare c cursor for

select name from sysobjects where xtype='U'

open c

fetch c into @id

while @@fetch_status = 0 begin

  insert into #t

  exec sp_spaceused @id

  fetch c into @id

end

close c

deallocate c

select * from #t

order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

--------------------------------------------------------------------------------------------------------------------------

---- Finding Page count for Each table separately -----------------------------------------------------------

select

    index_id, partition_number, alloc_unit_type_desc

    ,index_level, page_count, avg_page_space_used_in_percent

from

    sys.dm_db_index_physical_stats

    (

        db_id() /*Database */

        ,object_id(N'dbo.TableName') /* Table (Object_ID) */

        ,1 /* Index ID */

        ,null /* Partition ID – NULL – all partitions */

        ,'detailed' /* Mode */

    )

--------------------------------------------------------------------------------------------------------------------------

--------------------------ReOrganize the same table-------------

ALTER INDEX ALL ON dbo.TableName REORGANIZE

--------------------------------------------------------------------------------------------------------------------------

Ref:- https://carra-lucia-ltd.co.uk/2017/05/15/how-to-reduce-index-size-and-free-up-space-in-a-sql-database/

free hit counter

SQL Delete duplicatie records in a table which as a primary key

delete e1.* FROM TempTable e1 INNER JOIN TempTable e2 ON e1.col1=e2.col1 AND e1.col2=e2.col2 AND e1.ID>e2.ID

DB Backup and Restore with Differential backups

--Taking Backup

--Step1:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

GO

--Step2:

--Create some test table and insert some Rows

--Step3:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH DIFFERENTIAL

GO

--Step4:

--Insert Some more rows

--Step5:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH DIFFERENTIAL

GO

--Step4 and Step5 Can be repeated as many time

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB First Time

--Step1:

--Create TestDB1 using UI . Pointing MDF and LDF as shown below, And without 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB1

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.ldf';

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Second Time

--Step1:

--Create TestDB2 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB2

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.ldf';

GO

--Step3:

RESTORE DATABASE TestDB2 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH RECOVERY

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Third Time

--Step1:

--Create TestDB3 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB3

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.ldf';

GO

--Step3:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH NORECOVERY

GO

--Step4:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH RECOVERY

GO

free hit counter

Execute .NET Code under SQL Server

----------Create one database anmed TestingCLR-----------------------

--ALTER DATABASE TestingCLR SET TRUSTWORTHY ON

--GO

-----Start---Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

------using System;

------using System.Collections.Generic;

------using System.Text;

------using System.IO;

------using System.Data.SqlTypes;

------using System.Data.SqlClient;

------using Microsoft.SqlServer.Server;

------namespace ManagedCodeAndSQLServer

------{

------    public class BaseFunctionClass

------    {

------        #region "Default Constructor"

------        public BaseFunctionClass()

------        {

------        }

------        #endregion

------        #region "Welcome Function"

------        ///

------        /// This function will be called from the SQL Stored Procedure.

------        ///

------        /// Name

------        /// Welcome Message

------        [SqlProcedure]

------        public static void GetMessage(SqlString strName, out SqlString

------        strMessge)

------        {

------            strMessge = "Welcome," + strName + ", " + "your code is executed under CLR !";

------        }

------        #endregion

------    }

------}

-----End------Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

--sp_configure 'clr enable', 1

--GO

--RECONFIGURE

--GO

--CREATE ASSEMBLY ManagedCodeAndSQLServer

--AUTHORIZATION dbo

--FROM 'C:\Dev\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE ASSEMBLY Messaging

--AUTHORIZATION dbo

--FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE PROCEDURE usp_UseHelloDotNetAssembly

--@name nvarchar(200),

--@msg nvarchar(MAX)OUTPUT

--AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage

--GO

--DECLARE @msg varchar(MAX)

--EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output

--PRINT @msg

-----Ref : http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server

SQL Column level Encryption / Decryption

----------- Prerequisites to be done --------------------Start----------

--SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';

--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';

--CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data';

--CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1;

----------- Prerequisites to be done --------------------End----------

OPEN SYMMETRIC KEY SymmetricKey1

DECRYPTION BY CERTIFICATE Certificate1;

Declare @Card_number as varchar(25)

set @Card_number = '1234'

select @Card_number OrginalVale, EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number) Encryptedvalue, CONVERT(varchar, DecryptByKey(EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number))) Decryptedvalue

CLOSE SYMMETRIC KEY SymmetricKey1;

Make Chart (Spatial results) from SQL Query

--To Draw a line string

----------------------------

SELECT geometry::STGeomFromText(

'LINESTRING(0 0,1 1)', 0 );

--To Draw Polygons

-----------------------------

SELECT geometry::STGeomFromText(

'POLYGON((0 0,0 1,1 1,1 0,0 0))',

0 );

SELECT geometry::STGeomFromText(

'MULTIPOLYGON(

((0 0,0 1,1 1,1 0,0 0)),

((2 0,2 1,3 1,3 0,2 0)))', 0 );

--Fetch Data from Table and Form chart

--------------------------------------------------

--Create Table

CREATE TABLE [dbo].[Sales](

[FY] [int] NOT NULL,

[Sales] [int] NOT NULL

) ON [PRIMARY]

--Form chart from Results of table

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',' + CAST( FY AS CHAR(4) ) + ' ' + CAST( Sales AS VARCHAR(30) )

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

Select @WKT

SELECT geometry::STGeomFromText( 'LINESTRING(' + @WKT + ')', 0 );

--Form Chart with some manipulations in table Result

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',((' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0))'

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

SELECT geometry::STGeomFromText( 'MULTIPOLYGON(' + @WKT + ')', 0 );

Ref : http://sqlmag.com/t-sql/generating-charts-and-drawings-sql-server-management-studio

TFS Add Work Item Column

1) Get List of WorkItem:

     witadmin listwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName

2) Export Specific WorkItem Type from the List:

     witadmin exportwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /n:"Product Backlog Item"

3) Edit the xml in VS 2015 it will open Eork Item Editor

4) Add Custom Columns ( StartDate -- Custom.StartDate, End Date -- Custom.EndDate)

5) Set the Layout where the colum should display

6) Save the WorkItem Template

7) Validate the WorkItem template

     witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /v

8) Import the WorkItem template (by removing /v in previous command)

    witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName /f:C:\TFS\WorkItem\Product_Backlog_Item.xml