SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082

Kathi SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about easy and amazing functions of SQL Server. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.


The first set of T-SQL window functions have been available for 10 years, but many SQL Server professionals are not quite sure what window functions are. I am frequently asked by people I meet at conferences and customer sites “What are window functions?” I always answer with another question, “Do you use ROW_NUMBER?” Almost every time, the answer is “Yes. I didn’t know that was a window function.”

T-SQL window functions are different than other T-SQL functions because they operate over a set, or window, of rows. Notice that I am not capitalizing “window.” They have nothing to do with the Windows operating system. They are part of the ANSI SQL 2003 standard.

T-SQL window functions give you the ability to perform a calculation over the rows that are the result of the FROM, WHERE, GROUP BY and HAVING clauses. You can do things like include a column from a different row, calculate running totals, and perform summary calculations without losing details or grouping.

My favorite T-SQL window function is called LAG. LAG, introduced with SQL Server 2012, lets you bring in a column from a previous row. This is useful for calculating year over year performance, for example.

Here is the syntax of the LAG function:

LAG(<expression>[,offset][,default]) OVER([PARTITION BY <expression>] ORDER BY <expression>)

You must provide the column name that you wish to bring forward, and you must provide an ORDER BY expression. The ORDER BY is used to determine just which row is the previous row. By default, the value returned is from the previous row. By specifying an offset, you can go back any number of rows. You can also specify a default value to replace any NULLs that are returned.

Run the following script to create and populate a table for testing LAG.

CREATE TABLE #Sales (
SalesYear INT NOT NULL,
SalesQtr INT NOT NULL,
SalesAmt MONEY
CONSTRAINT PK_Sales PRIMARY KEY(SalesYear, SalesQtr));
INSERT INTO #Sales( SalesYear, SalesQtr, SalesAmt )
VALUES (2009,1,1400),(2009,2,2200),(2009,3,2800),(2009,4,1000),
(
2010,1,1200),(2010,2,2300),(2010,3,2850),(2010,4,900),
(
2011,1,1550),(2011,2,2600),(2011,3,2900),(2011,4,1100),
(
2012,1,1500),(2012,2,2500),(2012,3,2800),(2012,4,1000),
(
2013,1,1300),(2013,2,2800),(2013,3,2750),(2013,4,900),
(
2014,1,1550),(2014,2,2700),(2014,3,2700),(2014,4,1200);

The following query uses the LAG function with an offset value of four to return the sales from the same quarter of the previous year.

SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearQtrSales
FROM #Sales;

notes 82 1 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 1: The partial results of using LAG

To calculate year-over-year sales, you subtract the previous year’s sales from the current sales and divide by the previous sales. To make the calculation a bit simpler, I’ll add the query to a CTE and perform the calculation in the outer query.

