Tip of the Week » All Tags » 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...

  • Deleting Duplicate Values in SQL Server 2005

    SQL Server 2005 provides a very easy way to remove duplicate values through the use of a Common Table Expression (CTE). The script below creates a temp table named #PendingAccounts. Notice that I will check in the tempdb to see if the table already exists. If it does, I will drop it. IF OBJECT_ID('tempdb..#PendingAccounts') IS NOT NULL DROP TABLE #PendingAccounts GO CREATE TABLE #PendingAccounts ( AccountID INT NOT NULL, PendingDate SMALLDATETIME, PendingAmount MONEY ) GO Below I am adding...

  • 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...

  • IDENTITY Columns

    SQL Server Identity Columns SQL Server IDENTITY columns are numeric columns which are set to auto increment when new values are inserted into the table. These columns are great for uniquely identifying records in a table, especially when you do not have any natural key to define a primary key constraint on the table. Like a primary key, there can only be one IDENTITY property defined on a column in a table. An IDENTITY column has a seed value (where the values begin) and the numeric increment value...
    Filed under: ,
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems