SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Is It Time To Clear Out Those Quirky Functions?

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?

Phil Factor from SQLServerCentral.com

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

ADVERTISEMENT
Just Azure

Getting started with Microsoft Azure Diagnostics?

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.

SQL Compare

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.

SQL Toolbelt

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.

Featured Contents

 

Instant File Initialization

Sujeet Singh from SQLServerCentral.com

This article describes a way to speed up various file operations performed by SQL Server. More »


 

The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work

Additional Articles from SimpleTalk

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 2014, Florida

Press Release

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?
DECLARE @intA INT = 2147483647, @intB INT = -2147483648, @intC INT --1
SELECT @intC = @intA + @intB --2
SELECT @intC --3
SELECT @intC = cast(@intA as bigint) + 1 + @intB --4
SELECT @intC --5
SELECT @intC = @intA + 1 + @intB --6
SELECT @intC --7

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Professional Microsoft SQL Server 2014 Administration

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

Yesterday's Question (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

Explanation:

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.

Notes:

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.


» Discuss this question and answer on the forums

Featured Script

Find SQL Server Install Date

Patrick Akhamie from SQLServerCentral.com

This script is compatible for both SQL 2000 and SQL 2005.
This is handy script which could be used to find the SQL Instance install date.
For SQL 2005 it lists out the individual install date like SQL Server, Integration Services.
Note : if there exists problem copying the code directly to Query Analyzer or SSMS.
Please copy it to wordpad/Word, from there copy to your query window.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Administration - SQL Server 2014

How did you set up Security Groups and Users - I've been tasked to come up with a security group model. We don't want to keep adding individuals to the...

AG-Read only replicas - We are planning to have read only replicas in our environment. Current setup holds one pair of synchronous nodes in...

Rebuild Indexes after upgrade to 2014? - We are doing a side by side upgrade from SQL Server 2008 to 2014 by setting up log shipping to...


SQL Server 2014 : Development - SQL Server 2014

RDP into server - queries run slow - Hello, Environment: I have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL...

sp_send_dbmail truncates when attaching query results. - Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated....


SQL Server 2012 : SQL 2012 - General

Sql Statement in SSIS package is all of a sudden making the tempdb grow exponentially - So we have a very large SSIS package that is inserting records from one table in a database to the...

SSMS forgets settings - hi, did anybody come upon this: SMSS installed on my client forgets all the settings I entered under tools / options. maybe it...

How to cheat DBCC DROPCLEANBUFFERS for Performance testing - Hi, I'm tuning big query so I'm in need to check clean execution time, but I don't have authority to run...

Dragon Boyhood Planet of The Apes - Richard Linklater delivered a near masterpiece which was warmly received by a very enthusiastic audience in the Texas premiere of...

using count(*) and case in on statement - Hi, I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But...

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 session_id: 79 login_name: me\me wait_info: (6ms)IO_COMPLETION CPU: 646,180 tempdb_allocations: 2,088 tempdb_current: 0 blocking_session_id: NULL reads: 171,237,095 writes 1,439,934 physical_reads: 637,080 used_memory: 2 status: rollback Thanks

Partial Restore / Upgrade - Hi, 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 - Hi, I want to write sql query which runs in a background on cyclic basis. Basically i want to count the...

Is it Possible to setup Snapshot Replication on a High Availability replica? - We have a vendor that is exposing our database via a High Availability replica. They are geographically far away from...

Need a SQL Server AUDITING tool - Need recommendations on a good, easy to use --- SQL Server AUDITing tool... to capture many facets of who did what,...

Mapped Credential is not used in any t-sql query - Hi, Due to a previous (mis)configuration, i need to grant read\write permission on a share from a MSSQL DB User. The SQL...

minimal logging insert statement on non clustered index table - I understand that minimal logging can occur on a non clustered indexed heap as long as http://technet.microsoft.com/en-us/library/ms190422(v=sql.105).aspx *not replicated *tablock is used *table...

Master Package failing while scheduling as a job - i have a master package which executes 10 child packages, all the packages sharing the same configuration file. i have...