WITH Sales AS (
SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearSales
FROM #Sales)
SELECT SalesYear, SalesQtr, SalesAmt, Sales.PrevYearSales,
FORMAT((SalesAmt - PrevYearSales)/PrevYearSales,'P') AS YOY
FROM Sales;

notes 82 2 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 2: Using LAG for year-over-year calculation

notes 82 3 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082The LAG function is really easy to use, and it performs great. If you would like to learn more about LAG and all of the other T-SQL window functions, be sure to check out my new book: Expert T-SQL Window Functions in SQL Server.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Using Power of Web to Query Databases / Sources With Skyvia

Web technologies are moving at a pace that we can hardly keep up in pace. More and more of investments are going on in the space of web that even database developers like me find it interesting. In the same context, recently I was exploring on Skyvia (SQL SERVER – Integrate Your Data with Skyvia – Cloud ETL Solution) from ETL to how one might query and was surprised to see how some of these can be made useful.

This blog stems from a simple idea that was in my mind for a long time and I was struggling for a suitable solution till I saw what was available here. Think that you are a DBA and are on a vacation to a far-away land. You didn’t take your laptop along because you wanted to really enjoy your vacation times. Everything looks normal till you get an SMS from your management that something is wrong on one of your servers and they need 10-15 mins of your time to quickly resolve. Now with no laptop and all the cool things that you would have normally installed on your machine, how can you help your management with a few query support? Is it even possible? Assume you get to an internet kiosk and you want to do some amount of troubleshooting. How will you do? Well, there are a number of solutions.

One of the many available including the use of Skyvia to do query on your servers once you have done the simple step setup. In this example, I have used an SQL Server 2014 version available on a Cloud provider so that we can see what is going wrong.

Let us start by connecting to Skyvia and setting up our connections.

 Using Power of Web to Query Databases / Sources With Skyvia

In my example, I am going to use the “SQL Server” source and will give all the credentials. Once done and tested, we must see this in our connections tab.

 Using Power of Web to Query Databases / Sources With Skyvia

Once the connection setting seem to be in place, we are all set to run our queries on the backend. We can then head to the Query tab as shown below:

 Using Power of Web to Query Databases / Sources With Skyvia

At this stage, I just linked the Query window to my connection that we created in our previous step. This started to list our all the tables that were available on my server. This was really a cool moment because I was able to work on my database in less than 5 mins via the web.

 Using Power of Web to Query Databases / Sources With Skyvia

Obviously based on the permissions that I used to connect, the tables and objects are displayed. If there are 100’s of tables to work, then we can surely use the search to find the objects.

The next stage or table is our awesome query window. It is here that we can type in any SQL-92 query that needs to be fired against our database. Since I was wanting to know what was happening in my SQL Server system and what operators were functioning of few long running query, I used a DMV from SQL Server 2014 to check if it works. For my surprise, I was not disappointed.

 Using Power of Web to Query Databases / Sources With Skyvia

This is the query result output based on the query that I was running in the backend.

SELECT
node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS FLOAT)/SUM(estimate_row_count)
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

To know more about sys.dm_exec_query_profiles you can read from MSDN. I also talk about this DMV in my course at Pluralsight – SQL Server 2014 Administration New Features. The course is exhaustive in understanding some of these new capabilities of SQL 2014.

Coming back to our query, based on the output I was able to find out what was the query that was running behind the scene. As you can see this is a Dummy query without any filters or restriction on the columns selected. Hence we are seeing a number of Clustered Index Scan in our query output via the web.

SELECT      *
FROM DimProduct
INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN FactInternetSales
ON DimProduct.ProductKey = FactInternetSales.ProductKey

Once the query was identified, you can start looking at what is firing up the query and take corrective actions. Any normal SQL syntax can be fired like this via the web. It is simple and quite powerful to say the least.

The results from Skyvia is powerful that you can also use them to export to CSV format for later offline consumption. As part of simple testing, I also fired few DDL statements and was surprised to have almost everything I do via SQL Server Management Script window here via the web, anytime and anywhere I like.

And another thing I’d like to mention is that Skyvia Query is not just for SQL Server and other databases. Skyvia Query supports SQL for cloud CRMs, such as Salesforce, Dynamics CRM, etc, and allows using SELECT, INSERT, UPDATE and  DELETE statements against them. This enables admins, knowing SQL, work with cloud data in the same way as with SQL Server data.

Currently Skyvia is free for use. You can find more information on how to use their online SQL editor on the following page: Online SQL Query Editor for Cloud and Relational Data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120
)

# do a check to ensure that collection duration is greater than
# or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"
    exit
}

# loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -ComputerName $ServerName -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},
            CookedValue

    Get-Counter -ComputerName $ServerName -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},
            CookedValue

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds
}

# Group by the drive and Calculate the average for each drive we have
# round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {
            [int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}
        } |
    Sort-Object InstanceName

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

PowerShell DiskLatency 01 SQL SERVER   Identify Disk Latency on SQL Server Box Using PowerShell

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns

