The multi-part identifier ... could not be bound

There are cases where a database developer might get the error message "The multi-part identifier [database_entity_name] could not be bound".

This happens because of the way table scopes are handled by the developer within the query.

Let's see a relevant example:

Consider two tables; table Employee and table Address.

Employee table:
CREATE TABLE [dbo].[Employee](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
[age] [int] NULL
) ON [PRIMARY]

Address table
CREATE TABLE [dbo].[address](
[empid] [int] NOT NULL,
[street] [varchar](50) NULL,
[city] [varchar](50) NULL,
[country] [varchar](50) NULL
) ON [PRIMARY]

Let's say we want to write a query returning all the employees and their country of residence sorted by the latter alphabetically.

A suggested query would be the following:

select emp.name as EmployeeName, addr.country as EmployeeCountry
from [Employee] emp
inner join [Address] addr
on emp.id=addr.empID
order by addr.country asc

Indeed, the above query works fine.

Though if someone tried to get the employees' country using a subquery like this:

select emp.name as EmployeeName, (select addr.country from [Address] addr where addr.empID=emp.id) as EmployeeCountry
from [Employee] emp
order by addr.country asc

he/she would end up with the following error message:

The multi-part identifier "addr.country" could not be bound.

The problem in the above T-SQL Statement is that even though we are using the addr table alias in the subquery, we are not syntactically allowed to use it outside the scope of the subquery, that is in this example, in the order by clause. Though the opposite is possible, that is to reference a table/alias of an outer query within an internal query (subquery). That is why in our subquery we are able to reference the emp.id table/column.

For eliminating the above error and keep on using the subquery, the correct code for this case would be:

select emp.name as EmployeeName, (select addr.country from [Address] addr where addr.empID=emp.id) as EmployeeCountry
from [Employee] emp
order by EmployeeCountry

Remarks
Even though in this example the problem was obvious, in many cases where we develop some really large and complex queries along with subqueries, we might end up consuming valuable time for resolving such issues :)

To this end we should always be careful when using subqueries in our T-SQL statements and always keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries' tables.

A future post will thoroughly explain the usage of subqueries in SQL Server.

Read more on this article...

Table-Level Locking Hints in SQL Server

I was recently asked by a friend, how can we control the level of locking in SQL Server when executing SELECT, UPDATE, INSERT and DELETE statements.

The answer to this question is by using locking hints. Locking hints direct SQL Server to the type of locks to be used. Even though the SQL Server Query Optimizer automatically determines the best locking option when executing a statement of the above kinds, there are cases where a DBA/Database Developer might needs to explicitly control the level of locking.

The available locking hints in SQL Server are the following:

HOLDLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

NOLOCK
Applies to: SELECT

PAGLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

READCOMMITTED
Applies to: SELECT, UPDATE, INSERT, DELETE

READPAST
Applies to: SELECT, UPDATE, DELETE

READUNCOMMITTED
Applies to: SELECT

REPEATABLEREAD
Applies to: SELECT, UPDATE, INSERT, DELETE

ROWLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

SERIALIZABLE
Applies to: SELECT, UPDATE, INSERT, DELETE

TABLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

TABLOCKX
Applies to: SELECT, UPDATE, INSERT, DELETE

UPDLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

XLOCK
Applies to: SELECT, UPDATE, INSERT, DELETE

The syntax for using locking hints is very simple. You only have to add the expression with (LOCKING_HINT) just right after you are referencing a database table name (or after a table alias) in your query.

An example of using the NOLOCK locking hint within a SELECT statement is the following:

SELECT column
FROM table with (NOLOCK)

The following MSDN Library link fully describes the above locking hints.
Read more on this article...

Microsoft SQL Server 2008 Feature Pack

SQL Server 2008 is undoubtedly an extremely powerful Relational Database Management System. It comes with many new features that increase the database developer's productivity and make the life of the DBA easier.

In September 2008, Microsoft has published the SQL Server 2008 Feature Pack, which includes various stand-alone install packages which provide additional value for SQL Server 2008. Here's the list of features/packages:

• Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider
• Microsoft SQL Server 2005 Backward Compatibility Components
• Microsoft SQL Server 2008 Command Line Utilities
• SQL Server Compact 3.5 SP1
• Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
• Microsoft SQL Server 2005 Driver for PHP
• Microsoft Core XML Services (MSXML) 6.0
• Microsoft SQL Server 2005 JDBC Driver 1.2
• Microsoft SQL Server 2008 Management Objects
• Microsoft OLEDB Provider for DB2
• SQL Server Remote Blob Store
• Microsoft SQL Server 2008 Native Client
• Microsoft SQL Server 2008 Policies
• Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
• Microsoft SQL Server System CLR Types
• Microsoft Sync Framework
• Microsoft SQL Server 2008 Upgrade Advisor