BIDS Helper 1.6 - not working in VS 2010 Shell - (not sure if this is the appropriate forum...) I've been using BIDS Helper (BH) in VS 2008, but I can't seem...

Approximate hours per month to monitor and maintain the health of MSSQL databases? - Based on the description below on average how many hours a month would it take to monitor and maintain the...

CU Failed however SQL Upgraded - All, I was updating (CU) a node of an AG, this was an Asnc copy in a different subnet than my...

How To Give SQL Agent Permission To Access Oracle 11g db? - Hi, What is the best way to give SQL Agent permission to run a job that accesses an Oracle db? I have...

Extended Events; xml_deadlock_report; key cannot be null - Hi there, Wondering if anyone can offer some insight. We have (running SQL 2012 Std) extended events capturing deadlock information and...

User Defined Table Type SCRIPT TO... error - Hi All, We are in the process of rolling out our new security strategy, and one of the developers testing the...


SQL Server 2012 : SQL Server 2012 - T-SQL

storing FOR XML EXPLICIT results to tmptable - SQL 2012R2 Std I have a sql statement to retrieve data in XML format. I have to use EXPLICIT because I...

Can't get rowcount to return to calling stored procedure - SQL Server 2012 Standard SP 1. Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure...

Odd issues - alter procedure not changing procedure - I'm trying to change references to a fully qualified object to a semi qualified in a stored procedure. I execute...

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' ,PLA.address5 AS...

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)) INSERT INTO...

Optimize SQL - Hi, We have an application management planning in which we must manage: First   - Daily attendance of resources   - For each day ranges...


SQL Server 2008 : SQL Server 2008 - General

GL transaction appeared after SQL reboot. - Hi all experts, Finance posted GL transaction in finance application, it finished with OK status. However it did not show up...