Sometimes the best way to learn is by taking some of the capabilities to the extreme. This blog post is one of those explorations which I would like to share. A very long time ago I wrote a blog on this feature which was introduced in SQL Server 2008 called “Sparse Columns”. You can read below blogs to read about the fundamentals of SPARSE columns:

One of the thing which people have misunderstood is the number of sparse column we can have with a table. As per documentation we can go beyond 1024 column limit by using sparse columns. One of my blog reader has done some test and said that the statement is not true. Here is the script which we was using:

CREATE TABLE SomeTable (Id INT SPARSE NULL)
DECLARE @SN INT
SET
@SN = 1
WHILE (@SN <= 1030)
BEGIN
DECLARE
@CLN VARCHAR(100)
SET @CLN = 'C' + CONVERT(VARCHAR(100), @SN)
DECLARE @Query VARCHAR(MAX)
SET @Query = 'ALTER TABLE SomeTable ADD ' + @CLN + ' nvarchar(100) SPARSE'
SET @SN = @SN + 1
EXEC (@query)
END
GO

Above script is having a logic where it would create a table called SomeTable as below

CREATE TABLE SomeTable (Id INT SPARSE NULL)

And then it would keep adding columns using dynamic SQL

ALTER TABLE SomeTable ADD C1 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C2 NVARCHAR(100) SPARSE
..
..
ALTER TABLE SomeTable ADD C1023 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C1024 NVARCHAR(100) SPARSE

It fails with below error

Msg 1702, Level 16, State 2, Line 1
CREATE TABLE failed because column ‘C1024′ in table ‘SomeTable’ exceeds the maximum of 1024 columns.

As per https://msdn.microsoft.com/en-us/library/ms143432.aspx (Maximum Capacity Specifications for SQL Server) we should not have received the error because our table is a wide table.

Columns per non-wide table 1024
Columns per wide table 30,000

Here is the answer from Qingsong Yao (Microsoft)

In order to create more than 1024 columns, you have to have a columnset column defined in the table. Once the table has columnset defined, the select * will hide all sparse columns, and only see the column set. Meanwhile, people can still select individual sparse columns in their query.

So, the test done by my blog reader was having little problem. He forgot to define columnset column. Here is the sample of columnset with sparse column.

AllDetails XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Let me give you a quick example.

SET NOCOUNT ON
GO
USE tempdb
GO
CREATE TABLE MySparseTable (
i INT
,Detail XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
,Height DECIMAL(10, 2) Sparse
,Width DECIMAL(10, 2) Sparse
,Length DECIMAL(10, 2) Sparse
,Color VARCHAR(10) Sparse
,Volume DECIMAL(10, 2) Sparse
)
GO
INSERT INTO MySparseTable (i,Height,Width)
VALUES (1,11.9,12.0);
INSERT INTO MySparseTable (i,Height,Length)
VALUES (2,2.8,9.3);
INSERT INTO MySparseTable (i,Color)
VALUES (3,'Red')
GO
SELECT *
FROM MySparseTable
GO
SELECT i
,Height
,Width
,Color
FROM [MySparseTable]
GO
/* Clean up time
drop table MySparseTable
go
*/

Here is the output.

Sparse 01 SQL SERVER   CREATE TABLE Failed Because Column C1024 in Table myTable Exceeds the Maximum of 1024 Columns

Have you ever use this feature in your environments? What has been your experience in using and what scenarios have you been using these capabilities? Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

Question: What is the difference between Index Seek and Index Scan?

Answer: Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan  in the execution plan.

Here are few other related articles on this subject which you may find useful:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server – Knowing Deprecated or Discontinued Features Using Extended Events

This blog is in continuation to what I wrote couple of weeks back – SQL Server – Knowing the Use of Deprecated or Discontinued Features. The concept of using a deprecated feature is not by intensions by developers but is an accident for many. When I wrote that blog post, lesser did I know people are going to ask me for more. One of my reader mentioned using SQL Server Profiler to identify the same. Though this is a valid answer, I wanted to keep Profiler away for this and introduce him to a new method of using Extended Events for the same requirements.

