SQL Functions: QUOTENAME

Hey all, today I am going to show a quick demo on QUOTENAME. When I first discovered this string function, it was one I wished I had learnt long ago as it helps simplify writing out dynamic SQL.

To quote books online, QUOTENAME “Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.” So basically it formats an identifier with delimiters so that in the event that an identifier does not comply with formatting.

The example below returns all the tables in AdventureWorks 2014 with the different types of delimiters that can be added using QUOTENAME.


USE AdventureWorks2014
GO

DECLARE @tables TABLE (
[tableName] SYSNAME
)

INSERT INTO
@tables
SELECT
name AS tablename
FROM
sys.tables

SELECT
tableName AS [Raw],
QUOTENAME(tableName) AS [default],
QUOTENAME(tableName,'''') AS [singleQuoted],
QUOTENAME(tableName,'""') AS [doubleQuoted],
QUOTENAME(tableName,'<>') AS [InequalitySigns],
QUOTENAME(tableName,'@@') AS [Ats]
FROM @tables
GO

If you try this yourself you will notice that the last column returns NULL. This is because the @ symbol is not a valid delimiter, so you cannot pass any character in to wrap it around the field.

The example below takes all the tables and finds their corresponding entry in sys.sysobjects. Using QUOTENAME here means we do not have to specify the quotation marks around the table. There’s nothing wrong with doing that, it’s just that QUOTENAME looks a lot neater. (This is purely for the purposes of demoing the function; clearly you could inner join the two tables together…)


USE AdventureWorks2014
GO
DECLARE @sql NVARCHAR (MAX),
@count INT,
@tablename sysname

DECLARE @tables TABLE (
[tableName] sysname
)

INSERT INTO
@tables
SELECT
name AS tablename
FROM
sys.tables

SELECT
@count = COUNT (*)
FROM
@tables

WHILE @count > 0
BEGIN
SELECT TOP 1
@tablename = tablename
FROM
@tables

SELECT
@sql = 'select
*
from
sys.sysobjects
where
name = '
+ QUOTENAME(@tablename,'''')
from @tables

EXEC (@sql)

DELETE FROM
@tables
WHERE
tableName = @tablename

SET @count = @count - 1
END

All of the tables in AdventureWorks2014 are correctly formatted, so lets add one that isn’t.


use AdventureWorks2014
GO

CREATE TABLE [why would you even add spaces]
(id INT,
name VARCHAR(128)
)

INSERT INTO [why would you even add spaces]
VALUES (1, 'dont ever add spaces in table names')

The below query is going to fail because we do not have a properly formatted table name.


DECLARE @sql NVARCHAR (MAX)

SELECT @SQL = 'SELECT *
FROM ' + name
FROM sys.tables
WHERE
name LIKE '% you even%'
EXEC (@SQL)

GO

Use the QUOTENAME function, and this will run correctly.

DECLARE @sql NVARCHAR (MAX)

SELECT @SQL = 'SELECT *
FROM ' + QUOTENAME (name)
FROM sys.tables
WHERE
name LIKE '% you even%'
EXEC (@SQL)

GO

UK SQL Relay 2014 Plus Travel Info for London and Southampton

Throughout October, SQL Relay 2014 is taking place in the UK. If you’re unfamiliar with SQL Relay, it is a series of 1 day events that are run throughout the UK. Each event is a single track (or more in the case of London) conference, with between 50 and 200 attendees and 6-8 hour long talks by SQL Server professionals, MVPs, authors, technical experts etc.

Continue reading

SQL Server Summary Scripts

When I am given a new SQL Server to look after, other than using excellent tools like sp_Blitz, I like to record the details of the instance in a spreadsheet I use to document our SQL estate. Part of what I document I can get from running a few SQL queries. I have shared these below.

Glenn Berry has a diagnostics script that goes into far greater detail per server, which is also a great tool to run in order to ascertain the state of a server.


SELECT servicename, service_account FROM sys.dm_server_services

SELECT SERVERPROPERTY('MachineName') AS [MACHINE NAME], SERVERPROPERTY('ServerName') AS [SERVER NAME],
SUBSTRING(@@version, 1, CHARINDEX ( '-', @@version)-1) as [RELEASE],
SERVERPROPERTY('Edition') AS [EDITION],
SERVERPROPERTY('ProductVersion') AS [PRODUCTVERSION],
SERVERPROPERTY('ProductLevel') AS [PRODUCTLEVEL],
CASE SERVERPROPERTY ('IsClustered') WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' END as [CLUSTERED],
CASE virtual_machine_type
WHEN 1 THEN 'YES'
ELSE 'NO'
END as [VIRTUAL]
from sys.dm_os_sys_info

SELECT name, recovery_model_desc, suser_sname(owner_sid) as [Owner_SID], compatibility_level FROM SYS.databases

SELECT create_date AS [INSTALL_DATE]
FROM sys.server_principals
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE'

DBCC TRACESTATUS (-1)

SELECT windows_release as WindowsRelease,
CASE windows_release
WHEN 6.3 THEN 'Windows 8.1/Windows Server 2012 R2'
WHEN 6.2 THEN 'Windows 8/Windows Server 2012'
WHEN 6.1 THEN 'Windows 7/Windows Server 2008 R2'
WHEN 6.0 THEN 'Windows Server 2008/Windows Vista'
WHEN 5.2 THEN 'Windows Server 2003/R2 Windows Server 2003/Windows XP 64-Bit Edition'
WHEN 5.1 THEN 'Windows XP'
WHEN 5.0 THEN 'Windows 2000'
END AS [Windows],
windows_service_pack_level as ServicePack
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

SQL Server Diagnostic Information Queries for April 2014

sp_Blitz® – Free SQL Server Health Check Script

Paid In Full

Before I get into what exactly it is that I have paid in full, let’s have Eric B and Rakim drop a beat for you this morning:

So, that it’s then, my student debt is paid off! A mere 7 years after leaving university, I have fulfilled my side of the deal. I was going to write a large piece about the state of education today, but I think I’ll just put a link to a sobering news story on the BBC website about the state of the student loan system in the UK.

How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different: Continue reading

Attach and Detach Cubes Using AMO

goneDrinking

 

Before I start this post, I want to mention a few things:

  1. I’m (finally!) moving house next week. So I won’t have any internet for a couple of weeks. I’ve been so busy with everything else I have not been able to write any posts that will publish during my time off. So output will be down for the next month or so.
  2. If anyone can point me in the direction of a site that helps with giving blog posts good titles let me know; I really do suck at coming up with decent blog post titles and need all the help I can get.

All that being said, let’s get on with today’s topic, using AMO to detach/attach cubes! Writing about using PowerShell to manage SSAS via AMO was the whole reason I created this blog before it expanded to sharing a whole lot more, so I always enjoy coming back to this subject as much as I enjoy writing the scripts.

Continue reading