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)
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.
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.
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
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
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.
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
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.
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.
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.