With every new release of SQL Server, the investments that are going to Extended Events is amazing. So I think to bring the same in a step-by-step for folks who want to use the same for identifying Deprecated features. Let us work through the basic steps in setting this up next:

  1. Open the SQL Server Management Studio.
  2. Connect to the server next.
  3. Click the plus sign next to Management then click the plus sign next to Extended Events.
  4. Click on Sessions then right click on New Session Wizard
    deprecated xEvent 01 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  5. At this point the New Session Wizard screen should have launched. Once the New Session Wizard has launched, click Next.
  6. Type “Deprecated and Discontinued” in the text box after session name and click Next.
  7. Choose the radial button  Do not use a template then click Next
  8. In the Event library text box type ‘deprec’ and choose the deprecation announcement and deprecation final support events and then click the > arrow in the middle of the page to move them over as Selected events.
    deprecated xEvent 02 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  9. In the Capture Global Events section check the following:
  • client_app_name
  • client_connection_id
  • database_name
  • nt_username
  • sql_text
  • username
  1. Then click Next
  2. On the Set Session Event Filters screen click Next. We are not filtering this Event Session.
  3. Check the top check box next to Save data to a file for later analysis Make the Max File Size 5 MB with Rollover files as 20.
  4. Click Next.
  5. On the next screen click Finish on the Summary
  6. On the next screen click Close after the Event Session is successfully created.

The same can be scripted out using the script button in the wizard before you hit the finish button. The script for the same steps would look like:

CREATE EVENT SESSION [Deprecated and Discontinued] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'Deprecated and Discontinued',max_file_size=(5),max_rollover_files=(20))
WITH (STARTUP_STATE=OFF)
GO

If you want to use the same. Go ahead and start the session for Extended Events and start the collection process.

Extended Events are cool in a lot of ways. As part of my learning adventures, Extended Events are slowly getting into mainstream. Having said that, I have tons to learn from you. Do let me know how you use Extended Events in your environments and let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Only Formatted Files on Which the Cluster Resource of the Server has a Dependency can be Used

Getting little help and learning from each other always helps. When I don’t get to know of something and I need to explore something, I try to check with my SQL friends who can guide me in the right direction. This blog is a classic example of me trying to learn something and help the community. Recently I got an email from my blog reader as below:

<EMAIL>

Hi Pinal,
My Name is Aysha and I have been a regular follower of you blog. Just to let you know that I am a hard-core developer, but also an accidental DBA. So please pardon me if this is a really simple issue for you. Also, I don’t have much knowledge about cluster. Here is the problem: I needed to create a new database on the SQL Server 2012 two node failover cluster to host database for new application under development. By following few internet articles, I added a new drive (M and N) into the SQL Group in the cluster, but I am unable to create database on the newly added drive.

Here is the command I was trying

CREATE DATABASE [TestDatabase]
ON PRIMARY
(NAME = N'TestDatabase', FILENAME = N'M:\SQLDATA\TestDatabase.mdf'  )
LOG ON
(NAME = N'TestDatabase_log', FILENAME = N'N:\SQLLOG\TestDatabase_log.ldf')

But here is the error I am receiving

Msg 5184, Level 16, State 2, Line 1
Cannot use file ‘M:\SQLDATA\Testdatabase.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Do you think I did something wrong? I have restarted SQL multiple times, but still same error. Can you please help?

Thanks
Aysha

<EMAIL>

Here is my reply.

<Reply>

Hi Aysha.

Thanks for contacting me and giving me all the details which I always expect from anyone who contacts me. Since you are new to cluster, you need to read a little more about the dependencies of cluster resource. Dependency decides the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster.

  • The IP Address and the Disk Resource do not depend on anything.
  • SQL Network Name depends on the IP Address.
  • SQL Server depends on the Network Name and a Disk Resource.
  • SQL Agent depends on SQL Server.

Coming back to problem which you are facing (i.e. below error). Here is a breakup of error

