Estimating the Size of an Index

If you’re creating a new index in SQL Server, it is good practice to make sure you have enough space on your drive to do so. You’ll not only need the space to hold it, but double the space as SQL Server builds it on temporary disk space. (https://msdn.microsoft.com/en-us/library/ms191183.aspx) So if your index is going… Read More »

Finding Duplicate Records In Database

SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK) FROM TABLE_NAME GROUP BY COL_TO_CHECK HAVING COUNT(COL_TO_CHECK) > 1 If there is more than 1 column with that value it will show up like: COL_TO_CHECK COUNT(COL_TO_CHECK) ————————————————————- Value 3 Value2 2 Value99 4

SQL Server – Find Stored Procedures User Can Access

Returns a report that has information about user permissions for an object, or statement permissions, in the current database. To find stored procedures that “username” can access: exec sp_helprotect @username = ‘username’ To see all objects in database and who has access: exec sp_helprotect For complete capabilities see http://msdn.microsoft.com/en-us/library/ms190310.aspx

SQL Server Profiler New Templates Failing

Source of info: http://www.mattbutton.com/2011/06/01/sql-profiler-templates-missing/ Upon trying to create a trace template in SQL Server Profiler, I was able to name the trace but there were no events to choose from in the “Events Selection” tab, even when the “Show All Events” box was checked.  If I clicked the “Column Filters” button the entire Profiler application would… Read More »

Rolling Back Dynamic SQL

Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code: No Transactions You can see the third row having a value of “5”, showing that it was updated CREATE TABLE #T( ID INT IDENTITY(1,1) ,COL1 VARCHAR(100) ,COL2 VARCHAR(100) ); INSERT INTO #T (COL1,COL2) VALUES(‘1′,’2’);… Read More »

SQL Server – Find Missing Indexes With Script In Output

Source: http://sqlserverplanet.com/dmvs/missing-indexes-dmv SELECT mid.statement ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure ,OBJECT_NAME(mid.Object_id) ,’CREATE INDEX [idx_’ + LEFT(PARSENAME(mid.statement, 1), 32) + ‘_’ + CONVERT(VARCHAR, mig.index_group_handle) + ‘_’ + CONVERT(VARCHAR, mid.index_handle) + ‘]’ + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL(mid.equality_columns, ”) + CASE WHEN mid.equality_columns IS NOT NULL AND… Read More »

SQL Server – Download all RDL files from Report Server

—- Allow advanced options to be changed. –EXEC sp_configure ‘show advanced options’, 1 –GO —- Update the cuently configured value for advanced options. –RECONFIGURE –GO —- Enable xp_cmdshell –EXEC sp_configure ‘xp_cmdshell’, 1 –GO —- Update the cuently configured value for xp_cmdshell –RECONFIGURE –GO —- Disallow further advanced options to be changed. –EXEC sp_configure ‘show advanced… Read More »