Browse by Tags

Enclosed you will find how an improperly constructed OUTER JOIN query can turn into an INNER JOIN. An OUTER JOIN is described as a join where you want all data from one table along with any matching data from another table. In SQL Server the default behavior of using JOIN creates an INNER JOIN. But sometimes even if you use LEFT JOIN or LEFT OUTER JOIN, the query that you build may end up being an INNER JOIN. The first thing we will do is setup some test tables. Before you critique the tables, realize...

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

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

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