You can download the corresponding packages from this link.

Read more on this article...

Commonly used string functions in SQL Server

In this post, I provide a list of commonly used string functions in SQL Server.
By combining these functions, it is possible to easily manipulate strings.

-- REPLICATE
-- This function given a string and an integer N, it repeats N-times the given string
-- Example:
declare @string as varchar(10)
declare @num_repeats as int

set @string='ABCD '
set @num_repeats=4

select replicate(@string,@num_repeats)


-- Concatenation
-- By using the 'plus' (+) operator you can concatenate two or more strings
-- Example:
declare @string1 as varchar(10)
declare @string2 as varchar(10)

set @string1='Hello '
set @string2='World!'

select @string1 + @string2


-- REPLACE
-- This function given a string, a string pattern and a replacement string,
-- it finds the given pattern in the string and replaces it with the replacement string
-- Example:
declare @string as varchar(20)
declare @pattern as varchar(10)
declare @replacement_string as varchar(10)

set @string='Hard drive'
set @pattern='drive'
set @replacement_string='disk'

-- Original Expression
select @string

-- Modified Expression
select REPLACE(@string,@pattern,@replacement_string)


-- SUBSTRING
-- This function given an expression, an integer pointing to the starting position
-- and an integer representing the characters length, it returns the corresponding
-- part of the expression
-- Example:
declare @expression as varchar(20)
declare @start_index as int
declare @length as int

set @expression='Hello_World'
set @start_index=1
set @length=5

-- Original Expression
select @expression

-- Modified Expression
select SUBSTRING(@expression ,@start_index,@length)


-- CHARINDEX
-- This function given a string and a pattern, it returns a pointer (int) to the starting
-- position of the latter
-- Example:
declare @string as varchar(20)
declare @pattern as varchar(10)

set @string='SQL Server'
set @pattern='Server'

select CHARINDEX(@pattern,@string)


-- LEN
-- This function given a string, it returns its size in terms of number of characters
-- Example:
declare @string as varchar(20)

set @string='SQL Server'

select LEN(@string)


-- DATALENGTH
-- This function given an expression, it returns the number of bytes used
-- Example:
declare @expression as varchar(20)
set @expression='SQL Server'

select DATALENGTH(@expression)


-- ASCII
-- This function, given a character expression, it returns ASCII code value of its leftmost character.
-- Example:
declare @char_expression1 as char(1)
declare @char_expression2 as char(1)
declare @char_expression3 as char(1)

set @char_expression1='S'
set @char_expression2='Q'
set @char_expression3='L'

-- Get the ASCII code values for the characters 'S','Q', 'L'
select ASCII(@char_expression1)
select ASCII(@char_expression2)
select ASCII(@char_expression3)


-- CHAR
-- This funtion converts ASCII code values back to characters
-- Examples (based on the previous ASCII example):
declare @char_expression1 as char(1)
declare @char_expression2 as char(1)
declare @char_expression3 as char(1)

set @char_expression1='S'
set @char_expression2='Q'
set @char_expression3='L'

-- Get the ASCII code values for the characters 'S','Q', 'L' and then decode
-- them back to characters
select CHAR(ASCII(@char_expression1))
select CHAR(ASCII(@char_expression2))
select CHAR(ASCII(@char_expression3))

-- Construct the word!
select CHAR(ASCII(@char_expression1)) + CHAR(ASCII(@char_expression2)) + CHAR(ASCII(@char_expression3))


-- SPACE
-- This function given an integer N, it returns N blank spaces
-- Example (with concatenation):
declare @string1 as varchar(15)
declare @string2 as varchar(15)
declare @spaces as int

set @string1='Before_Spaces'
set @string2='After_Spaces'
set @spaces=10

-- Original Expression
select @string1 + @string2

-- Modified Expression
select @string1 + SPACE(@spaces) + @string2


-- LEFT
-- This function, given a string and an integer N, it returns the first N characters
-- of the string counting from the left
-- Example:
declare @string as varchar(15)
declare @num_chars as int

set @string='Automobile'
set @num_chars=4

