SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

Abolish Disjointed Time

One of the more complex types of data to deal with is date data. We have dates, times, strange rules for when mathematical operations occur, and when they don't. We have a period, which can be some combination of these, and that can include math rules that must be programmed consistently. Add to the issue of time zones where 3:00 isn't 3:00 everywhere in the world, especially those strange half and quarter time offset zones, and it's much more complex than seems necessary. There are no shortage of questions and articles at SQLServerCentral because this is a complex topic.

I read recently that the European Parliment is considering getting rid of DST. Quite a few of the members think the practice isn't useful and in a 384 to 153 vote, they decided to review whether or not they think this is a practice that should continue. There have been studies that show the change doesn't help with power, and it's certainly disruptive to everyone. I know it seems a percentage of people are always confused and either arrive early or late every year (or twice a year) when the clocks change. I know I've been late to work in the past on a Sunday when the clocks changed. 

I'm of the opinion that we shoudl do away with DST. I get a double whammy every year the I work for a company in the UK and the US changes clocks at a different time. That means I get meetings that move for a few weeks, and just as I adjust to the new time, they move back. It's a pain, and I'd just as soon do without it. There are also the adjustments to body clocks that likely slow our work and study habits, or at least mine, for a period of time. I'd just prefer that we stick to a single time schedule the entire year.

From a data perspective, the adjustments can cause issues with reporting and tracking data. Having an hour essentially repeat itself can distort any aggregation over that time period. Likewise losing an hour, especially if we use left joins to ensure each time period has a value, can look funny. I know the data issues aren't likely a big deal, after all, how much data does your company gather in the middle of Saturday night a couple times a year? Most reports probably don't bother to account for the discrepencies, and there don't seem to have been any issues I've seen from organizations. It's annoying to me as a data person, but it's probably not a significant issue.

Ultimately I think DST is just a little silly in this modern world, where specific times, especially daylight time, seems to be less of an issue. I work when I need to, take time off when I can, and it seems more and more companies do the same thing. Whether I go to work in the light or dark, the days are shorter in the winter.

Steve Jones from SQLServerCentral.com

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

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

SQL Provision

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

Featured Contents


TDE with Automatic Database Seeding

Evgeny Garaev from SQLServerCentral.com

Transparent Data Encryption on an Availability Group and Automatic Database Seeding More »


Azure Cosmos DB Partitioning

Additional Articles from Database Journal

One of the most important design decisions that must be made when planning deployment of Azure Cosmos DB involves logical partitioning of data that will populate target collections, graphs, or tables. Selecting the optimal partitioning model has both performance and pricing implications. In this article, we will explore the rationale behind these implications and review the partitioning options. More »


From the SQLServerCentral Blogs - Finding Composite Primary Key Columns

SQLRUs from SQLServerCentral Blogs

You never know when the idea for a script will make an appearance. I had to work with composite primary keys... More »


From the SQLServerCentral Blogs - Set New Errorlog File location – SQL Server on Linux

Dharmendra Keshari from SQLServerCentral Blogs

For SQL Server 2017 RTM, the fourth cumulative update was released on 20th February, and it is available for download... More »


From the SQLServerCentral Blogs - Using the Analysis Services Execute DDL Task

Angela Henry from SQLServerCentral Blogs

My grandfather used to say, “There’s more than one way to skin a cat.”  I always thought it was a... More »

Question of the Day

Today's Question (by J. Drew Allen):

What is returned when you run the following query? 


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: SUBSTRING.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have these two data frames:

> passing.2016
  rank   player.name year yards2016 trailingyards
1    1    Drew Brees 2016      5208             0
2    2     Matt Ryan 2016      4944           264
3    3  Kirk Cousins 2016      4917           291
4    4 Aaron Rodgers 2016      4428           780
5    5 Philip Rivers 2016      4386           822
> passing.2017
  rank        player.name year2017 yards2017 trailingyards
