CREATE OR ALTER

One of the fundamentals that we learnt when learning SQL, that we use CREATE to create a database object, and ALTER if we needed to change its structure. It was simple and straightforward.

But it was too much of a problem writing a deployment script, where you had to check if the object existed, and then create or alter it based on the result. Or you could drop and re-create the object but that would make you lose all the permissions that have been set on the object. In short it tended to get a little messy. Or of course you could try a clever idea like this one. Putting it in as dynamic SQL was a clever alternative too,  but then you had difficult-to-read and difficult-to-maintain scripts.

SQL Server 2016’s Service Pack 1 now gives us the CREATE [OR ALTER] statement, with which you could create a database object or modify in just one go. It’s a feature been asked for, for quite a long time, and it’s finally here.

You can use CREATE OR ALTER on the following types of objects: Stored procedures, Functions, Views and Triggers. It would be nice to have this on tables too, but then we have the whole issue of tables being populated, and the effect that it would have on dependent tables would be all too complicated.

Here is a piece of code comparing usage:

THEN:

IF (SELECT OBJECT_ID(‘TestProcedure’)) IS NULL
EXECUTE(‘CREATE PROCEDURE TestProcedure AS PRINT ”Test”’)
GO

ALTER PROCEDURE TestProcedure
AS
BEGIN

PRINT ‘Here”s the real code I wanted to write’
END

NOW:

CREATE OR ALTER PROCEDURE TestProcedure
AS
BEGIN
PRINT ‘Here”s the real code I wanted to write’
END

 

Accessing a Remote SQL Server instance via SQL Server Management Studio

When you work with remote SQL Server instances, such as those on a standalone machine, or one on a separate domain, and you only have Windows Authentication to access the remote machine, how would you do it?

• If the target instance had SQL Server Authentication, you need only supply the user name and password of SQL login, and you are in.
• If the target is on the same domain as the user you have logged on to your local machine with, then it’s just a matter of the login having access on the server, and you could just Windows Authentication it.

However, in this case, you are on your own domain/standalone machine trying to access another domain/standalone machine remotely. Your immediate impulse would be to run SQL Server Management Studio as another user, and supply the remote machine’s credentials. Problem is, your machine/domain is not going to know the domain’s/remote machine’s credentials:

Hence, in order for you to open Management Studio using the target machine’s credentials, you need to do things a little differently: Open the command prompt as an administrator, and run Management Studio under the credentials of the target machine:

This will open Management Studio under the supplied remote machine’s credentials. Note that we use the RUNAS command with the NETONLY flag. Warning: you need to ensure that the password is entered correctly, since Management Studio will open regardless of you supplying the correct password.

Logging in to Management Studio will look like this:

and once logged in, the instance connected to, would look like this:

You are now connected to a remote SQL Server instance from your local machine, with credentials of the remote instance.