-- Original Expression
select @string

-- Modified Expression
select LEFT(@string,@num_chars)


-- RIGHT
-- This function, given a string and an integer N, ir returns the first N characters
-- of the string counting from the right
-- Example:
declare @string as varchar(15)
declare @num_chars as int

set @string='Automobile'
set @num_chars=6

-- Original Expression
select @string

-- Modified Expression
select RIGHT(@string,@num_chars)


-- REVERSE
-- This function given a string, it reverses it
-- Example:
declare @string as varchar(15)
set @string='1234'

-- Original Expression
select @string

-- Modified Expression
select REVERSE(@string)


-- LOWER, UPPER
-- These two functions given a character expression, they set it to lowercase
-- or uppercase respectively
-- Examples:
declare @expression1 as varchar(15)
declare @expression2 as varchar(15)

set @expression1='SQL SERVER'
set @expression2='sql server'

select LOWER(@expression1)
select UPPER(@expression2)


-- LTRIM, RTRIM
-- These two functions given a character expression, they remove the
-- leading and trailing blank spaces respectively
-- Examples:
declare @expression as varchar(30)
declare @additional_string as varchar(15)

set @expression=' SQL SERVER '
set @additional_string=' 2008'

-- Original Expression
select space(10)+ @expression + space(10) + @additional_string

-- Modified Expressions
select LTRIM(space(10) + @expression + space(10)) + @additional_string
select RTRIM(space(10) + @expression + space(10)) + @additional_string


For more information on string functions in SQL Server, you can visit this MSDN Library link.

Read more on this article...

SQL Server 2005 Surface Area Configuration Tool

An important security improvement in Microsoft SQL Server 2005 is that various of its features that could be potentially susceptible to security risks are turned off by default.

One of these features is that the built-in administrator of the machine on which SQL Server 2005 is installed, it is not automatically included in the SQL Server Administrators (SysAdmin role).

In the case you want to change this setting, you have to use the SQL Server 2005 Surface Area Configuration tool.

This tool can be found within the "Microsoft SQL Server 2005" program group (Configuration Tools --> SQL Server Surface Area Configuration) in Start Menu --> Programs.

When running the tool, you have to select "Add New Administrator". Then you will be presented the available SQL Server privileges which you can grant to your Windows user. Such privileges can be SQL Server SysAdmin role, Analysis Services Administrator role, etc.

Additionally you can use the Surface Area Configuration Tool for SQL Server Services, Connections and Features.

In SQL Server 2008 the Surface Area Configuration tool has been removed. The features of the tool have been replaced and enhanced by the powerful Policy-Based Management features.

A future post will explain in detail the Policy-Based Management Framework in SQL Server 2008.

For more information on using Policy-Based Management in Microsoft SQL Server 2008 you can visit the following Microsoft TechNet link:

http://technet.microsoft.com/en-us/library/bb510667.aspx

Read more on this article...

Getting table index information in SQL Server

At some time today, I had to compare the indices of various database tables.
To this end, I needed a fast way of getting index information for specific tables.

In SQL Server, there is a system stored procedure (hosted in the master database) called sp_helpindex.

The syntax for using it is the following:

EXEC sp_helpindex 'schema.table_name'

The execution of the stored procedure will return the following information for all the indices in the given table:

• index name
• index description (clustered, nonclustered, etc.)
• index keys
  

Applies to SQL Server 2000, 2005 and 2008.
Read more on this article...

SQL Server 2005 Service Packs

Last night I installed Microsoft BizTalk Server 2006 R2.

Though, during the configuration process of BizTalk, I was prompt that SQL Server 2005 Service Packs were not detected.

At that time I though to myself; how do I find if any, and which Service Pack is installed for SQL Server?

By reading the Microsoft Help and Support Article on the following link I solved my question:

http://support.microsoft.com/kb/321185

In the article I found that I just had to execute the following query in my SQL Server Instance:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The first column presents the product version for SQL Server, the second presents the product level (i.e. RTM, SP1, SP2, etc) and the third column presents the edition of the installed SQL Server (i.e. Developer Edition).

The product level column was empty :) To this end I directly proceeded with installing SP2 and continued with the configuration of BizTalk Server.

Anyhow, Service Pack 3 for SQL Server 2005 was released on December 15th 2008 so I guess I have to install this as well! :)

On the following link you can find information on how to obtain the latest Service Pack for SQL Server 2005:

http://support.microsoft.com/kb/913089
Read more on this article...