Stairway to MDX - Level 2: The Ordinal Function (SQLServerCentral 11/15/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Stairway to MDX - Level 2: The Ordinal Function]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [Remembering passwords in SQL Compare and SQL Data Compare]( - [Consuming hierarchical JSON documents in SQL Server using OpenJSON]( - [Over-Indexing can hurt your SQL Server performance]( (From the SQLServerCentral Blogs) The Voice of the DBA The SQL Server Crossword Enjoy our latest crossword puzzle and see if you can solve all the clues. The key will be posted in the comments tomorrow. Across 2. Create a hash of a value. 5. Add a query hint to the whole query. 10. Open an XML document as if it was a table or view. 12. Billy the kid. 14. Immediate unreliability 16. a.b.c.______ 17. The memory where index and data pages go swimming. 18. Required to sort the output of a select statement. 19. DOS 20. One copy of each row. 21. How am I going to grant you permissions without actually granting you permissions? Down 1. You probably aren't using this to store your code. 3. Geometric or Geographic 4. Login or User 6. Time to be back up. abbr. 7. Comma 8. Evaluate the right-hand source once for each row in the left and return a row regardless of if there is a result. 9. Permission required to run a select statement. 11. Doorways to SQL Server 13. No, a 50gb DB is not one of these. 15. Create a new table containing the data from the query. 19. Loop through a result set. Kenneth Fisher from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [Database DevOps]( Webinar: data protection & privacy in the world of DevOps Microsoft MVP and PASS President Grant Fritchey dispels the myth that database DevOps and compliance can't go hand in hand. After a brief look at how extending DevOps to the database lays solid foundations for data governance and compliance, the focus will then shift to guidance around the upcoming GDPR. [Register now]( Featured Contents  [] [Stairway to MDX - Level 2: The Ordinal Function]( Bill Pearson from [SQLServerCentral.com]() Business Intelligence Architect Bill Pearson introduces the MDX Ordinal Function, as a means for generating lists and for conditionally presenting calculations. He also demonstrates the use of the function in creating datasets to support report parameter picklists.[More »]( ---------------------------------------------------------------  [] [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from [SQLServerCentral.com]() This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way.[More »]( ---------------------------------------------------------------  [] [Remembering passwords in SQL Compare and SQL Data Compare]( The Redgate SQL Compare team have recently added a feature to automatically populate your SQL Server credentials when you’re using SQL Compare or SQL Data Compare - here are the details of the new feature.[More »]( ---------------------------------------------------------------  [] [Consuming hierarchical JSON documents in SQL Server using OpenJSON]( Additional Articles from [SimpleTalk]() Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server, and wrote several articles on ways of overcoming these problems. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed the process up.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Over-Indexing can hurt your SQL Server performance]( Dharmendra Keshari from [SQLServerCentral Blogs]( Indexes are a vast subject and well documented elsewhere on the web. But, it is still a very commonly asked...[More »]( Question of the Day Today's Question (by Avinash): Sometimes we need to share a variable or session state across different batchs in same session. For example, the second part of the below script will return an error since @i is not available in that conext. DECLARE @i INT = 100 SELECT @i GO SELECT @i Which of the below options can be used to store values in a SQL Session (SQL 2016), so that it can be accessed in all the batches of a script in the same session? 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: Session. 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 [Pro Power BI Desktop]( This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): What's the datename return value? My instance default language is US English DECLARE @RunDate DATE = '10/02/17' SET LANGUAGE British SELECT DATENAME(dd, @RunDate) Answer: 02 Explanation: The Datename function returns a value based on the language and parameter. In this case, the return is the day of the month. Datename() will return the numerical value. The SET LANGUAGE for the variable declaration sets the date, which is stored as an integer value. The SET LANGUAGE for the SELECT doesn't change this until the end of the batch. Ref: Datename - [click here]( SET LANGUAGE - [click here]( --------------------------------------------------------------- [» 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]( [Implications of slower writes with 64K pages using SSDs vs SAN]( - As part of our migration to SQL Server 2017, we are considering a move to local SSDs.  Some testing with... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Indexing Issue]( - Hi, I have the following temp table listed below. In the interface we created the user has the option to enter in... [Shared stored procedure with very complicated input]( - So, the basics of what I want to accomplish is simple...I have a stored procedure that has some really complex... [Delete syntax not working]( - hi, Here is the delete syntax but it is not working even though all the conditions satisfy. What is the issue? DELETE... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [can I put more than one database per disk?]( - Actually I have one database disk C : OS disk D: tempDB disk E : MDF fie disk F : Ldf file disk G : Backup disk now I have to create... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [A way to restaure backup skipping some filegroups...]( - Hello, A have to restaure a huge database (PROD) on a smaller environment (STAGE). My database is split in filegroups, I... [Using Extended Events to alert on long running queries]( - Hi Is there a way with Extended Events, where I can set up something; should a user/developer run a query via... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Doing multiple updates with an ADODB Connection]( - This working code lets the user enter something in a cell, click a button in Excel and exports the data... [Modifying my parameterized query to select * an entire column]( - This working code lets the user use a click event in Excel/VBA to separately insert values into the same field... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Delete Server in DB Engine]( - Hello, I have two different instances of database engine on local servers. How do I delete one? Thanks --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [CASE statement and inner join giving duplicates]( - Dear DBAs I am using a CTE to get terminal locations from one table and then joining this CTE with... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Move user database]( - Why is detach/attach not recommended for moving a user database? --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Query to give sum of items with condition from multiple tables]( - I have these tables: LineItemSubParts, CostCodeSubCategories, CostCodes and LineItems In the LineItemSubParts table there are fields named Quantity, MQuantity and Cost In the CostCodes... [Running Balance query]( - I have a Transaction table having the structure as follows: DECLARE @Transtable TABLE( ID INT IDENTITY(1,1) NOT NULL, DATE DATETIME,... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [S?QL Server using 97% of RAM.]( - Good Morning Experts, SQL Server is using 97% of RAM. Could you please advise what needs to be done. [Date Search]( - Hi, I have in a data row: column   2017-11-06 01:17:34.000 I only want to search for values if the year is... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 Administration]( [Log shipped db in suspect mode]( - Hi, I have a log shipped database on SQL 2008R2 that last night went into suspect mode & I cannot run checkdb... --------------------------------------------------------------- [Reporting Services]( : [SSRS 2014]( [Sum for year within Tablix]( - Hi I have a report that I am trying to do that is filtered by what period the user enters.... [What URL should I give to run the report directly..]( - Question: I am trying to get a report to run immediately. What I mean is if I paste the following... --------------------------------------------------------------- [Microsoft Access]( : [Microsoft Access]( [Hard going back...]( - Is it just me, or once you've used SQL Server, is using Access again (especially for reasonably complex queries) just... 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