For Error 701, insufficient system memory, compare max server memory with min server memory, check the paging file size, and free caches with DBCC commands.
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT: how each one returns the last identity value, and why a trigger can make @@IDENTITY give the wrong one.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
Clean Cache commands affect different parts of SQL Server memory. I separate buffer-pool testing from execution-plan testing.
SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Msg 128, The name is not permitted in this context, shows up when one column is used as the DEFAULT of another. Use only constants, expressions or variables.
Auditing Who Holds CONTROL SERVER and IMPERSONATE Rights
Audit CONTROL SERVER and impersonation grants alongside sysadmin and role inheritance, test effective rights, and remove excess access.
SQL SERVER – SQL Commandments – Suggestions, Tips, Tricks
My 20 SQL Commandments for SQL Server, rewritten from an Oracle article: know your data, test with realistic data, write identical statements, and more tips.
SQL SERVER – Fix: Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: 08001, Native Error: 17
A Sqllib error with hr = 0x80004005 and Native Error 17 says SQL Server does not exist or access denied. The simple fix is to enable TCP-IP in Configuration Manager.
SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
DBCC CHECKIDENT with RESEED lets you reset table identity to any value. Reseed to 0 so the next insert gets 1, and never go below values already in the table.
SQL SERVER – Union vs. Union All – Which is better for performance?
UNION removes duplicate rows like SELECT DISTINCT, while UNION ALL keeps them all. My early notes on Union vs. Union All, with a link to the rewritten post.
Checking the Update History of an Instance
Reconstruct SQL Server update history from Setup Bootstrap logs, startup error logs, and the current build when no patch ledger exists.