Today's editorial is a guest post from Phil Factor as Steve is on sabbatical.
One of the things that tend to trip up newcomers to SQL Server is the fact that certain functions don’t really work as you’d expect. Some functions, such as ISNUMERIC, are entirely useless for any purpose, and are just there for backward compatibility, and possibly as a joke - try Select isNumeric(','), for example; yes, it thinks that the comma is a number: to quote MSDN ‘ISNUMERIC returns 1 for some characters that are not numbers’. You bet! The grey-muzzled developers will use the TRY_PARSE() function or create a scalar user-defined function that is appropriate for the numeric datatype whose validity they are testing.
LTRIM and RTRIM are string operators that will trap the innocent programmer. These don’t work as they do in any other language. They only trim ASCII space rather than any whitespace character. They were designed for a different problem, the fact that a long time ago, strings could be ‘packed’ with spaces when they were stored in the CHAR datatype. They aren’t the only quirky string functions: Who, for example, has been caught out by trying to use DATALENGTH to get the length of a CHAR-base string datatype?
These functions are left there purely for backward-compatibility. However, I think it is time to do a bit of spring-cleaning on these string functions. I’d draw the curtains to let the light in, throw out the dead ISNUMERIC, fix QUOTENAME so it only quotes the name if necessary, send SOUNDEX and DIFFERENCE to the museum, add the missing ‘start_location’ parameter to PATINDEX, give REPLACE the facility to use wildcards, fix SUBSTRING so that if you leave out the length parameter, it returns the whole string from the start position. Then, after a tidy-up like that, what should be added? Well, The ANSI standard TRIM and OVERLAY should be there, I suppose. I’d add a simple STRLEN function, of course. I’d probably leave it since I like the minimalist uncluttered look, unlike what you find in MySQL.
What else would you add by way of built-in string operators and functions?
Read Michael Collier’s free article to get tips and advice on Microsoft Azure Diagnostics, including using the Diagnostics Agent and how to extract the data. Read the article here.
Need to compare and sync database schemas?
Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.
13 essential SQL Server tools
In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.
Although many professions require judgement and skill, they also require the ability to do many repeated standard procedures in a consistent and methodical manner. For DBAs, standardization involves providing and following checklists, notes and instructions so that the results are predictable, correct and easy to maintain. More »
Agile Alliance is holding a conference July 28 - August 1 in Orlando, Florida. If you're interested in Agile development, whether your initiative is mature or brand new, enterprise-wide or team-centric, you will find the finest knowledge, resources and people at Agile2014. This conference is for Teams, Developers, Managers and Executives. Register while space is available. More »
Question of the Day
Today's Question (by Shiva N):
in the following code, at what point will you get an Arithmetic overflow error? Which line fails?
Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Andy Warren):
A junior colleague (JC) just received a request to export some images stored in an Image column in a SQL Server 2012 database based on a query provided:
Select top 100 FileID, ImageData from dbo.ScanData order by DateAdded desc
JC verified that the query works, and then constructed a quick SSIS package using an OLEDB Source and an Export Column transform, pasting in the above query as a SQL Command. He connected the OLEDB Source to the Export Column, then opened up the Export Column transform, selecting the ImageData column as the "Extract Column" but got stuck when he got to the "File Path Column" - there were no options in the drop down.
What's your best guess on what's going wrong?
Answer: The FileID column is the wrong data type - it must be a string type
The package is missing a required OLEDB destination is incorrect because the "end" of the data flow is writing the files to disk and that is done using Export Column.
The File Path Column is optional is incorrect. A file name must be provided (and in practice you want a complete path, not just a filename) in order to do the export.
The FileID column is the wrong data type - it must be unicode is incorrect because it will work with a VARCHAR (I tested!), and that makes The FileID column is the wrong data type - it must be a string type is the correct answer for this scenario. This does require you to make the assumption that FileID is probably a numeric of some type, in real life you'd probably get it quickly if you saw the query results, it's harder when you're just hearing/visualing the problem. I'll admit to wondering why the transform can't just implicitly convert to a string.
The File Path Column has to come after the image column in the SELECT is incorrect, but I could see a junior developer wondering if physical order mattered - it doesn't.
Export Column is the wrong transform for this task is also incorrect, it is the easiest/right way for this kind of work, and it has a corollary transform called Import Column.
I ran into this because I had to complete a very similar task as described and hit the empty dropdown issue, only then realizing I didn't have a good filename in my query. I took a minute to test converting my original INT column to VARCHAR in the select and it then worked fine. I did not do further testing to see if there were issues/successes with other data types.
SET STATISTICS XML OFF - Blocking me from using Object Explorer
- Has anyone seen activity like this? If so, where does it come from?
dd hh:mm:ss.mss: 00 00:18:32.210
Partial Restore / Upgrade
I have a large database (SQL Server 2008 R2) in which some of the filegroups are read-only, therefore the backup...
MS SQL 2012 HELP
I want to write sql query which runs in a background on cyclic basis. Basically i want to count the...
Maximum Tenancy Ref
- Hi there,
I have the following script -
[code="sql"]SELECT DISTINCT LOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
SP to compare records
- I have a table that have different groups in it, All the records that belong to one Group have the...
Compress table timelines
Here is data I am working with:
CREATE TABLE HISTORY(CUSTOMER VARCHAR(10), PLANNBR VARCHAR(10), SUBPLAN VARCHAR(3),
STARTDATE DATETIME, ENDDATE DATETIME, HISTORYMONTH VARCHAR(6))
We have an application management planning in which we must manage:
- Daily attendance of resources
- For each day ranges...
I need to Update a column with AutoNumber
I need to update a column with AutoNumber which is depending on some other column.
the output sud be like following..
Pivot help? ...or other approach?
- This is the type of data that I'm working with:
CREATE TABLE testtable
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Include date in result
- I am new to MDX queries and have an MDX query that runs fine:
[Measures].[Total Patients Waiting] on columns,
- Let me start off by admitting I know very little about SSAS. I have been using SQL Server for several...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.