In my previous post, we looked at the various naming conventions available to us when working with database objects.

Often we may face the need to either generate three-part object names for all objects in the database, or parse a three part name into it’s constituent parts. This post provides quick scripts to achieve this.

Generating Multi-Part Names

Generating multi-part names is a simple affair of querying the sys.objects table as shown in the script below:

USE AdventureWorks2012;
GO

SELECT CAST(SERVERPROPERTY('ServerName') AS VARCHAR(100)) + '.' + 
           DB_NAME() + '.' + 
           SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS FourPartName,
       DB_NAME() + '.' + 
           SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS ThreePartName,
       SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS TwoPartName,
       so.schema_id AS SchemaId,
       so.name AS ObjectName,
       so.type AS ObjectType,
       so.type_desc AS ObjectTypeDescription
FROM sys.objects AS so
WHERE so.is_ms_shipped = 0 AND so.type IN ('U','P','V','TR')
ORDER BY so.schema_id ASC, so.type DESC;
GO

Parsing Multi-Part Names

Parsing multi-part names does not involve any fancy string manipulation, but can be easily done using a system function – PARSENAME().

The PARSENAME function accepts two parameters – the multi-part object name to be parsed and a number from 1-4 indicating the part that needs to be fetched from the supplied name, as shown below:

• 1 = Object Name
• 2 = Schema Name
• 3 = Database Name
• 4 = Server Name

If a particular name is not found in the string supplied, the PARSENAME() function returns NULL.

The query to parse multi-part names into their constituents is available below:

USE AdventureWorks2012;
GO

DECLARE @objectNames TABLE (MultiPartName VARCHAR(100));

INSERT INTO @objectNames (MultiPartName)
VALUES ('W8SQLSERVERSQL2K12.AdventureWorks2012.HumanResources.Employee'),
       ('AdventureWorks2012.HumanResources.Employee'),
       ('HumanResources.Employee')

SELECT oname.MultiPartName AS SuppliedMultiPartName,
       PARSENAME(oname.MultiPartName,4) AS ServerName,
       PARSENAME(oname.MultiPartName,3) AS DatabaseName,
       PARSENAME(oname.MultiPartName,2) AS SchemaName,
       PARSENAME(oname.MultiPartName,1) AS ObjectName
FROM @objectNames AS oname;
GO

Notice that the PARSENAME() function returned NULL for the ServerName column when interpreting a three-part parameter. Similarly, we get NULL for ServerName and DatabaseName when working with a two-part name.

I trust the scripts above were useful to you.

Further Reading

• PARSENAME() – http://msdn.microsoft.com/en-us/library/ms188006.aspx
• TIP: PARSENAME simply parses the string based on the decimal point (.) – it can therefore be used for string manipulation as well!

Until we meet next time,

Be courteous. Drive responsibly.

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.