1    1          Tom Brady     2017      4577             0
2    2      Philip Rivers     2017      4515            62
3    3   Matthew Stafford     2017      4446           131
4    4         Drew Brees     2017      4334           243
5    5 Ben Roethlisberger     2017      4251           326 

I want to combine them on the player name to find the matches. Which of these will return me a data frame with only two rows?

Answer: merge(passing.2016, passing.2017, by="player.name")


The merge command will combine these two data frames, but it needs to know which field to use. The by parameter can be used to specify a field. Choosing player.name here resutls in:

> merge(passing.2016, passing.2017, by="player.name")
    player.name rank.x year yards2016 trailingyards.x rank.y year2017 yards2017 trailingyards.y
1    Drew Brees      1 2016      5208               0      4     2017      4334             243
2 Philip Rivers      5 2016      4386             822      2     2017      4515              62 

Ref: Merge() - 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 2016 : SQL Server 2016 - Administration

Upgrade to 2014 or 2016 - Dear DBAs I hope you guys are doing great My company is planning on upgrading all 2008 R2 database servers...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Fill in gaps between 2 dates - Hi  All, Need some help in filling up gaps between 2 dates. Scenario :  I have  Product Location balance  snapshot for specific dates ...

SQL Server 2014 : Administration - SQL Server 2014

License dev server - We have our prod server where our we have SSIS installed. Our development team creates packages and update our DBs....

What Extended Events should I use? - I have a scenario where a production SQL server appears to be completely clearing both the plan and buffer caches...

Backups, Backups, Backups....... - I work as a DBA for a medium size municipal government organization. I work together with another DBA and together...

SQL Server 2014 : Development - SQL Server 2014

Why does my stored procedure return -1? - Hello, I have a stored procedure. The call to it returns -1. What does this mean? Here is the code that calls...

Create Index Script with Fill Factor Question - I was looking for a t-sql script to create all the indexes on a database.  I found the following link which provided...

I am given a task to troubleshoot performance issue - I am given my first task to troubleshoot a performance related issue. I have a very small DB with some...

Datatype Change - Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ?? How...

SQL Server 2012 : SQL 2012 - General

Trying to get an SFTP process for SSIS - Hi,  I have been looking all over the web for this. I realize that SSIS does not, on its own handle...

SQL Server 2012 : SQL Server 2012 - T-SQL

Parse a string help - I have a string that I need to parse. The text will vary but the format always the same. Example Text: ...

Manipulate a field that contains a specific format - Hi, Please can anyone assist with my SQL problem? Here's an example of the contents of a single field: abcdef 01/01/2018 08:15:13 This is...

SQL Server 2008 : SQL Server 2008 - General

SQL Agent Job - Carry on Failure - Hi I have script that I run to fix orphaned user accounts on a instance - If I run the script in...

SQL Server 2008 : T-SQL (SS2K8)

Generate UserName from Existing table - Hi Guys, I have one new challenge to generate UserName from the table, below i provide some sample data CREATE...

SQL Server 2008 : SQL Server 2008 Administration

dbWarden questions - Hello -- I am trying out the dbWarden application by running it within the 2008 Standard R2 SQL Studio application. It...

Programming : General

T-SQL or Standard SQL - Hello SSC, I am currently reading through "Querying Microsoft SQL Server 2012" (for the 70-461 Exam).  The book suggests to use...

Data Warehousing : Integration Services

Refresh flat file source columns from change in Foreach Loop Container - I have an integration that works great and took a while to get it setup to work properly.  I now...

Data Warehousing : Strategies and Ideas

Typically, how often does an ETL process affect (through an update or an insert) a Fact table as a result of activity in a Dimension table? - Hey guys, Thanks in advance for your help. I'm going to try to describe the above situation, and it may conclude...

SQL Server 7,2000 : T-SQL

can I query for a list of linked servers? - I have a set of related dbs and I need to do some cross-db queries and I want to do...

Microsoft Access : Microsoft Access

How do you have an append query run automatically based on a new record in table? - Hope I explain it clear enough for people to understand what I'm trying to accomplish. I have tbl1 that gets populated...

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 ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com