Use WITH ENCRYPTION to hide the code of a stored procedure or UDF, but keep a copy of the script. Once encrypted, there is no simple way to decrypt it.
SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)
Msg 131 says the size given to varchar exceeds the maximum allowed (8000). Use VARCHAR(8000), or VARCHAR(MAX) in SQL Server 2005 as the TEXT replacement.
SQL SERVER – Recompile All The Stored Procedure on Specific Table
After BCP or DTS mass inserts, stored procedures can slow down. I use sp_recompile to recompile all the stored procedure on a table, or just one procedure.
REPLACE Return Limits: Cast the Input to max
REPLACE return limits can truncate text at 8,000 bytes. Cast the searched input to max first, then compare Unicode and non-Unicode byte lengths.
SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file
What is xp_cmdshell used for, and how do you run a batch file using T-SQL? Examples run a system command, a plain BAT file and one with a parameter.
SQL SERVER – 2005 List All Tables of Database
The simple way to list all tables of a database in SQL Server 2005: select from sys.tables to get every table that users have created.
SQL SERVER – Explanation and Example Four Part Name
What is a four part name in SQL Server? It is ServerName.DatabaseName.DatabaseOwner.TableName, for example localhost.AdventureWorks.Person.Contact.
SQL SERVER – Repeate String N Times Using String Function REPLICATE
REPLICATE repeats a string N times. I found this T-SQL string function while searching for replication, and it is handy when you need repeated text.
Memory Grants: Why a Query Waits for Memory
Memory grants can leave SQL Server queries waiting on RESOURCE_SEMAPHORE; inspect estimates, grants, and spills before adding memory.
SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005
What is Index Fragmentation and how do page splits cause it? How to measure it with DBCC SHOWCONTIG in SQL Server 2000 and sys.dm_db_index_physical_stats in 2005.