The topic of this blog post is not something that is new, but is intended to address a common issue I have found in most database documentation. Most database documentations fail to address name resolution errors – i.e. the ability to ensure that a name uniquely identifies a database object.

For example, it is quite possible to create an Employee table in the Person or dbo schema in the AdventureWorks2008R2 database even though another table with the same name already exists in the HumanResources schema.

Good database documentation is one which leaves very little room for interpretation – especially when ownership of objects and security relationships is concerned. This post looks at the most commonly used naming conventions when documenting databases and finally comes up with a recommendation for their usage.

Three-Part Naming

The three part naming is the standard for unique identification of any database object. The three part naming convention essentially consists of:

• Database Name
• Schema Name
• Object Name

The standard representation is like: [Database Name].[Schema Name].[Object Name].

Four Part Naming

A common variant of the 3-part naming convention is the 4 part naming convention, which also adds the SQL Server instance name to the mix, making the representation like: [SQL Instance Name].[Database Name].[Schema Name].[Object Name].

This naming convention is essential when attempting to access data across remote/linked servers. However, it is strongly tied to the SQL Server instance name, and therefore I do not prefer it when documenting my databases.

Two Part Naming

Most applications do not have a restriction on the database name (this was not the case for legacy systems). In such cases, having the [Database Name] in the three part naming convention does not make sense. Most documentation therefore adopt what is called as the two-part naming standard.

Under the two-part naming standard, a database object is represented as: [Schema Name].[Object Name].

It is my humble request to the kind reader to please use the two-part naming convention at the very least (even when using the default “dbo” schema).

Other Variants

Those coming directly from the world of SQL Server 2000 or those used to working within the default “dbo” schema would omit the “Schema Name” and the representation would be like: [Database Name]..[Object Name].

I do not subscribe to this theory as it makes the documentation open to interpretation.

For example, in cases when the database design is such that all objects use a schema other than “dbo” does omitting the schema name indicate that all objects use the available user-defined schema or that they use “dbo”? It is therefore, my recommendation that when writing database documentation, a two-part naming convention should be used at the very least.

Conclusion

Name resolution is not a problem limited to the database documentation. When ambiguous naming is encountered in database code, the results can be confusing and make troubleshooting even more difficult. The recommendation for usage of naming conventions in documentation or in database code is therefore:

• The two-part naming convention is sufficient to prevent name resolution errors within the same database
• When more than one database is involved, the three-part naming convention should be used
• Similarly, when more than one SQL Server instance is involved, the full length four-part naming convention is required

All other variants leave room for interpretation, and are not something I would recommend.

I would be happy to hear about the naming convention followed by you, the kind reader. Do leave a note in the blog comments as you go.

Until we meet next time,

Be courteous. Drive responsibly.

When I was studying databases, our instructors always told us to use ISO-compliant SQL code as much as possible. The prime advantage that this strategy has is that the code remains immune to the changes made to vendor specific SQL code. Ever since I wrote the piece on “CAST v/s CONVERT”, I have been looking for ISO equivalents to the SQL code I write.

Recently, I was writing a simple IF EXISTS check and from a purely academic standpoint, wondered if it’s ISO equivalent (SOME or ANY) can be used in SQL Server. Today’s post is based on the confirmatory test I did.

What does EXISTS do?

Very briefly, the EXISTS keyword checks if a particular sub-query returns any records or not. The sub-query to be evaluated is typically fetching records matching a particular condition defined by the business. For example, an HR function may want to identify if any employees exist who have accumulated more than n number of vacation days. A query using the EXISTS keyword will look like the following:

USE AdventureWorks2012;

GO

DECLARE @minimumUnusedVacationHours INT = 40;

IF EXISTS (SELECT * FROM HumanResources.Employee WHERE VacationHours > @minimumUnusedVacationHours)

    BEGIN

        PRINT ‘Found using EXISTS’;

    END

ELSE

    BEGIN

        PRINT ‘Not Found using EXISTS’;

    END

GO

The ISO equivalent – SOME or ANY clause

If we simplify the sub-query that is typically used for an EXISTS evaluation, what happens is that a scalar value is compared with a set of values. A special case would be to compare the scalar value with a single-column set, which is the most common condition found in production systems today.

The ISO equivalent to EXISTS is the SOME or ANY clause. The query shown above can therefore also be written as:

USE AdventureWorks2012;

GO

DECLARE @minimumUnusedVacationHours INT = 40;

IF @minimumUnusedVacationHours < SOME (SELECT VacationHours FROM HumanResources.Employee)

    BEGIN

        PRINT ‘Found using SOME’;

    END

ELSE

    BEGIN

        PRINT ‘Not Found using SOME’;

    END

GO

Conclusion

