Usage of CTE - Trick with Dates (SQLServerCentral 3/3/2017)

Klicken Sie hier, um Sqlservercentral.com zu abonnieren

Laden...

7 jahre vor

Html
Text

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

Featured Contents

Usage of CTE - Trick with Dates SSIS Catalog Compare v2.0 Launch Event Clone a SQL Server login and password to a new server Is Your Database App Ready for the Cloud? (From the SQLServerCentral Blogs) I Don’t Understand (From the SQLServerCentral Blogs)

Featured Script

Corrupt Page Notification

The Voice of the DBA

Null Defaults

One of the things that becomes important in a distributed, team environment is the interface that you present to other systems. In a Devops environment, we really need to have an API for every system, including the database. This means a contract for our database, that spells out what access points are available and what they return. For our data, this often means the structure and shape of a table.

Our tables often aren't static. In fact, they grow and chance over time. This means that the values returned from the table, or even views or stored procedures referencing the table, will change over time. In some sense, this means we're really versioning our API. If that's the case, then as you make additive changes, you'll be adding columns for the most part. The will be time when your application, or maybe a subset of your applications, will not know how to provide data for those columns. In those cases, we need to make a decision about how to handle the column.

What's your default choice? Do you use NULLs when you aren't sure? Perhaps you choose some magic value, such as a blanks or a known date? I know some people like to use 1900-01-01 to mean an unknown date, or even a number such as 99999. Any choice has advantages and disadvantages, and what works for one person might not work for another.

Let us know your default choice and why. I'm sure some of you have great reasons for why you choose a value, and I bet more than a few of us learn something about why one value might be better than others. I'm looking forward to reading your thoughts.

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.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

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

NEW SQL Clone - version 1 now available!

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.

Question of the Day

Today's Question (by Steve Jones):

I want to use ALTER SERVER CONFIGURATION in SQL Server 2016 to set process affinity in my processors. What are my options?

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

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

Professional Microsoft SQL Server 2014 Integration Services

Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

How can I use OBJECTPROPERTY() to determine the number of triggers a table has?

Answer: Query multiple times for each type of trigger's count property

Explanation:

Each type of trigger has a property that returns the count of that type of trigger. These properties are:

TableDeleteTriggerCount TableInsertTriggerCount TableUpdateTriggerCount

You can query for each of these and sum the values.

Ref: OBJECTPROPERTY - click here

» Discuss this question and answer on the forums

Featured Script

Corrupt Page Notification

Henrico Bekker from SQLServerCentral.com

Create the trigger after modifying your mail profile settings, and any additional attachment types if you wish.

Simple technique to ensure you are informed if any content is written.

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

SQL Server 2016 DBA Training - Hi people, I've been working as a SQL developer for a number of years, with an increasing amount of DBA work...

RDP into SQL server BAD or not - I am just qurious if I overlook somthing, keep asking why..... I started doing some BI work recently and like to RPD...

SQL Server 2014 : Administration - SQL Server 2014

Can you set up AlwaysOn between two different clusters? - I have Cluster1 with 2 nodes and a whole different Cluster2 with 2 nodes. Can I set up AlwaysOn between...

Windows Authenticaion vs. Mixed Mode - Greetings: I'm reading Peter Carter's book 'Securing SQL Server' and there is a brief discussion about Windows Authentication vs. Mixed Mode. Currently...

Performance improves after reboot - I am an ETL Developer who was asked to look at SQL Server performance problem. We have two DBA's but they're...

SQL Server 2014 : Development - SQL Server 2014

top 1000 added to linked server select taking forever to run - We have a mysql database that we are referencing in sql server via linked servers. Our report guy have a...

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_TagAlongs". The conflict occurred in database "sanroqued", table "dbo.Inventory". - Insert into Inventory_tagalongs (itemnum, store_id, tagalong_itemnum, quantity) select itemnum, store_id, tagalong_itemnum, quantity from tagalonglistsq I do not understand why i am getting...

Indexing Small Temp Tables used to JOIN based on a value being in a range... - Just wondering if there's much I can do to improve a query that has to join about 70K rows to...

SQL Server 2012 : SQL 2012 - General

Trying to figure out why a trigger fired - we have 2 triggers on one of our SQL servers. One checks for one of 4 logins and 4 apps...

SQL Server 2012 : SQL Server 2012 - T-SQL

CREATING A NEW TABLE FROM AN EXISTING TABLE - Hi guys, I am trying to create a new table from an existing table and I am using the code...

Number of integers where digits are not repeating. - --------- This is for fun only --------- --------- This is for fun only (and for my education) --------- --------- This is for fun only &nbsp

Set query order by comparison result of two fields - I have a dataset like below: CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) NULL, (20) NULL, NULL ) ON...

TRIGGERS ON VIEWS - I have created a view which as you know pulls information from base tables. Now this view stores live information pertaining...

XML Shred skips around if node is missing expected elements - Hello, I have bulk imported xml to a local table called CAQH_Return_XML. as a blob so that I can then shred it.....

SQL Server 2008 : SQL Server 2008 - General

Need urgent help for creating index on subscriber replication server -

Change sql server collation - Hi Guys .. I would like to change sql server collation . Can I just reinstall sql and change the collation during...

sqlcmd error when executed from bat file - Hi All, Any ideas why I am getting the following error below? It is being executing in a batch file locally...

SQL Server 2008 : T-SQL (SS2K8)

Disable and enable foreign key constraints - SQLSERVER 2008 What is the best way to disable foreign key constraint and enable it back. I have to delete data from...

Reporting Services : Reporting Services

Using IN in a Where statement with and option of all - I'm trying to use a Where TS.Skill IN (@TS) and it works fine. :) The user can select one or any number of options...

SQL Server 2005 : SQL Server 2005 Integration Services

Rename File in SSIS - I need to rename a file in SSIS. Part of the file rename should include the current date. I am trying...

This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected].

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.

Teilen auf

Laden...

Weitere Newsletter von Sqlservercentral.com

A Trigger... just not that kind Sqlservercentral.com
Vor 20 Stunden
Configuring Maintenance Plans in SQL Server (20 24-02-21) Sqlservercentral.com
Letzten Freitag um 08:17
How to Resolve SQL Server Database Stuck in Rec overy Pending State (2025-02-19) Sqlservercentral.com
Letzten Mittwoch um 08:13

Weitere Newsletter von Sqlservercentral.com

Laden...

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

Featured Contents

Featured Script

The Voice of the DBA

Null Defaults

The Voice of the DBA Podcast

Featured Contents