SQL Server Nation
For all of your SQL Server needs.
Tip of the Week » All Tags » Intermediate SQL » Beginner SQL (RSS)

Browse by Tags

  • The Numbers Table (Round 2)

    Originally I wrote about The Numbers Table over at SQL Server Central . Over there I presented the structure and a couple options on how to populate it. I won't dwell on all of that again. I'm just going to keep it simple. What is a numbers table? A numbers table is a table containing sequential integer values. The size of your table will vary depending upon how you may choose to use it. Most practical applications that I have encountered have never called for more than 100,000 records but...

  • Comparing a Sub-Query and a UDF

    There has always been a lot of confusion in terms of performance between correlated sub-queries vs user-defined functions. So, I've taken it upon myself to try to figure out which one is more effecient. The results I have found are by no means conclusive, but will hopefully give you an idea as to when to use which approach as well as a general methodology for how to compare queries for effeciency purposes. First, I'll start by creating a scalar user defined function that accepts the EmployeeID...

  • Database Role Members

    Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which database roles on your SQL Server databases. SELECT rolename = rolep.name, membername = memp.name FROM sys.database_role_members rm JOIN sys.database_principals rolep ON rm.role_principal_id = rolep.principal_id JOIN sys.database_principals memp ON rm.member_principal_id = memp.principal_id To determine which users are members of your server level roles on your instance, see this article ...

  • Using Table Aliases

    Table aliases are an often overlooked, underused, and confusing facet of database management systems. While table alaises may be confusing at first, they are wonderful for making queries easier to read, and easier to write. A table alias is nothing more than assigning a pseudo name to a table name in a SQL statement. For example, the following SELECT statement DOES NOT use a table alias because it is not helpful to do so. Table aliases are most helpful when you're joining two or more tables and...

  • How to Automate SQL Server Express Backups

    SQL Server Express is great for small business with small data requirements. It is essentially a scaled down version of SQL Server that is free. However, because it is throttled down there are some features that are missing...one such feature is the SQL Server Agent. This service is responsible for automating SQL Server tasks, such as automatically backing up your databases. While there are plenty of tools out there that you can use to automate the backup of your SQL Server database....why pay for...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems