How To Delete The Last X Records From A Table By Group



TODO:

Have you ever wanted to keep the last X records for a Customer, Employee, Order, etc?

 

SOLUTION:

DELETE c FROM (
  SELECT *, rn=row_number() OVER (PARTITION BY CustomerId ORDER BY Id desc)
  FROM CustomerHistory
) c
WHERE rn > 3

 

NOTES:

The SQL above will keep the last 3 records in the table CustomerHistory.  The Grouping is by CustomerId, and Id is an Identity column, which ensures we keep the last 3 records (order by desc).  If you want to test it, just execute the statement as a select (Change DELETE c to SELECT CustomerId, Id, rn

How To Change The Encryption Password On A Database Master Key



TODO:

Have you ever wanted to change the password associated with your Database Master Key?

 

SOLUTION:

use my database
GO
--step 1, open the key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my password'
GO

--step 2, regenerate it with new password
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD 'new password'
GO

 

NOTES:

There are no notes on this topic.

How To Restore A SQL Server Database Master Key On A New Server



TODO:

Have you ever wanted to backup your database master key and move it to a new server?

 

SOLUTION:

--Step 1 -- Backup file
OPEN MASTER KEY DECRYPTION BY PASSWORD 'my password'
     BACKUP MASTER KEY TO FILE = 'c:\myfile' ENCRYPTION BY PASSWORD ='mypassword'

--Step 2 -- Restore file
USE myDatabase
GO
RESTORE MASTER KEY FROM FILE 'my file'

     DECRYPTION BY PASSWORD = 'mypassword'
     ENCRYPTION BY PASSWORD = 'mypassword'
     FORCE;
GO
 

NOTES:

You may not need the 'FORCE' option, read up on what it does and decide accordingly.

How To List All Columns In All User Tables In A Sql Server Database



TODO:

Have you ever wanted to list all columns in all User tables that exist in a SQL Server Database?

 

SOLUTION:

SELECT so.name "table", sc.name "column", sm.text "Default" 
FROM dbo.sysobjects so INNER JOIN dbo.syscolumns sc ON so.id = sc.id 
LEFT JOIN dbo.syscomments sm ON sc.cdefault = sm.id  
WHERE so.xtype = 'U'  
ORDER BY so.[name], sc.colid

 

NOTES:

There are no notes on this topic

How To Call Stored Procedure In C# That Has Output Parameters



TODO:

Have you ever wanted to call a stored procedure in C# that has output parameters?

 

SOLUTIONS:

System.Data.Objects.ObjectParameter param1 = new ObjectParameter("paraminsp1", typeof(bool));
System.Data.Objects.ObjectParameter param2 = new ObjectParameter("paraminsp2", 0.000);           //default so EF works

using (MyEntities MyDatabase = new MyEntities())
{
     MyDatabase.sp_name(x, 2, param1, param2);
}

 

NOTES:

There are no notes on this topic

How To Pass A DataTable To A Stored Procedure Using C# and SQL Server



TODO:

Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?

 

SOLUTION:

Step 1:  Create the User-Definied Table Type in SQL Server

CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
	[Id] [int] NULL,
	[FieldOne] [int] NULL,
	[FieldTwo] [int] NULL
)

 

Step 2:  Create the DataTable and call the Stored Procedure

using (DataTable myDataTable = new DataTable())
{
     myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
                
     DataRow dataRow = null;
                
     for (int i = 0; i < 100; i++)
     {
          dataRow = myDataTable.NewRow();  
          dataRow[“FieldOne] = 1;
          dataRow[“FieldTwo”] = 2;
          dataRow[“FieldThree] = 3;
          myDataTable.Rows.Add(dataRow);           
     }

     using (SqlConnection connection = new SqlConnection(“connectionstring”))
     {
          
          using (SqlCommand command = connection.CreateCommand())
          {
               connection.Open();
               command.CommandType = CommandType.StoredProcedure;
               command.CommandTimeout = 90;
               command.CommandText = “MyStoredProcedureName;
               command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
               command.ExecuteNonQuery();
          }
     }
}


Step 3:  Create the Table

CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)

 

Step 4:  Create the Stored Procedure

CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY

AS
BEGIN 

    INSERT INTO dbo.MyTable
	([FieldOne], [FieldTwo], [FieldThree])
	
	SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType

END

 

 

NOTES:

Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.

How To Incrementally Shrink A SQL Server Database Using T-SQL



TODO:

Have you ever wanted to shrink a large SQL Server database in small increments?

 

SOLUTION:

declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'MyDB'

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 100

-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin

set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

exec ( @sql )

if @@ERROR <> 0
begin
print @@ERROR
break
end

print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a

 

NOTES:

There are no notes on this topic

How To Fix The Issue When SQL Jobs Are Running But Not Showing History Records



TODO:

Have you experienced the issue that jobs are running, but you do not see any job history?  This can occur if you have a job that runs at say 3AM, and you have a series of other jobs that run every minute or so during the day.  Those jobs will fill up the Job History, and the entry for the job in question is purged from the history, due to the Maximum History setting.

 

SOLUTION:

1.  Right click on SQL Agent, choose Properties.

2.  Click to History

3.  Increase the value for "Maximum Job History Log Size (in rows)

 

NOTES:

There are no notes on this topic.

How To Find The Tables And Columns Referenced By A Foreign Key Using T-SQL



TODO:

Have you ever wanted to find the table and column names referenced in a Foreign Key Constraint using T-SQL?

 

SOLUTION:

SELECT 
   OBJECT_NAME(fk.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS fk
INNER JOIN 
   sys.foreign_key_columns AS fkc 
      ON fk.OBJECT_ID = fkc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fkc.referenced_object_id
WHERE 
   OBJECT_NAME (fk.referenced_object_id) = 'YourTableGoesHere'

 

NOTES:

There are no notes on this topic.

How To Find Tables With Large Amounts Of Data In SQL Server



TODO:

You want to find tables that contain large amounts of data, which can be due to large row counts.  The solution will get table stats, and sort by row count descending.

 

SOLUTION:

SELECT 
    t.NAME AS 'Table',
    i.name as 'Index',
    sum(p.rows) as 'Rows',
    sum(a.total_pages) as 'Total Pages', 
    sum(a.used_pages) as 'Used Pages', 
    sum(a.data_pages) as 'Data Pages',
    (sum(a.total_pages) * 8) / 1024 as 'Total Space (MB)', 
    (sum(a.used_pages) * 8) / 1024 as 'Used Space (MB)', 
    (sum(a.data_pages) * 8) / 1024 as 'Data Space (MB)'
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    Rows DESC

 

NOTES:

There are no notes on this topic.