While it may be quite difficult to find production code that uses these ISO equivalents (my own code does not use SOME or ANY), it is still interesting to know about the basic standards that drive a vendor specific implementation.

A tip for all the students out there: These standards are the building blocks of any SQL-based RDBMS system in the market today – knowing them makes understanding SQL Server a lot easier.

Please note that it is important to keep in mind that ISO equivalents for all T-SQL operators may not be available (e.g. MERGE is a Microsoft SQL Server specific keyword and will not have an ISO equivalent).

Further Reading:

• SOME or ANY: http://msdn.microsoft.com/en-us/library/ms175064.aspx
  
• Comparison operators modified by ANY, SOME or ALL: http://msdn.microsoft.com/en-us/library/ms187074(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.

Working in a solutions consulting organization has it’s own challenges and benefits. This includes the opportunity to discuss the features/challenges and solutions that are being implemented across the organization.

Recently, one of my colleagues (let’s call him Jim) called me up to discuss the implementation for a particular requirement in their project. The requirement was to implement a windows like wild-card search in SQL Server.

Jim: Good morning, Nakul! Do you have some time to discuss possible implementation for a requirement that I am working on?

Me: Sure. Please go ahead, I will try to do my best to help you out.

Jim: The product that I am working on has a document management feature that allows users to upload files into the system. The users can then search for these files and then take a bunch of actions on the results. The problem is that on the application side, the user experience has to be same as that offered by the Windows operating system, i.e. the user will search using an asterisk [*] as the wild-card character. SQL Server does supports a percentage/modulo [%] as the wild-card, so the two requirements are conflicting.

Me: That’s an interesting feature. One of the first things that come into mind is that you can do a replace on * with %. Did you try it out?

Jim: I could probably wrap the search into a stored procedure and do that. Let me try it out.

Me: Sure. Take your time. Let me know how it goes.

[After 10-15 minutes…]

Jim: We do seem to be making progress, however, the percentage character [%] is a valid character in a filename. SQL Server treats it as a wild-card and the query does not work as expected.

Me: Hmm…ok. Let me thing over it for a while. I will get back to you in the next half-hour.

[After 30 minutes…]

Me: Hello, Jim! It looks like I have a solution for you. I searched the Books On Line and based on that, I just sent you a query via E-mail. Can you open it? I will walk-you through it.

Jim: Absolutely! I just received your E-mail. Let me open up the query.

Me: In the first part of the query, let us just create a dummy table to hold the test data, and insert some test data into it.

USE tempdb;
GO

--Safety Check
IF OBJECT_ID('FileNames') IS NOT NULL
    DROP TABLE FileNames;
GO

--Create a dummy table to hold the test data
CREATE TABLE FileNames (FileId INT IDENTITY (1,1),
                        FileNameValue VARCHAR(255)
                       );
GO

--Insert some test data
INSERT  INTO FileNames ( FileNameValue )
VALUES  ( 'Nakul Vachhrajani.xls' ),
        ( 'BR.xlsx' ),
        ( 'Blogs.docx' ),
        ( 'Swipe Times for Nakul.xls' ),
        ( 'Trip Details for Nakul.xlsx' ),
        ( 'Sample%File.txt' ),
        ( 'SampleFile.txt' );
GO

Me: As you can see, I have tried to insert test data that covers different file names & types and also has a record with the percentage character [%]. The next part of the query creates a stored procedure that will perform the search.

USE tempdb;
GO

CREATE PROCEDURE dbo.uproc_SearchFileName
    @SearchStringEnteredByUser VARCHAR(255)
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT  dt.FileId,
            dt.FileNameValue
    FROM    FileNames AS dt
    WHERE   dt.FileNameValue LIKE REPLACE(REPLACE(@SearchStringEnteredByUser, '%', '/%'), '*', '%') ESCAPE '/';
END;
GO

Me: As you can see here, the stored procedure basically replaces asterisk [*] with [%]. What it also does is replace the percentage [%] with a combination of a forward-slash and the percentage [/%]. What this allows me to do is to use the ESCAPE keyword to let SQL Server know that it should skip the percentage and not treat it as an escape character. The next part of the script executes this stored procedure in a couple of different ways to show that this logic does work.

USE tempdb;
GO
EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = '*.xlsx';
GO

EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = '*Nakul.xls';
GO

EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = 'Sample%*.txt';
GO

Me: So that’s it. A simple REPLACE operation with the use of the ESCAPE keyword can help you achieve this requirement. Did it make sense?

Jim: Absolutely! It’s so simple yet efficient. I will do a bit more testing and tweaking, but I think I am all set now.

Me: I am glad I could help. Have a good day!

Further Reading

Pattern Matching in SQL Server: http://msdn.microsoft.com/en-US/library/ms187489(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of the project teams at the office had a customer-specified requirement to use a SQL Server running a server collation other than the English default (SQL_Latin1_General_CP1_CI_AS).

