SQL Server 2017—SELECT INTO With Filegroup Support

One of the things I really appreciate Microsoft doing in recent releases of SQL Server is fixing some of the longstanding Connect items. These aren’t necessarily bugs, but design gaps—a good example of this was with SQL Server 2016, where the ability to truncate an individual partition came into effect. Some of these are minor, but have real impact into usability and functionality of the RDBMS.

Image result for file cabinet

The feature I am highlighting here is the SELECT INTO syntax for SQL Server. In the past, this syntax could only be used to create the new table in the user’s default filegroup. There were workarounds like changing the default filegroup for the user, but it was an extra step that shouldn’t have been needed. Starting with SQL Server 2017, T-SQL will support the ON syntax in this command:

SELECT * INTO dbo.NewProducts FROM Production.Product ON SecondaryFG

This isn’t huge, but it is a very nice thing that Microsoft has fixed.

Using SQL to Build SQL

One of the DBAs on my team hadn’t seen this trick yesterday, so I thought it would be good to document it for persistence. I particularly like to use this technique when granting access to a specific object to user or role. This technique is also known as concatenating SQL.

Here is how you do in SQL Server–if you want to grant select on all tables in a database to some user.

select ‘grant select on ‘+name+’ to someuser;’ from sys.tables

You will want to pay careful attention to your spaces, but the main concept is putting the text you want to generate in single quotes, and then using + signs around the name of the columns you are querying. Your result should look like this:

grant select on UNUSED_DBA_Maint_DBCC_CheckDB_Error_Results to someuser;

For those of you who use Oracle, you can do the same thing there–just using pipes instead of plus signs.

select ‘grant select on ‘||table_name||’ to someuser;’ from dba_tables where owner=’SYS’;


%d bloggers like this: