SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Tracking Professional Athletes

I used to read about technology to allow computers to track a single person's movements from video footage. We've seen this shown in Hollywood movies, where casinos can take a picture of a person and backtrack all their previous movements throughout the day. I have no idea if this is possible, but if it is, it's a scary proposition. 

However in specialized, controlled circumstances, we can track people very well. The NBA (SportVU) is tracking all player's movements and gathering more data that can be analyzed to better evaluate player's performances. I expect in the next year or two, players will start to review this analysis and learn how to better adapt to the situations on the court.

The NFL is also starting to use sensors to track their players, but with different goals. They are monitoring workloads, trying to ensure the health and peak performance of players. It remains to be seen how else they might use this data, but in the linked article, there are perhaps more implications for us as data professionals.

Can you imagine more tracking data, for any movable object available? People, machinery, who knows what else will be tracked, and what else we might need to analyze. I thought spatial data had tremendous possibilities when I first saw it introduced in SQL Server 2005. However across the last decade I think we've barely scratched the surface of what location data might mean for applications, using it mostly for mapping locations and routes. If this data does become useful, that means that learning to aggregate, trace, and analyze location might just need to be a valuable, if not core, skill set for the DBA and developer in the future. If you want to get started, maybe run through this article on your system and see what you think.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
SQL Doc

Hate explaining your database in meetings?

SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.

SQL Monitor

26 free helpful monitoring ideas for SQL Server

Protect yourself from the most common causes of SQL Server pain, with this series of 26 free tips. Covers the hows and whys of monitoring, with practical advice and worked examples. Sign up now.

SQL Source Control

Want to use Git with your SQL Server database?

SQL Source Control now has built-in support for Git. Inside SQL Server Management Studio, you can commit to Git, push & pull changes from remote repositories, and get an object-level history for your database. Learn more.

Featured Contents

 

I thought my database was collation agnostic

Thomas Romeo from SQLServerCentral.com

My struggles with a customer's database that used a different collation sequence. The easy way is never the right way, or is it? More »


 

SQL Server 2016 : Availability Group Enhancements

Additional Articles from SQLPerformance.com

Aaron Bertrand reveals details about the changes to Availability Groups that will ship in the next major version: SQL Server 2016. More »


 

From the SQLServerCentral Blogs - MongoDB -Access different databases and Collections

Prashanth Jayaram from SQLServerCentral Blogs

You can use db.getSiblingDB() method to access another database without switching the database. To List Collections of PP database and query... More »


 

From the SQLServerCentral Blogs - Why is my SQL Server using all of the RAM on the server?

Andy Galbraith from SQLServerCentral Blogs

The TL;DR on this is simple: "BECAUSE IT'S SUPPOSED TO!" A frequent complaint we receive comes from a client that has an... More »

Question of the Day

Today's Question (by Uwe Ricken):

You have a database with two tables. The table [dbo].[Customers] contain 75.000 records and the table [dbo].[Orders] has 1.000.000 orders stored. Both tables have a clustered index on the [ID] attribute and the table [dbo].[Orders] has a nonclustered index on the [customer_id] attribute. The indexes are definied as follows:

CREATE UNIQUE CLUSTERED INDEX ix_Customers_Id ON dbo.Customers (Id);
CREATE UNIQUE CLUSTERED INDEX ix_CustomerOrders_Id ON dbo.CustomerOrders (Id);
CREATE INDEX ix_CustomerOrderes_Customer_Id ON dbo.CustomerOrders (Customer_Id);
GO

The following query will be executed and the execution plan comes out with a MERGE JOIN!

SELECT CO.Id,
       CO.Customer_Id,
       C.*
FROM   dbo.CustomerOrders AS CO INNER JOIN dbo.Customers AS C
       ON (CO.Customer_Id = C.Id);

The developer of the database has heard about QUERY HINTS and tries to manipulate the query by usage of these hints. The code changes to the following statement:

SELECT CO.Id,
       CO.Customer_Id,
       C.*
FROM   dbo.CustomerOrders AS CO INNER MERGE JOIN dbo.Customers AS C
       ON (CO.Customer_Id = C.Id)

Question:

What will happen to the query? How will the query performance react to the query hint?

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 3 points in this category: Query Optimization.

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

Professional Microsoft SQL Server 2014 Administration

Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by M Martin):

Assume the following:

declare @myint tinyint= 5;

Will the following compile?

SELECT  s1.c1, s2.c1
FROM (values (s2.c1+2)) s1(c1)
CROSS APPLY
(values (@myint),(@myint+1),(@myint+2)) s2(c1)

Answer: No

Explanation:

For APPLY operations, we are told that "The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input."

Thus the left input must first be resolved. If you change the statements to reflect this , for example the following: 

declare @myint tinyint= 5;

SELECT  s1.c1, s2.c1
 from (values (@myint+2)) s1(c1)
