SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

Not Useless Features

Some time ago I noticed Adam Machanic had written a post titled The SQL Hall of Shame. In it, Adam notes there are features that Microsoft will introduce in SQL Server and then let die. These are the useless features that aren't widely used, or even useful for many projects. You can read his list, and agree or disagree.

I think there certainly are some features that were silly experiments and I wish they had been introduced as experimental, or beta, features early, allowing users to give feedback on whether they're useful and where they need refinement. I certainly think too many resources were initially wasted on items like MDW and Query Notifications, and far too little investment later. These days I think we'll see more early releases of items in Azure where users can experiment and provide feedback to help Microsoft decide if more investment is needed. At least, that's my hope.

Today I'm wondering if you think there are features in SQL Server that do need more attention. Those features you use, but appear forgotten and you'd hope they are improved in the next version.

For me, I certainly wish replication would get more attention, tooling, and resilience. I'm not sure if this needs an overhaul, like SQL Server 2005 was, or there just needs to be some reworking of the code, but in today's distributed world, we need a more reliable way to hook up portions of databases and move data around. Not ETL, not Availabilty Groups, a better replication architecture and implementation.

There might be other features you use or want, but let me know today. What's a not useless feature that you want improved.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Clone

SQL Clone: Now supporting databases up to 64TB

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free

SQL Compare

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

Featured Contents


Bones of SQL - Practical Calendar Queries

Bob Hovious from SQLServerCentral.com

After the first article created a Calendar table, learn how to use this in a practical sense with some common queries. More »


SQL Server Database Provisioning: Getting Started with SQL Clone

This article serves as a ‘first look’ at cloning databases with SQL Clone. It will review some of the database provisioning challenges that we seek to overcome, explain briefly how SQL Clone works, and then walk through a simple example of cloning a database. More »


Even More SQL Server Features that Time Forgot

Additional Articles from SimpleTalk

SQL Server works well, and Microsoft does everything it can to keep it relevant and competitive: As with everything in real life, it doesn't don't always get it completely right, and Rob Sheldon continues his quest through the jungle of past features to rediscover and explore the ones that time forgot. Here, he comes across Lightweight Pooling, XML Indexes, Stretch Databases, SQL Variants, Transaction Savepoints and In-Memory OLTP. More »


From the SQLServerCentral Blogs - Microsoft Professional Program in Data Science – The Finish Line

Koen Verbeeck from SQLServerCentral Blogs

In October 2017, I completed the capstone project of the Microsoft Professional Program for Data Science. I’ve blogged about this... More »


From the SQLServerCentral Blogs - How to Calculate Running Totals in SQL Server

Guy Glantser from SQLServerCentral Blogs

  In show #84 of the SQL Server Radio Hebrew Edition, Matan and I talked about how to calculate running totals in SQL... More »

Question of the Day

Today's Question (by Evgeny Garaev):

How many indexes can be created on a Memory-Optimized Table if you use SQL Server 2016?

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: Memory-Optimized Tables.

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


Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I am looking to add some documentation to my database by using extended properties. I want to add a property with these parameters:

  • Property Name: IsExternalAPI
  • Property Value: 1

to the dbo.SalesOrderHeader table. I decide to run sp_addextendedproperty with this code:

EXEC sys.sp_addextendedproperty @name = 'IsExternalAPI' ,
                                @value = '1' ,
                                @level0type = 'Table' ,
                                @level0name = 'SalesOrderHeader'; 

This doesn't work. Why not?

Answer: The level0type is invalid


The problem with this code is that the level0type is invalid. Schema is the correct type here. Others are: assembly, contract, Event notification, filegroup, message type, partition, function, partition scheme, remote service binding, route, service, user, trigger, type, plan guide, and NULL.

Once this is fixed, the level1 values would be what is shown for the level0 values. The correct code is:

EXEC sys.sp_addextendedproperty @name = 'IsExternalAPI' ,
                                @value = '1' ,
                                @level0type = 'Schema' ,
                                @level0name = 'dbo',
                                @level1type = 'Table' ,
                                @level1name = 'SalesOrderHeader'; 

Ref: sp_addextendedproperty - 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

Why dbid is 1 for master, 2 for tempdb, 3 for model and 4 for msdb? - Hi Is there any reason why the below DBid order is maintained? Master - 1 TempDB - 2 Model - 3 MSDB - 4

SQL Server 2016 : SQL Server 2016 - Administration

SSIS Database with lots of space - Dear all, I have a SSIS DB in my SQL Server. This database currently uses more than 10 GB of data...

Slow query - client or server issue - If seeing a client make a regular query that uses the same plan but completes in radically different times on...

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

SSRS 2016 History Snapshot - Hello, I am building out SSRS to replace our Business Objects server. In BO, every time a subscription is run, it...

What is the best ways of storing unstructured data in sql server 2016, except filestream. - What is the best ways of storing  unstructured data in sql server 2016, except filestream.

SQL Server 2014 : Administration - SQL Server 2014

SQL Replication - Is there an easy way to find out the following Get Transactional replication details (publisher name, Publication, table, sp_names, filters)

Find all triggers that contain FACTS in trigger name - i cant seem to write a query to find all triggers in a database that contain the word FACTS in...

SQLServerAgent Error: The process cannot access the file because it is being used by another process - I have created the following as an automated job to run at midnight on every database server I manage:

SQL Server 2014 : Development - SQL Server 2014

Query Optimization Advice - Hi All, So I'm faced with the need to optimize a pretty hefty query, as it's beentaking extremely long to run....

Resolving many to many linkage - USE GO /****** Object: Table .  Script Date: 11/29/2017 9:52:07 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE .(  &nbs

SQL Server 2012 : SQL Server 2012 - T-SQL

Executing Stored Procedure against a linked server - Is it possible to run a stored procedure against Server B if the stored procedure only exists on Server  A? I...

Strange Month number value in table of Dates - I've been tasked with writing a script to populate a Dates table.  This contains a row for every date in...

Speed up query execution. Can we add an index or rewrite the query - I've been working on this query handed to me by a developer who wanted to reduce the query execution time...

Force execution plan in a view - I'm having an issue right now. We removed an identity column that is mostly irrelevant to a table. However, this...

SQL Server 2008 : SQL Server 2008 - General

Add Column to Staging table - Hi, The code below used to work fine. It is part of a stored procedure which bulk inserts data from a...

Difficulty with SQL UPDATE Query - Greetings... Hair ~ Gone I cannot figure this out...I'm actually working in a VBE trying to write an UPDATE query loop based on...

SQL Server 2008 : SQL Server Newbies

'Debugging Query' for a long time... - I am trying to run a simple select query in SQL Server 2008 that I have run many, many times...

Data Warehousing : Integration Services

Need to export excel sheet with SPECIAL formats - Hi, I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it...

SQLServerCentral.com : Articles Requested

Basics of Statistics (data distribution) - I would like a basic article to explain how statistics are captured for SQL Server tables and updated (auto/manual). A...

SQL Server 2005 : Administering

SQLServerAgent could not be started (reason: Error creating a new session). - I can't start the SQL Server Agent Service on a new installation. During the install I chose not to start the...

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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com