I think this is the best way to get the month name when you have the month number Select DateName( month , DateAdd( month , @MonthNumber , 0 ) – 1 ) Or Select DateName( month , DateAdd( month , @MonthNumber , -1 ) )
SELECT DATEPART(HOUR, GETDATE()); DATEPART documentation
Yes, in SQL Server 2005 it’s possible to use a variable in the top clause. select top (@top) * from tablename
The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible. If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN. EDIT: In … Read more
Disk space is not the issue… but memory and performance will be. Double the page reads, double index size, strange LIKE and = constant behaviour etc Do you need to store Chinese etc script? Yes or no… And from MS BOL “Storage and Performance Effects of Unicode” Edit: Recent SO question highlighting how bad nvarchar … Read more
( SELECT * FROM table1 EXCEPT SELECT * FROM table2) UNION ALL ( SELECT * FROM table2 EXCEPT SELECT * FROM table1)
This is my first crack at a query, based on Andomar’s suggestions. This query is intended to provide a list of permissions that a user has either applied directly to the user account, or through roles that the user has. /* Security Audit Report 1) List all access provisioned to a sql user or windows … Read more
I think this: SELECT ISNULL(NULLIF(listing.Offer_Text, ”), company.Offer_Text) AS Offer_Text FROM … is the most elegant solution. And to break it down a bit in pseudo code: // a) NULLIF: if (listing.Offer_Text == ”) temp := null; else temp := listing.Offer_Text; // may now be null or non-null, but not ” // b) ISNULL: if (temp … Read more
If you are using SQL 2005 you can do something like this… SELECT rs.Field1,rs.Field2 FROM ( SELECT Field1,Field2, Rank() over (Partition BY Section ORDER BY RankCriteria DESC ) AS Rank FROM table ) rs WHERE Rank <= 10 If your RankCriteria has ties then you may return more than 10 rows and Matt’s solution may … Read more
In most cases connection pooling problems are related to connection leaks. Your application probably doesn’t close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method. You want to make sure that you are really closing the … Read more