Passing Table Type to Stored Procedure
User-Defined Table Types Stored Procedure Code Note: Need to make sure the type type has the right access permissions.
SQL Server Build Verions
RTM SP1 SP2 SP3 SP4 Most Recent Version ↓SQL Server 2019 15.0.1600.8 (CTP 3.0) ↓SQL Server 2017 14.0.1000.169 14.0.3192.2 (CVE-2019-1068) ↓SQL Server 2016 13.00.1601.5 13.0.4001.0 13.0.5026.0 13.0.5366.0 (CVE-2019-1068) ↓SQL Server 2014 12.00.2000.8 12.0.4100.1 12.0.5000.0 12.0.6024.0 12.0.6293.0 (CVE-2019-1068) ↓SQL Server 2012 11.00.2100.60 11.0.3000.0 11.0.5058.0 11.0.6020.0 11.0.7001.0 11.0.7469.6 (FIX for SP4) ↓SQL Server 2008R2 10.50.1600.1 10.50.2500 10.50.4000 10.50.6000 10.50.6560.0 (KB4057113) ↓SQL Server 2008... » read more
Database Backup Encryption
Restoring encrypted database on another server (using Backup Encryption). Only works with SQL Server 2014 and later. Backup If a certificate is not backed up prior to backing up a database in T-SQL the following warning will be displayed in the results: Warning: The certificate used for encrypting the database encryption key has not been... » read more
SQL Profiler
File Folder Location: PROFILER.EXE C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn D:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\ Running under a different domain… If you create a cmd file, you might need to copy over the cmd file to the profiler directory and run it from there. To filter to only Stored Procedures… Events -> Check... » read more
Run SSMS Under A Different Domain
SQL Server Management Studio interface will prevent you from using domain other than the one you are currently login as. To use another domain, use a .bat file runas. SSMS 17 SSMS 18 Profiler
Starting SSMS using bat file.
Create a bat file using the following…
Currency Type in SQL Server
Money vs Decimal(9,2) Precision defines total length of the number. Scale represents only position of the delimiter. Decimal(9, 2) will store numbers like 1234567.89 that is 9 digits so it will be 5 bytes. Decimal(9,2) = X,XXX,XXX.XX
Detach and Attach Database
Detaching a Database Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. You cannot detach... » read more
Backup and Restore Database with Move
Database Backup Database Info Use the following script to get information about the database backup file. Database Restore Note: Folder must be created first. Check Progress Sources: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-to-a-new-location-sql-server?view=sql-server-2017
Shrinking Database Files
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system. To shrink... » read more