You are browsing archives for
If you’ve restored a database onto another server, you may get the following error message when adding user logins to the database: Create failed for User ‘TMS’. (Microsoft.SqlServer.Smo) User, group, or role ‘TMS’ already exists in the current database. (Microsoft SQL Server, Error: 15023) To fix this there are two different methods depending on […]
Rob Farley has a great post on when is a SQL function not a function? Read the two below functions – they return the same thing, but one is more effecient than the other. If you can’t see why, head over to Rob’s post that shows the execution plans for a great explaination. CREATE FUNCTION dbo.FetchSales_inline(@salespersonid […]
I came across an interesting problem last night – a stored procedure that had been created years ago was suddenly coming up with this error message: UPDATE failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or […]
If you are trying to determine which rows are different between two sets, the usual method is to create a query comparing table1.Column1 != table2.Column1 but this doesn’t check for rows where one of these columns is null. isnull(table1.Column1,-1) != isnull(table2.Column1,-1) might seem like a good way to compare nullable columns – but the performance […]
Rob Farely has posted a great article on why your covering indexes in SQL Server may not be getting the perfomance you are expecting. Covering, schmuvvering – when a covering index is actually rubbish If you use covering indexes but SQL Server keeps ignoring them – read Rob’s article for some reasons why.
Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility. He also covers why you should only grant permissions to your stored […]
— Here’s a little line of code I use a lot to find out what SQL stored procedures / functions use a particular table SELECT * FROM information_schema.routines WHERE routine_definition LIKE ‘%GetThemeColours%’ –&nnbsp;INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but has all the text names instead of objectid’s with the added bonus the full stored procedure text is searchable (its in ROUTINE_DEFINITION column) – its like a super powered sp_helptext — Also check out these helpful views: — View all the columns in tables + views with full table/view name SELECT * FROM information_schema.columns — stored procedure / function parameters SELECT * FROM information_schema.parameters — output columns for table valued functions (NOT stored procedures) SELECT * FROM information_schema.routine_columns — list of tables SELECT * FROM information_schema.tables — list of views SELECT * FROM information_schema.tables — 195cac785d844883be7a61f2b54189c6
