TODO:
You have table A, and Table B. Table A has a foreign key to table B, and table B has a foreign key to table A. You need to delete data from table A, but cannot because of the foreign key on Table B.
SOLUTION:
ALTER TABLE employee NOCHECK CONSTRAINT ALL
delete employee where id=100
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT ALL
NOTES:
This will allow you to delete data when there arecircular foreign keys.
TODO:
Have you ever wanted to print output messages in a stored procedure?
SOLUTION:
PRINT 'some string'
PRINT 'some string' + @SomeVariable
NOTES:
Note, if you are concatenating a numeric variable you will need to cast it to Varchar
TODO:
Have you ever wanted to add a column to an existing SQL Server Table using T-SQL?
SOLUTION:
ALTER TABLE Customer ADD FavoriteColor VARCHAR(20)
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to insert the contents of a DataTable efficiently using SqlClient.BulkCopy?
SOLUTION:
public static void FillMonteCarloSimulationResults(DataTable InDataTable)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy("myconnectionstring")
{
//***NOW Do column mappings. This maps columns from DataTable (left) to DB Table (right)
bulkCopy.DestinationTableName = "Results";
bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.WriteToServer(InDataTable);
}
}
NOTES:
This example will take the DataTable, map the columns, then preform BulkCopy
TODO:
Recently I had a corrupt log file, and my database was continuously 'Suspect'. Since this was a test database, I did not care about my corrupt .ldf file. Therefore, I simply wanted to attach my .mdf file and let it create a new .ldf file. The SQL below will do just that.
SOLUTION:
CREATE DATABASE myDatabase ON
( FILENAME = N'G:\DATA\myDatabase.mdf')
FOR ATTACH
GO
NOTES:
This method works when there is only one log file and it is missing.
IF there is more than 1 log file, use 'FOR ATTACH_REBUILD_LOG' rather than 'FOR ATTACH'
TODO:
Have you ever wanted to get badly fragmented indexes, and rebuild them using T-SQL?
SOLUTION:
CREATE procedure [dbo].[sp_GetIndexFragmentation]
AS
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
--UNCOMMENT LINE BELOW TO ACTUALLY DEFRAG
--EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to shrink a database or log using DBCC Shrinkfile?
SOLUTION:
DBCC SHRINKFILE(DataOrLogFile, 1)
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to filter a tablix using an OR clause. The interface is a little confusing, but the steps below will accomplish this.
SOLUTION:
1. Open Tablix Properties
2. Add Filter
3. Set Filter Expression: =(Fields!MyTagName.Value = "value1" OR Fields!MyTagName.Value = "value2" OR Fields!MyTagName.Value = "value3")
4. Set Filter Expression Type: Boolean
5. Set Filter Operator to '='
6. Set Filter Value = 'True'
That should do it.
NOTES:
There are no notes on this topic.
TODO:
SQL Server Management Studio reports a database in "Recovery Pending" status.
SOLUTION:
use master
ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE YourDatabase SET ONLINE WITH ROLLBACK IMMEDIATE
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to convert the data in a column to INIT CAP? For example you have data 'TEST DATA' and you want to change it to 'Test Data'?
SOLUTION:
CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @i INT
, @cWork NVARCHAR(4000)
SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))
SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
, @cWork COLLATE Latin1_General_BIN)
WHILE 0 < @i
BEGIN
SET @cWork = Left(@cWork, @i)
+ Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
, @cWork COLLATE Latin1_General_BIN)
END
RETURN @cWork
END
GO
NOTES:
This solution was posted by user Pat Phelan on this site.
To run issue this command: update MyTable set Mycolumn= dbo.fCapFirst(Mycolumn)