Only formatted files on which the cluster resource of the server has a dependency can be used.
Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

See that, dependency terms in error message?

So, all you need to do is add dependency of disk M and N which you have added post installation, to the SQL Server resource. SQL Server is preventing us from creating databases on drive which aren’t a dependency of the SQL Server resource. This is to make sure that the disk is online before the SQL is trying to access database file.

Here are the steps

  • Open Failover Cluster Manager from Start Menu OR you can also go to Start > Run > Cluadmin
  • Select the clustered application, i.e. the group which has SQL Server.
  • Right-click on your “SQL Server” resource then choose Properties.
  • Go to Dependencies tab.
  • Click the Insert button to add an additional row, then select the shared disk (M) that is going to contain your SQL databases. In next row N also to be added.
  • Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.

That’s it. Depending on the version of SQL, you might need to take SQL Server resource offline and bring it back online by choosing from right click.

Hope this helps!

</Reply>

Later she responded back and informed me that she was able to fix the issue after following the steps.

Home work: Can you help me out with this – which SQL version does not need restart after modifying dependencies of the disk? If you already have a working SQL cluster, please test and let me know by commenting. This was a great learning for me too, and felt I would quiz you with some research.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Adding Reference Data to Master Data Services – Notes from the Field #081

[Note from Pinal]: This is a 81th episode of Notes from the Field series. Master Data Services is one of the most important, but very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add referencing data to Master Data services to our enterprise. Read the experience of Reeves in his own words.


Reeves Smith SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

In a previous post, I talked about Master Data Services and how it can enhance your Enterprise Data Warehouse. Through the Master Data Services add-in for Excel, the business can access and manage reference data without much involvement with IT. This might sound like a bad idea, but the subject matter experts (SME) for the data often do not reside in IT. SMEs should have the ability to manage reference data, and with the Master Data Services add-in for Excel they can.

In this post, I’m going to outline the three steps needed to get your data governance people up and running after Master Data Services has been installed. The three are:

  • Create a model to store the reference data (completed on the MDS website)
  • Format the reference data in Excel
  • Create an Entity within the Master Data Services add-in for Excel

IT will only need to perform the first step of the process. The first step to manage reference data is creating a container for the reference data. This step must take place within the Master Data Services website.

Step 1 – Create a model to store the reference data

  1. Open the Master Data Service’s website and Select System Administration, as you see in Figure 1.

notes81 1 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 1 – Master Data Services System Administration

  1. After you select System Administration, the website will list three menus: Model View, Manage, and System. Select Manage and then select Models from the pop-out menu.
  2. Click on the green plus sign to create a new model.
  3. In the Add Model dialog, which you see in Figure 2, add a model name that would be relevant to the appropriate reference data. In this example I will use Demo. Unselect all of the check boxes under the model.

 

Figure 2 – Add Model

  1. Test your model by connecting to it from the Master Data Services add-in for Excel.
    1. Open a blank Excel workbook and select the Master Data Services tab.
    2. Click the Connect button to create a new connection to your Master Date Services Instance.
    3. In the Master Data Explorer, which you see in Figure 3, select your newly created model.

Figure 3 – Master Data Explorer

notes81 2 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 2 – Format the reference data in Excel

  1. Create an Excel worksheet with the formatted reference data and provide a header for each of the columns.

Note: Master Data Services requires a Code column and suggests the use of a Name column. Both of these columns do not need to be directly called Name and Code but should have like columns within the reference data. The code column must contain unique values.

notes81 3 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 3 – Create an Entity within the Master Data Services add-in for Excel

For this example, I will create a list of customers from the Adventure Works database to enable the discussion of enhancements to the data set in a later post. Your reference data should be less complex then the customers list.

  1. With an open connection to Master Data Services and the worksheet with your reference data, click the Create Entity button, as you see in Figure 4.

