sp_rename to change schema?

words: 519

Reading time: ~2.5 minutes

The Set Up:

Recently I was asked by a developer whether they could use sp_rename to change the schema of a table.

I said no but I realised that I don’t know for sure as I’ve¬†never tried it this way.

Granted I have never needed to when we have such a descriptive command like ALTER SCHEMA.

So I tested to see if sp_rename could change the schema of a table and thought I would share my results.

Here they are:


SP_RENAME:

Script 1:


SELECT [Schema Name] = SCHEMA_NAME([schema_id]),
 [Table Name] = [name]
 FROM sys.tables
 WHERE [name] = N'Alphanumeric';

original_table

Now taking a look at the documentation for “sp_rename”, turns out all we need is

  1. the current name,
  2. the new name we want to call it, and
  3. an optional object type (which I’ll include because I like typing).

So with that, it seems simple to run the following…

Script 2:


 EXEC sp_rename
 @objname = N'dbo.Alphanumeric',
 @newname = N'deleteable.Alphanumeric',
 @objtype = 'OBJECT';

sp_rename
That error message! I enjoy that error message ūüôā

So now all there is left to do is check if it worked, so we run our first script again and we get???:

original_table_changed
eh…what?

I repeat the above:¬†eh…what???

Where did my table go???

Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.

However, on a whim, I changed my first query to use a LIKE:

Script 3:

sp_rename_found
ehh..WHAT??

So I haven’t changed the schema? I’ve renamed it to be dbo.deletable.Alphanumeric?

Is that even query-able?


SELECT * FROM dbo.deleteable.Alphanumeric; -- Fails!

SELECT * FROM [dbo].[deleteable.Alphanumeric]; -- Works!

Okay, let’s just change it back quickly and pretend it never happened:

Script 4:


EXEC sp_rename
@objname = N'deletable.Alphanumeric',
@newname = N'Alphanumeric',
@objtype = 'OBJECT';

error_message_02
You’re killing me!!

Okay, okay maybe it’s like the SELECT statement and I need to wrap it in square brackets?

Script 5:


EXEC sp_rename
@objname = N'[deletable.Alphanumeric]',
@newname = N'[Alphanumeric]',
@objtype = 'OBJECT';

error_message_03
I count that as a metaphorical middle finger to me…

Maybe we’re being too specific?

Script 6:

EXEC sp_rename '[deleteable.Alphanumeric]', 'Alphanumeric';
finally_works
Hopes are up…

A quick run of our first script to confirm?

works_again
…and¬†we’re back to normal!!!

Now, as to why that syntax works but the others don’t…I have no idea.

I will try and figure that out (fodder for another blog post ūüôā ) but I’m going to need a few more coffees before I go touch that again.


ALTER SCHEMA:

It’s a bit sad though… all that hassle for something that didn’t even work in the end?

Now, lets check out the documentation of “ALTER SCHEMA”.

  1. where we are changing it to, and
  2. what we’re changing.

Seems simple, but then so did sp_rename and that burnt me.

Script 7:

alter_schema
Give my “deleteable” the “dbo.Alphanumeric” object!

A quick check to see if it actually¬†worked as I’m not swayed anymore just by a lack of warnings:

actually_works
Yes!

Sum it up:

If I didn’t know the answer at the start, I definitely do now.

Can you change the schema of an object by using “sp_rename”?

Hell no.

Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.

 

Create View Permissions

CREATE VIEW Permission

I’m mainly writing this as documentation for myself as, in the end, this is the original purpose of this blog, to document SQL Server and new aspects of it that I learn and try.

Personal Template

I’ve always had a little block with regard to this as, for database permissions, I always followed a template in my head:

USE <database>
<Give/Take away> <what permission> <On What> <To Whom>

It’s The Little Things That Trip You

With CREATE¬†permissions this isn’t the case; there is a piece of the above template that isn’t needed, and it’s quite easy to see why when I¬†sat down and thought¬†about it.

Specifically, it’s this bit:

<On What>

I’m¬†granting CREATE permissions; since I¬†haven’t created anything, I¬†can’t grant the permission on anything.
So for CREATE permission, I have to modify my template a bit:

USE <database>
<Give/Take away> <what permission> <To Whom>

If I use this now as a template to a GRANT CREATE VIEW, it will work:

USE [localTesting];
GRANT CREATE VIEW TO [testUser];

And it works!

[SQL Server] How Well Do You know Your sys Tables? Test yourself

I may have been too reliant on Intellisense…

Intro:

Reading Time: ~1 minutes

Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.

select name, is_not_trusted from sys.check_constraints;
select name, is_not_trusted from sys.foreign_keys;

In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.

However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.

All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.

This, in turn, got me wondering; how well do you really know your sys tables?

If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?

Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.

Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?

Test yourself:

The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.

In the dialog box,¬†Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:

  • Auto list members
  • Parameter information

If you uncheck these two checkboxes, your intellisense is gone!

TransactSQLGeneral

Try it out, even for 5-10 minutes.

Hopefully, with your Intellisense gone, your sense will remain.

Semi-Unique Constraints.

When full uniqueness is a bit too much.

What are Unique Constraints? How can we enforce them and finally, is there anyway that we can have modified uniqueness…can we have Semi Unique Constraints?

Unique constraints.

There are many use cases that require that a column in a table only have unique values. The main example of this is a table’s PRIMARY KEY¬†which, needing to uniquely identify a record, needs to be unique by default.

In fact, SQL Server enforces this uniqueness by creating a unique index on whatever column(s) the Primary key is defined on.


