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

Featured Script

Use Of Cursor Vs Simple While Loop

The Voice of the DBA

Finally, Create or Alter

There are lots of reasons to upgrade to SQL Server 2016, but this is the one for me. We finally get a CREATE OR ALTER statement in T-SQL. This not only makes lots of code easier to write, it means that the ways in which you might script and schedule your future deployments will be cleaner. This is an exciting change for implementing a simpler and easier Continuous Integration/Continuous Deployment system in your organization.

It's not perfect news for a few reasons. First, this is a SQL Server 2016 addition to T-SQL only. That means until you have most of your applications have moved to SQL Server 2016 SP1+, you won't be able to use this construct. That's OK, because it will mean that at some point most of our instances will be on SQL Server 2016 SP1 or later, and much of our code will be cleaner. We won't resort to including IF statements in our deployment scripts. We won't need to create stubs of procedures and functions so our code is embedded in an ALTER script. In essence, you won't need to maintain two separate code constructs to make a change.

This isn't perfect, nor is it complete. We still don't have CREATE OR ALTER for tables. That's the place where I'd really like to get a consistent way of coding items. What I really want is a complete view of the table each time I change it. By this I mean that if I create a table like this:

 CREATE TABLE Students ( studentname VARCHAR( 200), status TINYINT );

Then I want to be able to add a column like this:

 ALTER TABLE Students ( studentname VARCHAR( 200), status TINYINT, DOB DATE );

Or alter a column like this:

 ALTER TABLE Students ( studentname VARCHAR( 200), status BIT, DOB DATE

);

Or better yet, have a CREATE OR ALTER for tables.

I know this might be asking for a lot, but I really think that we ought to get a consistent way of coding databases so that we can reduce the mistakes and make our systems easier to understand. I'm sure this may require substantial engineering, not to mention a great deal of understanding of how this would actually affect our systems when run, but it would certainly make our code cleaner.

I doubt we'll see these kinds of changes, at least not until we have an ANSI standard that encompasses them, but I would hope that as an industry we would mature and improve the way we work with databases, not remain bound by tradition and history.

Steve Jones from SQLServerCentral.com

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

Quickly search
for SQL across
your databases

Featured Contents

EzAdo Part 4

Alan Hyneman from SQLServerCentral.com

EzAdo is a very simple api built to exploit some new features of SQL 2016. It combines software, convention, and creativity to get more done in less time. More »

Quick SQL Prompt Tip – using the CDB snippet

Thre are a number of built-in snippets for SQL Prompt, and many of them are very simple, but they can greatly speed up your T-SQL coding if you learn what they are. Data Platform MVP Steve Jones shows how to make best use of the popular CDB snippet, for CREATE DATABASE statements, to save you time, but also ensure databases are created with the standard settings your organization may have, every time. More »

Who the Devil Wrote This SQL Code?

Additional Articles from SimpleTalk

The way that you format T-SQL code can affect the productivity of the people who have to subsequently maintain your work. It is never a good experience to see SQL Code, cry out “Who the devil wrote this code?”, and then realise that it was you. Grant gives some examples of bad formatting and explains why you should never check-in badly-formatted SQL code. More »

From the SQLServerCentral Blogs - SQL Server on Linux – How I think they did it!

Anthony Nocentino from SQLServerCentral Blogs

OK, so everyone wants to know how Microsoft did it…how they got SQL Server running on Linux. In this article,... More »

From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 26 – Timeline)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Timeline Power BI Custom Visual. The Timeline is a great... More »

Question of the Day

Today's Question (by Renato Buda):

 DECLARE @T1 TABLE (COL1 char(1)); DECLARE @T2 TABLE (COL1 char(1)); INSERT INTO @T1 VALUES ('A'),('B'); INSERT INTO @T2 VALUES ('A'),(null); SET ANSI_NULLS ON SELECT COL1 FROM @T1 WHERE COL1 NOT IN (SELECT COL1 FROM @T2) SELECT COL1 FROM @T1 T1 WHERE NOT EXISTS (SELECT * FROM @T2 T2 WHERE T2.COL1=T1.COL1) SELECT COL1 FROM @T1 EXCEPT SELECT COL1 FROM @T2;

Running the SQL above produces 3 resultsets. What are the 3 results?

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

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

Tabular Modeling in Microsoft SQL Server Analysis Services

With SQL Server Analysis Services 2016, Microsoft has dramatically upgraded its Tabular approach to business intelligence data modeling, making Tabular the easiest and best solution for most new projects. In this book, two world-renowned experts in Microsoft data modeling and analysis cover all you need to know to create complete BI solutions with these powerful new tools. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

