October this year is shaping up to be a month of learning, especially for those of us who live in London. Because not only is SQL Relay taking place on the 30th, there’s also RedGate’s SQL In The City happening a week earlier on the 24th. Continue reading
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) GOFollow @rPh0enix
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.
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);Follow @rPh0enix
A real quickie this evening, and it’s something I discovered yesterday the hard way: using NOLOCK table hints can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by design and also, there’s nothing wrong with it. Continue reading
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.Follow @rPh0enix
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
Before I start this post, I want to mention a few things:
- 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.
- 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.
To paraphrase a saying; learning is priceless, but education must be cheap. And if you’re like me then your training budget is usually whatever you find down the back of the couch!