Azure DWH part 20: Create External and Internal tables As Select (SQLServerCentral 12/12/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Azure DWH part 20: Create External and Internal tables As Select]( - [The four pillars of DevOps]( - [Checking SQL Server with Policy-Based Management]( - [The One Piece of Career Advice I Wish I’d Gotten]( (From the SQLServerCentral Blogs) - [Power BI Custom Visuals Class (Module 80 – TreeViz)]( (From the SQLServerCentral Blogs) The Voice of the DBA Advice for Newcomers This editorial was originally published on April 18, 2014. It is being republished as Steve is rehearsing for tomorrow's SQL in the City. A friend recently was asked to give a presentation on their career to a group of 12 year olds. It was a challenge to engage the students, and my friend was surprised that very few of the kids were interested in technology. I was disappointed as well since I think this is a great career choice, and worth a little investigation, especially at that age. It's hard to convince people to enter this business if they don't have any interest, but if they do, I'm wondering what you might tell them. Today I'd like you to think about your words of wisdom to someone interested in your job. What would you tell someone that you wish you knew early on in your career? You might impart some hints about how to approach technology. You might give them ideas on how to build skills faster or better? However for me, I'd tell them two things. First, you can't underestimate the value of networking, no matter what field you enter. Whether that's technology, medicine, law, or anything else, networking will help you. Learn to make contacts and interact with people. Second, learn what you don't like to do. Experiment with the technologies, practice the jobs, and understand what you don't like. You might not find something you have a passion for, but I'd certainly encourage you not to enter a field that you just don't enjoy. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [Redgate Hub]( Register now for SQL in the City Streamed Redgate’s popular SQL in the City Streamed virtual event takes place again this December. Wherever you are, tune in on Wednesday December 13 to watch some of the best-known speakers from the database world present the latest technologies and tools from Redgate. [Register free now]( [SQL Clone]( SQL Clone: Now supporting databases up to 64TB Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. [Try it free]( Featured Contents  [] [Azure DWH part 20: Create External and Internal tables As Select]( Daniel Calbimonte from [SQLServerCentral.com]() CTAS and CETAS are very important T-SQL features in ASDW. These features allow to create a table and fill with data based on a query.[More »]( ---------------------------------------------------------------  [] [The four pillars of DevOps]( DevOps isn’t just about tools. DevOps isn’t just about process. DevOps isn’t just about the people. DevOps isn’t just about culture. If you try and focus on one thing only, you’ll probably do it wrong and fail. You need to embrace all of the pillars. You wouldn’t build a house with only two walls and expect to have a wonderful experience the next time it rained.[More »]( ---------------------------------------------------------------  [] [Checking SQL Server with Policy-Based Management]( Additional Articles from [SimpleTalk]() Policy-Based Management, a feature of SQL Server, is a flexible tool that can help DBAs manage one or more SQL Server instances. It's used for monitoring and enforcing a standard set of policies for SQL Server throughout an organization. While there are many built-in conditions from which to choose, Dennes demonstrates how to set up a custom policy as well as a standard one. Viewing the policy status over many servers can be tedious, so he also explains how DBAs can evaluate the states of multiple servers with just one glance.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [The One Piece of Career Advice I Wish I’d Gotten]( Tim Mitchell from [SQLServerCentral Blogs]( Over the years, I’ve been approached numerous times with requests for advice from folks who are looking to get started...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Power BI Custom Visuals Class (Module 80 – TreeViz)]( Devin Knight from [SQLServerCentral Blogs]( In this module you will learn how to use the TreeViz Custom Visual. The TreeViz is a breakdown tree that...[More »]( Question of the Day Today's Question (by Steve Jones): I'm working with sales and I want to sum my totals by year and month, but I'd also like a total for the year in my result set. I have a few queries I've written. Which of these will give me a total for each month and a total for the year? I should end up with 13 rows in my result set. -- Query 1 SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, SUM(SubTotal) AS Incomes FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2012 GROUP BY YEAR(OrderDate), MONTH(OrderDate) WITH ROLLUP; GO -- Query 2 SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, SUM(SubTotal) AS Incomes FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2012 GROUP BY YEAR(OrderDate), MONTH(OrderDate) WITH CUBE; GO -- Query 3 SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, SUM(SubTotal) AS Incomes FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2012 GROUP BY GROUPING SETS ( YEAR(OrderDate), (YEAR(OrderDate),MONTH(OrderDate)) ); GO Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: GROUP BY. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592) The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. [Get your copy from Amazon today]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592). tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=0988262592 Yesterday's Question of the Day Yesterday's Question (by Steve Knox): I have the following variable: DECLARE @A VARCHAR(6) = 'ABCDEF' I want to produce the following output: values A B C D E F How can I do this using the STRING_SPLIT() function in SQL 2016+? Answer: SELECT value FROM STRING_SPLIT(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0)) Explanation: STRING_SPLIT() requires a delimiter of exactly one character, so you cannot pass in an empty string (answer 1) or NULL (answer 2) as the delimiter. However if you can modify your string such that every original character is followed or preceded by a new known character, you can split on that character. That's essentially what answer 3 does. Here's what happens: 1. CAST(@A AS NVARCHAR) replaces the byte-per-character @A with a Unicode two-byte-per character representation of the same string. Importantly, the input string consists entirely of characters which map directly to some of the first 256 Unicode characters. 2. CAST(... AS VARBINARY) tells SQL Server to treat this Unicode string as a sequence of bytes. In SQL Server, that's little-endian, so it becomes '0x410042004300440045004600' -- note the alternation of our desired values and 00 -- because these characters map to low Unicode characters, the most significant byte is always 0. 3. CAST(... AS VARCHAR) converts the binary value, byte-by-byte into a new string. If you select this value in SQL Server, it will return 'A', but it actually holds 'A{0}B{0}C{0}D{0}E{0}F{0}' where {0} represents the 0 byte values from the varbinary. Now you can use char(0) as the delimiter to split the resulting string and get the results you're after. Reference: STRING_SPLIT: [click here]( Unicode Code Charts: [click here]( (NB the characters which map to the first 256 bytes of Unicode and hence would work with this trick are under European Scripts > Latin > Basic Latin and Latin-1 Supplement) --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Administration]( [Which OS for SQL Server 2017 Standard? (new data warehouse)]( - My company will be setting up a new data warehouse server using SQL Server 2017 Standard. We're a mixed shop,... [Backing up multiple SQL instance and databases using 3rd party software - Any recommendations]( - Hello, We are looking to have a centralized software management tool for backing up SQL Server databases that can also provide... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Calling stored procedures from within other stored procedures]( - Hi folks, I heard something today that has me wondering: Is it is a good idea to write a long stored... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [dm_os_process_memory shows more memory usage than dm_os_memory_clerks & dm_os_buffer_descriptors]( - Hi, I'm looking for some help finding some missing memory and how to reclaim it without restarting the SQL service. The original... [Slow running Stored Procedures]( - Hi All, We have a number of stored procs and some of them are running quite slow. I have currently run... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Calculating varying process capacity]( - Hello again, SQL experts, I'm facing an interesting challenge. I have a production process that runs from 8 AM to 7... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [computed columns]( - Hi, With some googling, I learnt that SQL Server doesn't have persisted column names (like Netezza supports, for instance), which demands... [adding 365 partitions to existing scheme and fucntion in sql server 2014]( - Hi My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition... [Logic to check for empty table before joining]( - Hi All, I have a requirement which requires me to display report as per the dimension access to the user trying to pull the... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Intellisense broken?]( - Up until about a week ago, Intellisense on my SSMS worked fine. Now it's still working but for some reason... [Retrieving revenue total for last day of previous month]( - Hi Im trying to retrive total revenue last day of previous month, i can get all the months totals, however i cant... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Please help with xp_cmdshell]( - Ok I am writing a proc that will look to a directory and import excel files. It is a work... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Populate Acct Number (based on record above)]( - I have a file I get every month that has the account number on one line and the detail is... [How to execute multiple scripts]( - Hi  How to execute single file multiple scripts in one go. Thanks [Stock take FiFo conundrum]( - Hello, I'd very much appreciate some advice/help with a stock take problem. I've had a look at but my scenario differs somewhat.... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [DBCC CHECKDB job is throwing error:]( - Good Morning Experts, DBCC CHECKDB job is throwing below error: . A timeout occurred while waiting for memory resources to execute the query... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Administration]( [Monitor spids on SQL Azure instance? Can we do it?]( - Hi All, Can I do the below for SQL Azure instance/db? Suppose, I want to take a look at what is... --------------------------------------------------------------- [Programming]( : [General]( [bcp row terminator]( - I am trying to export the contents a query into a ASCII file. I need the character "LINE FEED" to be... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Design Input for ETL of OLTP Database]( - Hello All - I wasn't sure exactly where my question fit, so thought i'd put it here first. I just started... --------------------------------------------------------------- [SQL Server 2005]( : [SQL Server 2005 General Discussion]( [SQL Agent Time Gap between job steps]( - last night I got paged because a job ran about twice as long as it should so I checked the... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com