-- Drop Table if Exists.
IF OBJECT_ID(N'dbo.SemiUniqueColumn', N'U') IS NOT NULL
DROP TABLE dbo.SemiUniqueColumn;

-- Create Table.
CREATE TABLE dbo.SemiUniqueColumn
(
ID int IDENTITY(1, 1),
UniqueColumn int,
SemiUniqueColumn int,
CONSTRAINT [PK_SemiUniqueColumn] PRIMARY KEY CLUSTERED (ID)
);

-- Check Primary Key Exists.
EXEC sp_helpindex N'dbo.SemiUniqueColumn';

SemiUniqueConstraints_PK
Index Description : unique.

Unique But Not Primary

Primary keys are not the only options that can be unique, SQL Server recognizes this, and so there is the option of marking other columns as unique as well. Whether this be actioned by a UNIQUE CONSTRAINT¬†or a UNIQUE INDEX¬†is user’s perogative.

I’ll be creating this using a UNIQUE INDEX¬†but¬†for the purpose¬†of completeness, the syntax for UNIQUE CONSTRAINTs¬†is


ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column(s))

Now say we are forced to ensure that the column UniqueColumn is unique, so we create a UNIQUE INDEX on this.

-- Ensure UniqueColumn is Unique by Creating Unique Index on it.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_UniqueColumn] ON dbo.SemiUniqueColumn (UniqueColumn);
SemiUniqueConstraints_UIX
Index Description : unique.

We insert values into this table and, as long as the uniqueness of these rows are satisfied, we’re going to have a good time.


-- Insert Data.
WITH RecordSet (SeqNum) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn) SELECT SeqNum FROM RecordSet ORDER BY SeqNum DESC;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_TblSelect.png
All Unique = All Good.

When Is A NULL Not a NULL?

Short answer, when you try and compare it to another one.

Now you may think that I’ve lost the plot and gone off on a tangent but bear with me here a second and I’ll explain.

A NULL is UNKNOWN and an unknown value should not equal another unknown value. What happens if your first unknown turns out to be the number 1 and the second unknown is the number 2? 1 <> 2 so comparing them then would be pretty foolish.

If you ever look at some generated script and see at the very top of the script, hiding amongst the XACT_ABORTs¬†and the NOCOUNTs¬†is another option called ANSI_NULLs¬†(although not anymore as it’s on by default and should be left that way IMHO).
This ensures that NULLs are treated as unknown and cannot be compared to another unknown except for some specific cases (GROUP BY, UNION ALL, INTERSECT, EXCEPT, etc)

What Does This Mean For Us?

Good question! Remember our Unique Index on UniqueColumn? What happens if we run the following?


-- Insert NULL into UniqueColumn.
INSERT INTO dbo.SemiUniqueColumn
( UniqueColumn )
VALUES
( NULL );

It inserts no problem, going all the way from 7442, 7441, … 2, 1, NULL. What about if we run it again?

SemiUniqueConstraints_UniqueColumn2ndNull
An Index is apparently exempt from NULL <> NULL.

Semi Unique Constraints.

Now ask yourself the question, what happens if we are told that our other column SemiUniqueColumn can have as many NULLs as it wants but if it gets a value, that value must be unique?


-- Generate Semi Unique Values.
-- Every Sixteenth Value is NULL.
UPDATE
original
SET
SemiUniqueColumn = IIF((original.UniqueColumn % 16) = 0, NULL, original.UniqueColumn)
FROM dbo.SemiUniqueColumn AS [created]
JOIN dbo.SemiUniqueColumn AS [original] ON created.ID = original.ID;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_SemiUniqueColumn.png
Multiple NULLs But How to Force Uniqueness?

I used to think that this would be a complex requirement, possibly requiring a TRIGGER or two to check the inserted value against whatever is already there; but there is a way to have this functionality and have it the way that SQL Server normally would enforce a uniqueness on a column; by using a UNIQUE INDEX.

In case you’re thinking…

“Oh, a unique index doesn’t check what’s already there, is that it?”

I’m afraid that’s not the case.


-- Standard Unique Index Treats NULLs = NULLs.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn);

SemiUniqueConstraints_FailUniqueIndex.png
Won’t Create As Values Aren’t Unique

Yes, I know that we just showed that SQL Server treats NULLs as equal on Indexes but there is a way around this, ¬†and that’s to not use a simple unique index.

We simply ignore the NULLs altogether by using a UNIQUE FILTERED INDEX.


-- Filtered Index.
CREATE UNIQUE NONCLUSTERED INDEX [UFIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn)
WHERE SemiUniqueColumn IS NOT NULL;

Don’t trust me? Trust but verify!

First attempt to insert a duplicate value in SemiUniqueColumn:


-- Test it (Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, 7439);

SemiUniqueConstraints_FUI_Dup
7439 Is Already There So It’s Rejected.

Now we attempt to insert a duplicate value in SemiUniqueColumn but attempt to insert a duplicate NULL.


-- Test it (Not Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, NULL);

SemiUniqueConstraints_FUI_NotDup
NULL Is Already There But We Don’t Care.

Finally, NULL was chosen just for example purposes, filtering can be done on any value but, at the moment, there are a couple of got’cha’s with them…

Conclusion

I think you’ll agree that this would be so much easier to manage and troubleshoot than multiple triggers.

In fact, indexes are extremely useful aspects of databases and, once you start to realise how they work, you can start to use them for various different things than simple seeks and scans.

But the main aspect here is that complexity is not king. There are a multitude of ways to achieve something, so take a few moments to¬†think about the problem you are trying to face and you’ll be thankful in the long run.