Citrus also contains - [url=http://freerice.com/node/2913680/]Netherlands vs Argentina Live Stream[/url]

Unable to start execution of step 2 (reason: Error authenticating proxy ABC\ashif, system error: Logon failure: unknown user name or bad password. - Hi Experts, We fail overed one of our database server to another node earlier today(for testing purpose), Since then the replication...

but on this one - [url=http://freerice.com/content-group/argentina-vs-netherlands-live-stream-semifinal-online-fifa-world-cup-2014-hd]Argentina vs Netherlands Live Stream[/url]

Index Fragmentation? - I'll try to be as concise as possible and lead you down the path i've traveled :). I'm in the process...

Parsing strings from a field value - Hi, I created an ETL process using C# (SSIS was not available) and ran into some issues with one of the...

How to Consume Return Values From Table Valued Function - Hi All, I have a table valued function that splits a string based on a delimiter and produces a set of...

Where does my data comes from? - Hi Folks, Can someone help me with a script or how to know where my data in a table comes from....

Unused space in a table - Hello all, Following a large cleanse of data I now have a lot of unused space in a table. The problem...

Restore verifyon - HI guys, I'm currently running a backup job and it's still not finished yet but I need to restart the service....

How to get recently updated tables with total row count - How to get recently updated tables with total row count in sql server 2008?

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('. - Hi guys, I'm running below script to get the list of fragmented indexes but I'm getting this error: Msg 102, Level 15,...

OpenRowset Issue - Hi, I am trying to access remote db server's stored procedure and am using openrowset for that. i am aware of...

DB Mirroring Performance Statistics - Hi, I am trying to find some performance statistics for database mirroring which I have set up on my SQL Server...

BCP - I have huge table around 5 million rows in source table (i.e Sybase server) .Need to copy 5 million rows...

SSIS email-query issue - Hi guys, First of all apologies for the vague title. Hopefully the next bit will be clearer! I basically been asked to...

What are the options for splitting/reducing large mdf files? - I have a database with 2 mdf files. One file is 750gb and the other file is 112gb. There are...

Cannot open New SSIS Project in SQL Server 2008 R2 - I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer...


SQL Server 2008 : T-SQL (SS2K8)

Not sure how to write this query without cursors and/or dynamic sql - I have a couple of tables that look like this: [code="sql"] CREATE TABLE Colors ( Id INT NOT NULL, ColorName VARCHAR(25) ) CREATE TABLE Cars ( ID INT NOT...

Strange behaviour - Can someone explain why only the last 2 versions of this query fail (in the cross applied function)? I'm mystified....

Resources for tuning large workloads - Hi, It seems that a lot of the performance tuning books and articles I read assume up front that data is...

I need to Update a column with AutoNumber - Hi, I need to update a column with AutoNumber which is depending on some other column. the output sud be like following.. Obj1 Obj2 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0002 ijk-001 0002 ijk-001 0002 ijk-001 0002 ijk-001 i...


SQL Server 2008 : SQL Server Newbies

Trying to parameterise code executed by sp_executesql - I'm trying to parameterise code executed by sp_executesql. It's not a stored procedure, I've just got a number of similar...

Trying to get Null values while also using MAX - I'm trying to write a view that pulls a column with Null values, but also uses the MAX function to...

Help with Max Date/Time and IsNull - I'm trying to write a view that pulls a column with Null values, but also uses the MAX function to...

Pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence - I am using the following stored procedure which is then used in a tableadapter. My problem is that although 90%+...


SQL Server 2008 : SQL Server 2008 High Availability

Rebuild Index on Published Tables PK? - On the publisher database, can you ALTER INDEX REBUILD (ONLINE=ON) the primary key of a replicated table or must you...

In Replication why we should not give write permission on subscribers - Hi friends, In our environment we have uni directional transactional replication setup. if we give read write access to the user...

Question about initial backup for Log Shipping - Hi All, I'm in the process of setting up log shipping for several SQL 2008 DB's. Everything works fine using...


SQL Server 2008 : SQL Server 2008 Administration

syspolicy_purge_history job failed at step 3 - Hello, In one of my sql 2008 sp1 standalone instance, syspolicy_purge_history job is fails at step 3 Erase Phantom System Health...

Cluster Failover Cause - Hi Experts, How to find the root cause of cluster failover?? Where to start ? what all logs to check? Is it possible...

migration sql databaase - is it possible to migrate 7TB database within 30 minutes ? if yes, what is the best way ? Regards Jernas

Doing healthcheck on 40+ SQL Servers centrally. - Hi, We have almost 40 SQL Servers and we need to send an healthcheck report on all the servers everyday. Currently the...

Automate Data Compression - I would like to automate the compression job for every week end by filtering on newly created tables/indexes with more...

An existing connection was forcibly closed by the remote host. - We are going live tomorrow in a completely new environment for our PeopleSoft HR and Finance systems. We are getting...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Querying SQL Server over WAN - Is there any command to show the network latency of querying the SQL Server from SSMS over WAN versus direct...

Reduction Of Performance after migration SQL 2000 to SQL 2008 - Hello, we just migrated a SQL 2000 Database to MS SQL 2008 R2 with 80% compatibility. Unfortunately, this produces massive lost of...

Performance issue with view - I have a view which has joins with multiple tables and have some complex logic. I am facing weird performance...


SQL Server 2005 : Administering

Can ever selet count(*) be faster that select * - I have come across a case where "select count(*) " is way slower than " select * from my view order by id...

Sql Server 2005 query performance on large table - I have a SQL Server 2005 database that is 6 years long the transaction tables are already big in gigabytes...

Create linked server to Avaya CMS database - Hi! I'm trying to create a linked server with an Avaya CMS database. I've created a DSN using Openlink ODBC driver and...

Disabling FileStream - Hello, We have a new sharepoint 2010 farm setup and we were playing with fliestream. We decided not to use it...


SQL Server 2005 : Development

Entity Framework - Adhoc queries... - Are any of ya'll using the Entity Framework to develop? I am told that it generates the SQL statements. I am...


SQL Server 2005 : SQL Server 2005 General Discussion

Return row values where exists in table, "not scanned" where missed scan - Hi SSC, I have two tables - Production and Inspection. I want to return all production records because production will be 100%...

Pivot help? ...or other approach? - This is the type of data that I'm working with: [code] USE TestDB GO CREATE TABLE testtable ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CompanyNumber nvarchar(50), UserNumber...


Reporting Services : Reporting Services

ssrs 2008 want repeating column headers on Word document export - In an SSRS 2008 report that I modifed, I changed the report to do the following: a. Display repeating column headers...

Add / Edit Subscription takes a long time (2 plus Minutes) to open on One Particular Report - SQL Server Version: 2008 R2 Release: 10.50.4000 We are running SSRS in Sharepoint Integrated mode (Using Sharepoing 2013 I believe). We have...

ssrs parameter value use can select from - In an new SSRS 2008 the endyear parameter has, 1. the following is the default values sql: Select case when MONTH (GETDATE())...

New to SSRS and how most people implement (on its' own server or with db engine) - Hi I have implemented SSRS on one of our test servers that also has the database engine installed. This is the...


Reporting Services : Reporting Services 2008 Development

Blank 1st page - How can I get the 1st page Blank in my report?

Reports Running Faster in IE8 than BIDS - Hi, I am just wondering how this works as it is not really a problem. When I run a report deployed to...


Reporting Services : SSRS 2012

Export Report to SSMS - I would like to export a report to a table in SSMS, ultimately on a scheduled basis using Sql Server...


Programming : XML

Adding a Header Row to a SSIS package exporting out as XML - I've been asked to create an SSIS package to export data out as and XML file. I've got the xml file...


Data Warehousing : Integration Services

Find \ Replace within a .csv - I would like to execute a simple find and replace of a single character within the contents of a .csv...

Execute Process Task: how can I make the task's argument and executable fields use the expressions I built for them?? - I've got a series of Execute Process Tasks whose arguments and executable fields I would like to populate dynamically, using...

Random Data is inserted in SSIS - Hi I m using SSIS to transform the data from csv file(which has fixed width as delimiter) to database. I m...

Sent Mail task - Hi All, I am new to SSIS, I have a 39 tables in source and same tables in destination, The source...


Data Warehousing : Analysis Services

SSAS Tabular Data coversion issue - Hi, recently our orgnization decided to go with MSBI. We started working on the SSAS Tabular model. I am trying to import...

Include date in result - I am new to MDX queries and have an MDX query that runs fine: Select [Measures].[Total Patients Waiting] on columns, CrossJoin({[ReportDate].[ReportDate].[Report Date].&[20140401]}, {[Facility].[Facility].ALLMEMBERS}) on...

SSAS Overview - Let me start off by admitting I know very little about SSAS. I have been using SQL Server for several...

2008r2 Cube not linking two views on key - I am building a cube in 2008r2 analysis services (BIDS) which requires me to link two views. The first view...


Data Warehousing : Data Mining

Sigma Value of each tree node in Decision Tree algorithm of SSAS - Hello All, I am trying to get the sigma value of each tree node in decision tree. Please let me know...


SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQL Server 7,2000 : Administration

Error 14258: Cannot perform this operation while SQLServerAgent is starting. Try again later. - Suddenly the automated database backup using DB Maintenance Plan has stopped working. When I am going to the JObs section under...


SQL Server 7,2000 : General

hyderabad dba institutes - Dear Friends, i'm working for a development company, as Junior DBA. for oracle concepts,Wilshire and SQL * are very famous in hyderabad. is there...


Career : Certification

How to break into data warehouse management - Hi All, I'm hoping I can get some valuable input from people in the field. My current and past jobs have...


Career : Employers and Employees

What if interviewer is wrong? - During a job interview yesterday the DBA interviewing me was incredulous that at a former employer were running DBCC CHECKDB...


Testing Center : SQL Server Security Skills

.... - [url=https://exploreb2b.com/articles/watch-argentina-vs-netherlands-live-stream-fifa-world-cup-football-2014-semifinal-online]https://exploreb2b.com/articles/watch-argentina-vs-netherlands-live-stream-fifa-world-cup-football-2014-semifinal-online[/url] [url=http://www.cross.tv/events/eventDetails/2907/netherlands_vs._argentina_live_stream_2014wc_watch]http://www.cross.tv/events/eventDetails/2907/

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
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.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com