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

The Voice of the DBA

Is SQL Server Mature?

This editorial was orignally published on Sept 26,. 2014. It is being republished as Steve is on holiday.

When I asked recently what compelling reasons there are, or aren't, for upgrading SQL Server, it seems cost was the issue for most people. I can understand that, especially as licensing for VMs has made it an expensive endeavor to upgrade their systems. Many, many companies have started to revisit their policies regarding upgrades and seriously evaluate the decision to undertake a version change. While I'm not sure if many companies are starting to look at keeping all instances of SQL Server for ten years, I know many are looking to keep their noncritical systems on the existing versions as long as possible.

As I read through the posts on my piece, I found this great quote from one of the commenter: "I'm a great believer in "If it ain't broke, don't fix it" and so if a system is running acceptably for it's purpose, I'll leave it alone." To a large extent, that's how I've viewed systems. If things are working, it becomes hard to justify an upgrade. In fact, I had a client that was running a SQL Server v6.5 instance in 2005, a full ten years after that version was released. Why? Mostly because the database backed a card key system that worked. While the database wasn't supported, neither was the card key system, and since an upgrade would have been close to $100,000, it wasn't worth performing. We did upgrade to a virtual server to remove hardware dependencies, but otherwise left the system alone.

That brings me to this week's question, which is one that I've asked myself for years: Is SQL Server mature?

If a version of SQL Server can handle the load placed on it, is there any good reason to upgrade. I suspect that for many applications, the base features of SQL Server are good enough. If these features have performed well for a number of versions, then why change? While I like the changes in T-SQL, especially error handling, I'm not sure those changes are worth paying tens of thousands of dollars for. I'm not sure the improvements are really justified for many applications, and I'm not sure many developers can even find the time to implement them.

More and more I think that key to continued adoption and growth of SQL Server is licensing by scale, not edition, and not by feature. Just like Azure, let me pay for the cores and RAM I need, and let me easily grow that as needed.

Steve Jones from SQLServerCentral.com

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

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Question of the Day

Today's Question (by Steve Jones):

I have a SQL Server 2017 instance. I want to verify my backup file and decide to run RESTORE VERIFYONLY on my backup file. What does this do?

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: Restore.

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

Exam Ref 70-774 Perform Cloud Data Science with Azure Machine Learning

Prepare for Microsoft Exam 70-774 and help demonstrate your real-world mastery of performing key data science activities with Azure Machine Learning services. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. Get your copy today from Amazon.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have this code to create a table and add an index.

 CREATE TABLE dbo.PurchaseOrder ( PurchaseOrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PurchaseOrderPK PRIMARY KEY , PONumber VARCHAR(100) , CustomerName VARCHAR(100) ); CREATE INDEX PurchaseOrderNDX_PO ON dbo.PurchaseOrder (PONumber) INCLUDE (CustomerName); GO

I now run this:

 ALTER TABLE dbo.PurchaseOrder DROP COLUMN CustomerName GO

What happens?

Answer: An error is returned as the column cannot be dropped.

Explanation:

If a column is included in an index, it cannot be dropped. The index must be dropped first. Whether this is a key column or an INCLUDE does not matter.

Ref: ALTER TABLE (Drop section) - click here

» Discuss this question and answer on the forums

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 2017 : SQL Server 2017 - Administration

SSDT 2017/2015 Installation failure on Windows 10 - Hi guys, I've exhausted my ideas on this one, and no amount of Googling seems to add the matter. I'm having...

SQL Server 2017 : SQL Server 2017 - Development

formatting T-SQL - Hi, I found one formatter online, but wanted to know if you use any tools to format the T-SQL code , before...

SQL help with date comaprisons - Hi, I have a scenario where i need to calculate the number of days of hold on a loan . Example is...

Finding levels in a hierarchy - i have some data which has a variable amount of hiearchy levels to it, DAX has a function called Path(),...

Assistance with DATETIME calculation when comparing to VARCHAR column (Test code included) - Hello, so I'm trying to get the difference in MINUTES between two columns. One column is a DATETIME column while the...

Get 2 decimals in my query - SELECT 'Per Category' AS Category, , , , , , , , FROM (SELECT CategoryID, AVG(UnitPrice) as UnitPrice FROM Products GROUP BY...

SQL Server 2016 : SQL Server 2016 - Administration

What does your backup process look like? - Hi, I'd be interested to hear how most of you have your backups set up. I know a lot of it...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Converting a COALESCE statement to varchar - Hello Everyone, I don't know if this is possible but I figured I would give it a try.... I have a...

SQL Server 2014 : Development - SQL Server 2014

Using an ALIAS table name in a CASE statement - Hi there everyone I'm trying to use an ALIAS table name in a CASE statement, but I'm hitting problems. Any help...

Join tables with a dynamic table and an unknown number of columns - I have a current schema that is fairly normal except one table can have multiple rows that need to pivot. I...

SQL Server 2012 : SQL 2012 - General

HELP,TSQL, - How do I write the SELECT stmt to condense multiple time spans - My attempts failed. I need a SELECT STMT that will give me 2 rows as output. The 2 rows must...

SQL Server 2012 : SQL Server 2012 - T-SQL

How to create a cartesian table? - Hello , I have this sample data: create table ##Test ( varchar (100), varchar (100), varchar...

SQL Server 2008 : SQL Server 2008 - General

login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2 - When I probe sys.dm_exec_sessions (joining with other DMVs to get active-session info) I get the login_name column to be blank...

SQL Server 2008 : SQL Server Newbies

How to set the primary key for each table - Hi all. I am a super noob with SQL server. and I mean, brand spankin' new noob still with bright sparkly...

How to get a record if any of it's column values is set to true ? - I have a table which has 20 or so boolean True/false. How do I find a record in the table...

Reporting Services : Reporting Services

how do I show sub report title on the main report? - I sure hope this makes sense: I have one main report with three sub reports. There are three links on...

Data Warehousing : Integration Services

SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile with same datatype - Please help me asap. SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile...

Project Deployment Model - Multiple Projects in a Solution - We've recently upgraded to SQL2014 and we're keen to move over to Project Deployment Model for our SSIS Packages. One...

Database Design : Design Ideas and Questions

Foreign Keys - Is there any merit in having a foreign key which references a non-clustered index in another table, over one which...

SQL Server 2005 : SS2K5 Replication

Remove article from existing publication, does it require a new snapshot? - The publisher is on one instance running SQL 2005 SP3 (9.00.4053.00). The distributor and subscriber are on a second instance...

This email has been sent to newsletter@newslettercollector.com. 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.