notes81 4 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 4 – Master Data Services tab with the Create Entity highlighted

  1. Click the red arrow to the right of the Range text box and select the range of your reference data. Be sure to include the column headers and select the My data has headers check box, as shown in Figure 5.
  2. Select the newly created model; in my case that would be Demo.
  3. Add and new entity name and select the appropriate Code and Name column.

Note: Master Data Services 2012 and forward enables the use of an identity values for the Code if you do not want to manage unique keys.

notes81 5 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 5 – Create Entity Dialog

  1. Click OK.

notes81 6 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

At this point you should have a working model with a new entity that is fully managed within Master Data Services.

Summary

In this post, I have walked through the steps that are required to move reference data from an Excel workbook and create a model within Master Data Services. Under MDS you get all of the benefits of a managed master data management solution, like audibility and security.

What’s Next

In a coming post, we will walk through enhancing or reference data. One enhancement we will demonstrate, will include adding domain values within the Model. This helps the business users select the correct data elements and increase the data quality of your master data.

Hope this helps.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle with MONTH Function – Win USD 50 Amazon Gift Card

It has been a while since we ran the contest. I reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.

Two Giveaways:

amazon gift cards SQL SERVER   Puzzle with MONTH Function   Win USD 50 Amazon Gift Card

(Global) USD 50 Amazon Gift Card to 1 Individual

(India) INR 2500 Flipkart Gift Card to 1 Individual

Contest 1: T-SQL

Run following T-SQL script and observe the answer:

SELECT MONTH(18/200), MONTH(200/18)

monthcontest SQL SERVER   Puzzle with MONTH Function   Win USD 50 Amazon Gift Card

When we look at the answer, it displays 1 as a answer to both of the expression. The question is why does above script return values as 1 instead of throwing an error or displaying any error?

Contest 2: Download and Install DBArtisan

This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.

How to Participate?

  • Leave an answer for contest 1 in the comment section of the blog.
  • Leave a comment with the same email address which you have used to download DBArtisan.
  • The contest is open till June 2nd, 2015 Midnight PST.
  • The winner will be announced on June 4nd, 2015.
  • There will be two winners 1) Global 2) India.
  • All the correct answer to the contest will be hidden till the winner is announced.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Antivirus Exclusions Best Practices With SQL Server

One of the classics as far as best practices is concerned is to exclude SQL Server files and folders on your antivirus programs running on the server. We all know this statements and often I see DBAs not adding any extra care to this recommendation. There are not many blogs that call them out explicitly.

Although antivirus software is a very important part of security planning, it is important to understand the effect it has on SQL Server files.  After rebooting a server, if the antivirus software locks a SQL Server file before SQL Server can gain access to that file, potential issues ranging from SQL Server not being able access those particular files to possible database corruption may occur. In a number of cases I have seen SQL Server refusing to start sometimes or the CPU / IO seems to be stalling sometimes. As you can see there are a number of these random errors one might get because of not implementing a simple best practice. Therefore, it is recommended that the following files be excluded from all antivirus scans:

  • SQL Server data files (typical extension is .mdf, .ndf, and .ldf)
  • SQL Server backup files (typical extension is .bak and .trn)
  • Full-text catalog files
  • Trace files (typical extension is .trc)
  • SQL Audit files
  • SQL query files (typical extension is .sql)
  • Directories holding Analysis Services data and partitions
  • The directory that holds Analysis Services temporary files used during processing
  • Analysis Services backup files

In addition to excluding SQL Server and Analysis Services files, it is recommended to exclude the following list of processes from antivirus scans:

  • SQLServr .exe
  • ReportingServicesService .exe
  • MSMDSrv .exe

For environments where SQL Server is clustered, exclude the C:\Windows\Cluster directory and the Quorum drive.

Though this list is not exhaustive, I am sure there might be a few more that I might have missed. This was a humble shot at this topic and I am sure as seasoned DBA’s you might have a little more to include. Do let me know under the comments section and I am sure this can be a great source of reference for others searching on this topic too.

Reference: Pinal Dave (http://blog.sqlauthority.com)