cross apply
(values (s1.c1),(s1.c1+1),(s1.c1+2)) s2(c1)

You will get a block that compiles and runs successfully. 


» Discuss this question and answer on the forums

Featured Script

Real World: SQL Server Quick Quality Check

Randeep Singh from SQLServerCentral.com

Database Administrators can sometimes have one of the most stressful jobs in the company.Hard work, dedication and commitment is the real key to success. In this article, I would like to demonstrate a simple way to minimize your stressful day when DBAs have a frequent deadline pressure with numerous projects in progress at given time and you need to build and maintain a SQL server in real world to meet your client commitments as client comes first. In our client environment we are using mount point (SAN LUNs).Our disk layout for SQL system databases and data (Primary and Secondary) files is RAID-5 and for user database Log files and System Temp database is RAID-1.SQL server quick quality check script works from version of SQL server 2005 to SQL server 2016 and can help you to check below 14 items when you build a SQL server in real world. SQL server name, Instance name, Current Date Time ,SQL version ,Error Log file location, server authenticaton,login auditing. Names of Members in SysAdmin role, Names of members in ServerAdmin, Temp DB File Location (mdf, ldf and ndf), Physical location of your system databases and application database(s). Also, It can give you information about SQL server instance Max server Memory(GB), Min server memory (GB) and Lock Pages in Memory. Our previous DBA's were spending around two hours to peer review the SQL server build manually. The script contributed to decrease the number of hours to minutes to peer review SQL server instances and helps to improve the overall quality of our SQL server builds.

Reference: http://msdn.microsoft.com/en-us/library/bb545450.aspx

More »

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 2014 : Administration - SQL Server 2014

Stop deletes on a table - Hi, I want to stop all users from deleting records from a table. I went into permissions on that table and...


SQL Server 2014 : Development - SQL Server 2014

Best way to merge two large client databases - I have two client databases (same schema, different data) that I need to merge into a single database. These are...


SQL Server 2012 : SQL 2012 - General

How do I update STATISTICS on a column - Hello I have an index on a certain column in a table A. Does any one knows how to tell SQL Server...

Courses- best ones? - Firstly am in the UK :) if that has any bearing. Not sure where the best place to put this was, but...

Mitigating STIGs - I am trying to STIG my Instance and databases and wonder if you can help. I need help figuring out...


SQL Server 2012 : SQL Server 2012 - T-SQL

distinct from STUFF - Hi, SELECT DISTINCT t1.ProjectUID,t1.BWF , t1.Deliverable , --Supressed data columns STUFF( (SELECT ', ' + (t2.[GeoMarket]) FROM #deploys t2 where t1.ProjectUID = t2.ProjectUID AND t1.BWF = t2.BWF AND...

How to identify duplicate data with out Primary Key - Hi, I need to identify possible duplicate (not exactly duplicate row) data without primary key: Table A: Col1 Col2 Col3 Col4 Col5 Col6 123...

limitation on number of tables joined - Hi, Is there a limitation on how many tables can be JOIN-ed? I have close to 20 tables to join, each...

Stored Procedures slower than queries - Hi SQL Server community, I'm hoping you can shed your light on some unexpected behavior I'm seeing. I have a...

Header and Detail Data - We have a Header table and a Detail Line table, which join on the Primary Key . However, we are trying...


SQL Server 2008 : SQL Server 2008 - General

Is it possible to set fill-factor on partition level? - We have a partitioned table, where all partitions except the most recent 3, are set to read-only. They are never...

Simple Query but not easy to tune - Hi Guys, I have been tuning this query for a while and no improvement so far . From my database performance monitor...

Reorg index, rebuild index and reorg table - I am fairly new in sql server database I am very confused about the index and table maintenance jobs that we...


SQL Server 2008 : T-SQL (SS2K8)

using getdate for returning course list running for 2 months - hi I am new to stored procedures in sql management studio 2012. I have to write a stored procedure to return...


SQL Server 2008 : SQL Server Newbies

What is recursion? - Could someone explain recursion to me?


Programming : Powershell

Disable NIC on remote server in powershell - Hi I have a requirement to disable NIC using powershell remotely. I'm using below command. It works when i hardcode...


Data Warehousing : Integration Services

Guarantee entire flat file loaded - Just looking to get some confirmation or maybe another train of thought. We are loading in flat files from a file...

SSIS Balanced Data Distributor Data Flow Component (Table Locking) - I have a question regarding the Microsoft Balanced Data Distributor. [url=http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx]http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx[/url] Does anyone have any insight into how SQL Server handles...


SQL Server 2005 : Administering

Openrowset returns an error when run from my WS - Hi all, I am stuck since yesterday morning with a stupid problem on which I hope you can help me. I can't...

Unable to Connect in Single User Mode - I added -m; at the beginning of the startup parameters to start the server in single user mode. I then...

This email has been sent to {user_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 ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com