Archive for the ‘T-SQL’ Category

Find out members of a database role in SQL Server

Just a quick post with a query to identify members of a specific database role in MSSQL. ?View Code TSQLSELECT dp2.name FROM sys.database_role_members dbrm INNER JOIN sys.database_principals dp ON dp.principal_id = dbrm.role_principal_id INNER JOIN sys.database_principals dp2 ON dp2.principal_id = dbrm.member_principal_id WHERE dp.name = ‘Database Role’; You may want to use this in conjunction with a […]

Using Hints in SQL Server

Query hints are bad right? I confess to using them on odd occasions but only when other attempts to find a solution have failed. Microsoft emphasize this themselves; Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that <join_hint>, <query_hint>, and <table_hint> be used only as a […]

Get a list of all your database files

I needed a list of all the database files on a SQL Server instance. Here’s how to get this easily. First create a temp table to hold the data like so.. ?View Code TSQLSELECT * INTO #db_files FROM sys.database_files; Truncate it so we don’t duplicate nay data. ?View Code TSQLTRUNCATE TABLE #db_files; Next we use […]

Functions & sargable queries

Using functions improperly in your where clauses always prevents index usage right? I’ve been reviewing some queries generated by linq and I’ve found out this isn’t always the case. A quick demo… Create a temp tables and insert some data ?View Code TSQLCREATE TABLE #test ( id INTEGER NOT NULL PRIMARY KEY CLUSTERED, [name] VARCHAR(100) NOT NULL ); […]

Are you checking for possible integer overflow?

I realized I wasn’t! We run a couple of systems that I know stick a mass of records through on a daily basis. Better start doing this then or I might end up doing a whoopsie! Here’s a script I’ve quickly knocked up to make checking this simple. This script will check the specified tables TINYINT, SMALLINT, INT & BIGINT values […]