A community of more than 1,600,000 database professionals and growing

I started using computers a long time ago, and in the PC world, we were often limited to an 8 character name and a 3 character extension. Unix and MacOS allowed longer names, and many of us in DOS and Windows were jealous. Eventually Windows evolved, allowing long names, spaces, and really quite a bit of latitude in what you want to name a file. For example, try this and see what happens on your SQL Server:

 BACKUP DATABASE Sandbox TO DISK = 'Sandbox.thisisafullbackupthatIusetostartarestore'

However, the three character extension still dominates, and many applications still use this. Microsoft has started to get away from this, as we have .docx, .xlsx, etc. Other vendors and software systems have started to expand names slightly. I was reading about the SQL Server Diagnostics Preview and noticed that the engineers will take a dump file (.dmp) as well as a mini dump (.mdmp) and a filtered dump (.hdmp).

Now I know developers are lazy, and they don't like to type, but in these days of auto-completion and other tools, why are we limiting ourselves. Why wouldn't we use .minidump or .filtereddump as descriptive way of identifying the file? If we are no longer bound, why not include a better extension? I can't imagine that the filesystem for many tools would be stressed by longer names.

I'm assuming that people still feel bound to using the shortest set of letters that they think are unique, but with the growth of software applications from many, many sources, why not just be more descriptive? Would you want to see better filenames? I know I would.

Join the debate, and respond to today's editorial on the forums

1 - comparing a SQL_VARIANT containing a varchar ('hey') with another SQL_VARIANT containing a varchar ('hey) will be ok
 2 - comparing a SQL_VARIANT containing an integer (123) with another SQL_VARIANT containing a varchar ('123') will fail
 3 - comparing a SQL_VARIANT containing an integer (123) with another SQL_VARIANT containing a integer (123) will be ok
 
 4 - comparing a SQL_VARIANT containing a varchar ('hey') with a VARCHAR(3) ('hey') will be ok
 5 - comparing a SQL_VARIANT containing an integer (123) with a VARCHAR(3) ('123') will fail; neither the 123 on table side will not be converted implicit to varchar nor the '123' to integer
 6 - comparing a SQL_VARIANT containing an integer (123) with a VARCHAR(3) (123) will fail too, since the integer 123 in the set statement will be converteted implicit into '123' (= same behavior as in answer 5)
 
 7 - comparing a SQL_VARIANT containing a varchar ('hey') with an explicit converted SQL_VARIANT ('hey') will be ok
 8 - comparing a SQL_VARIANT containing an integer (123) with varchar that was converted into SQL_VARIANT will fail, since the SQL_VARIANT contains the varchar '123'
 9 - comparing a SQL_VARIANT containing an integer (123) with varchar that was converted into SQL_VARIANT will fail, since the SQL_VARIANT contains the varchar '123' (in the SET statement 123 was converted implicit into '123')

10 - comparing an explicit convertet varchar ('hey') with another varchar ('hey') will be ok
11 - comparing an explicit convertet varchar (123 -> '123') with another varchar ('123') will be ok
12 - comparing an explicit convertet varchar (123 -> '123') with another varchar ('123') will be ok (in the SET statement 123 was converted implicit into '123')

13 - comparing an explicit convertet varchar ('hey') with a SQL_VARIANT containing a varchar ('hey') will be ok (similar to answer 1)
14 - comparing an explicit convertet varchar (123 -> '123') with a SQL_VARIANT containing a varchar ('123') will be ok
15 - comparing an explicit convertet varchar (123 -> '123') with a SQL_VARIANT containing an integer (123) will fail (because of datatype mismatch)

So when you have to handle SQL_VARIANTS (e.g. when working with sys.partition_range_values or sys.extended_properties), you have to ensure, that your variable is a SQL_VARIANT too and was filled with exact the same data type (answers 1 and 3).

If this is not possible (since you do not know, if the integers where inserted as integer or as varchar) the best solution (imho) would be to explicit convert the SQL_VARIANT on the table side into a VARCHAR and compare it with a VARCHAR variable (answers 10 to 12).
Remark: if you have an index on the SQL_VARIANT column, it would do an index scan (instead of seek) in this case, since the explicit convert will make it non-sargable. And of course you have to be careful when dealing with date or decimal datatypes (because of language settings).

See click here

» Discuss this question and answer on the forums