Please NOTE: Collation is a vast topic. The scope of this post is only present the means to choose the server collation at the time of installation or afterwards.

We have two major variants of the English language supported by Windows:

• “English (United Kingdom)”, Windows LCID = 2057 (Hex: 0x809)
• “English (United States)” , Windows LCID = 1033 (Hex: 0x409)

Each Windows LCID maps to a SQL Server side’s SQL LCID. Per the article “Collation Settings in Setup”, both 2057 and 1033 map to a single collation – 1033 (0x409), which is SQL_Latin1_General_CP1_CI_AS.

Unfortunately, the IT team who setup the servers for them had used the default values during the installation. When the issue was realized, they tried to change the server collation by changing the collation of the master database (because the collation of the SQL Server instance is that collation of the master database), which returned the following error:

Cannot alter the database ‘master’ because it is a system database.

Workaround (Reactive)

When their attempts failed, they called me and my instant reason was that because this is a customer-specified requirement, they would have to do the following:

1. Backup and drop all user databases
2. Rebuild the master database by specifying the new collation name in the SQLCOLLATION property of the SQL Server setup (command-line) as shown in the Books On Line here
3. Re-create all user databases

Ideal solution (Proactive)

The ideal solution here would be to setup the SQL Server such that it uses the required collation during installation itself. The Server Configuration page (step #12) in the SQL Server setup allows the administrator to specify the collation that SQL Server would use:

By default, the SQL Server setup chooses a collation that matches to the operating system settings. However, the administrator can use the “Customize” button to choose a collation of their choice.

References/Further Reading:

• Using SQL Server Collations: http://msdn.microsoft.com/en-us/library/ms144260.aspx
• Collation Settings in Setup: http://msdn.microsoft.com/en-us/library/ms143508.aspx
• Setting and changing the Server Collation: http://msdn.microsoft.com/en-in/library/ms179254(v=sql.110).aspx
• Collation & Unicode support: http://msdn.microsoft.com/en-us/library/ms143503.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of my colleagues asked me an age-old question – “How to ensure that I am able to access my SQL Server instance using my Windows login even if I forget the ‘sa’ login?” They had a long un-used instance of SQL Server for which they had forgotten the “sa” password. They had attempted to use Windows authentication, but had failed to login.

This incident was fresh in my memory when I re-installed the SQL Server instance on my test environment at home. Hence, it was the perfect time to write this post.

Proactive step

The ideal risk management strategy is to mitigate all them before they arise. Keeping this in mind, a simple step from the IT team (or whoever is in-charge of installing the SQL Server instance) can help ensure that at least one user always has access to a SQL Server instance at any given point in time.

As we all know, SQL Server supports two login mechanisms:

• Windows Authentication (default)
• Mixed Mode – SQL Server Authentication & Windows Authentication

In the interest of security, SQL Server has been designed such that Windows Authentication can never be turned OFF.

In SQL Server 2005, all members of the BUILTINAdministrators group were automatically members of the sysadmin fixed server role, and were therefore SQL Server administrators. Starting SQL Server 2008 however, this was no longer the case. In the “Database Configuration” screen of the SQL Server installer, one now needs to explicitly specify windows logins (or groups) that need to be added as sysadmins on the new SQL Server instance being installed.

Database Configuration Screen

When running the SQL Server 2012 installer, this information is requested in the “Database Configuration” screen (step #13):

As shown in the screen-shot above, you can do one of the following:

1. Add Current User – adds the currently logged-in user (under whose context the installer is running)
2. Add – Choose the domain or local user to add
3. Remove – Choose to remove any users added accidentally

The screen-shot below shows that when installing my SQL Server 2012 instance, I choose Mixed mode authentication and added myself as an admin:

Typically what happens is that when IT teams install SQL Server instances for development and QA teams, they set Mixed Mode authentication to ON, but forget to add one windows or local user as a SQL sysadmin. If the team now forgets the “sa” password, they are left with a SQL Server instance to which the applications can connect using other roles and users but cannot administer it anymore.

Moral of the story: The SQL Server installation checklists being followed by your team must have a check to ensure that at least one login (either that of the configuration manager on the team, or a local login) be registered as an administrator on the SQL Server.

Word of caution: Do note that whoever has been added to the sysadmin fixed server role has unrestricted access to the Database Engine.

Reactive step

While managing a risk in a proactive way is the ideal way, one might always find exceptions. As in the case of my colleague, it is possible to be left with an instance that one cannot administer because of not having the correct login credentials.

In such cases, I refer to an excellent blog post by my friend, Chintak Chhapia (blog) on How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password. Thanks to his recommendations, the colleague of mine had access to their server in almost no time!

Until we meet next time,

Be courteous. Drive responsibly.