With the release of SQL Server Management Studio 2016, v16.x, there has been a chance to how PowerShell should be used to manage SQL Server. What is the way that you should be importing modules for PowerShell after Aug 1, 2016?

Answer: Import-Module SqlServer

Explanation:

The correct way moving forward is Import-Module SqlServer, as this supersedes SQLPS. SQLPS has been deprecated and should no longer be used in scripting.

Ref: Import-Module - https://msdn.microsoft.com/powershell/reference/5.1/microsoft.powershell.core/Import-Module

SQL PowerShell: July 2016 Update - https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

» Discuss this question and answer on the forums

Featured Script

Use Of Cursor Vs Simple While Loop

S M from SQLServerCentral.com

Copy and Paste both the scripts and run it in master database.

I prefer using WHILE Loop as a better programming practice always suggest writing scripts with Less Line Of Codes (if possible) and also use of CURSOR is reducing now due to higher consumption of MEMORY and if someone misses to include Deallocate Cursor it can still grab the memory and wont release.

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 2016 : SQL Server 2016 - Development and T-SQL

possible combinations for all rows in table - I have a table having three column Name Value1 Value2 Value3 A 1 2 3 B 4 5 1 C 2 3 1 I want...

Recursion to iterate function - Some one help me to convert the recursive method to iterative function. USE [db] GO /****** Object: StoredProcedure [dbo].[proc_geneatecombkachha] Script Date: 11/27/2016...

how to retrieve numbers from a string - I have below kind of strings: 21-12-ABCD 23-1-hdf 19-1345-dsnf i want a result like below for each string: 21 12 23 1 19 1345 How can i extract...

SQL Server 2014 : Administration - SQL Server 2014

Cant create database (.bak) file.. - we have a system for banking with sql 2014 database.. i really dont know what really happened,, but suddenly we...

User unable to execute OpenRowset without Error - On our old server these style statements executed as sa from a website. [code="sql"] execute as user = 'NewUser' select * from OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\PathDocs\test.xls;IMEX=1;HRD=Yes',...

Resource semaphore Query Compile and Maxdop Setting SQL 2014 - Hello, We have migrated SQL standard edition from 2008R2 to SQL 2014. After the migration we are seeing huge amount of...

Number of logical reads very different between 2 supposed identical environments - Hello, What parameters can have an impact on "number of logical reads" ? UAT and PROD environments are supposed to be the...

SQL Server 2014 : Development - SQL Server 2014

No data in one column of a view (NULL value instead - Hello, I have a table [DataXYZ].[dbo].[DataXYZ_GeoLoc_Input] with, as you can see, data for both "x" and "y" columns for year 2015: [img]http://www.cjoint.com/data/FKxqcfsonpU_XYZ-input.png[/img] I...

Select continual date ranges from a list that overlaps - Hi There, need some help. I have list of prices and the dates they are valid from and to. For Example: Date...

SQL Server 2012 : SQL Server 2012 - T-SQL

A scenario where SQL Server doesn't seem to help - Hi We have a scenario, where busniess users are presented with a screen with 20+ fields. They can choose any number...

Looking for a smarter script to do this query - I am stuck on a very repetitive query now, I wonder if anyone can help me on figuring out a...

Can't get sp_create_plan_guide to work - I have the following SQL generated by a third party application that is using a wrong index and create undue...

SQL Server 2008 : SQL Server 2008 - General

DDL event for job changes - Hi, I have a DDL Audit trigger which traces all the following events: [code="sql"] FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_TABLE,...

OpenRowset for CSV File - So earlier, I am on the verge of creating a code that would import a CSV file that is machine...

SSIS Excel import error - The situation is: Server A: runs alle the ssis jobs. Excel is located on a fileserver. Connection string Excel: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Fileserver\somewehre\Excelfile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES"; The...

Reporting Services : Reporting Services 2005 Development

Select All (multi values) and Error - I have created a Report and attached it to my database. I have 4 parameters in the report, all of...

Programming : Connecting

Checklist to connect cassandra - Hi Friends, I have been assigned to a new project where data load s gonna happen from oracle to cassandra through...

Data Warehousing : Integration Services

Foreach ADO.NET Schema Rowset Enumerator won't loop through Excel Workbook - Hello all, I've searched the forums and internet for this problem all to no avail. Hopefully one of you can shed...

SQL Server 2005 : Business Intelligence

How do I remove zombie Business Intelligence Development Studio installed by SQL Server Express? - I am at a client where they install SQL Server Express 2008 R2 from a disk that apparently is capable...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Float vs Decimal? - Does anyone have any opinions concerning the performance of Float vs decimal data types? For example I have a table that...

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.