SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

Where's the schema?

Across the last few years, I've read many articles and listened to quite a few talks that discuss the advantages of NoSQL databases. I'll admit that I'm often skeptical of the advantages of other datastores overcoming the disadvantages with a relational system, but I try to keep an open mind. I do appreciate that there are some benefits to using another data store in certain situations.

One of the talks I heard recently discussed the fact that in many of these stores, we can add data in a "schemaless" fashion, and it's stored in a flexible format that allows the developer to quickly capture the data they are using and retrieve it without requiring up front design work to build a particular format.

That had me pondering the question of whether or not here really are schemaless data structures. If a developer  (or whatever SDK or framework they use) looks to persist come data, clearly there is a format of sorts, which means there is a schema. That schema might not be transferred or persisted in the data store, but there is some schema they expect, both on storage and retrieval. Whether this is a JSON, XML, some proprietary structure, or something else, there's an known structure that the developer uses to work with the data.

Is there really schema-less data? I tend to think no. All of the data we have contains some schema. That schema might vary from row to row, which is often what developers like when building applications. There is, however, a structure. The developer knows it, and must serialize and deserialize the data, or depend on some library like ADO.NET to do so. This often appears to a developer to be a lower barrier to entry. There's less complexity and often no need to map the objecct-like structure of properties to some relational schema and make decisions on sizes.

That's not completely true, as the schema of the data still exists and must be persisted in the application. There is code that must handle the various values stored in some hierarchical fashion. If this changes over time, as values are added, the the application must deal with the missing values in older properties or arrays. If items are removed in the application, then would older sets of data just disappear? Perhaps, but the developer must make a decision, which may have implications for users of their application. This doesn't even deal with the issues of aggregation and reporting, which might force other systems to implement the same schemas and business logic. Those rules and specifications don't easily transfer from one application to another, especially when different teams or developers are involved.

There's always a schema, and the rules have to be implemented up front, or later on. Whether you use a RDBMS or a NoSQL store, you are going to be dealing with a schema. The question is do you want to deal with it in a central location or in every application? I lean towards the former, but you might prefer the latter. Neither is wrong, but you should be sure you understand all the advantages and disadvantages of your choice.

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 ( 4.1MB) 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.

ADVERTISEMENT
GDPR

How to make your SQL Server development GDPR ready

Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. Find out more about how our tools help with GDPR compliance

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

 

Importing Tricky CSV Files with PowerShell

Stewart Campbell from SQLServerCentral.com

A means of importing large csv files (where there may be commas in quote-delimited free text) into SQL Server tables using PowerShell More »


 

What is SQL Change Automation?

William Brewer explores the features of Redgate's SQL Change Automation More »


 

Azure SQL Database and GDPR Compliance

Additional Articles from Database Journal

In recent years, technology landscape has undergone dramatic changes, driven primarily by cloud computing and a continuously increasing level of attention dedicated to security, privacy, and compliance. One of the more significant initiatives that attempts to address these challenges is General Data Protection Regulation (GDPR. In this article, we will explore how Azure SQL Database could help with addressing the GDPR requirements. More »


 

From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 109 – as Timeline)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the as Timeline visual. The as Timeline is a Gantt chart... More »


 

From the SQLServerCentral Blogs - Alter Multiple Databases at Once

Matthew McGiffen from SQLServerCentral Blogs

This is a quick and dirty method I often use when I want to make a change to multiple databases... More »

Question of the Day

Today's Question (by Steve Jones):

What does this code return? (answers in order of the SELECT).

DECLARE @a INT = 5 ,
        @b INT = 10;
SELECT @a = @b ,
       @b  = @a;
SELECT @a ,
       @b; 

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: T-SQL.

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

Extending SSIS with .NET Scripting: A Toolkit for SQL Server Integration Services

Extending SSIS with .NET Scripting is a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations helps you develop your own scripting solutions, but this book also shows a broad arsenal of readymade and well-documented scripting solutions for common problems. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Which of these are database scoped configuration options starting in SQL Server 2016? (choose 2)

Answer:

  • Legacy cardinality estimation
  • MAXDOP

Explanation:

There are a number of items that can be set as Database Scoped Configuration items. They are:

  • Clear procedure cache.
  • Set the MAXDOP parameter to an arbitrary value (1,2, ...) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (such as for reporting queries).
  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • Enable or disable parameter sniffing at the database level.
  • Enable or disable query optimization hotfixes at the database level.
  • Enable or disable the identity cache at the database level.
  • Enable or disable a compiled plan stub to be stored in cache when a batch is compiled for the first time.
  • Enable or disable collection of execution statistics for natively compiled T-SQL modules.
  • Enable or disable online by default options for DDL statements that support the ONLINE= syntax.
  • Enable or disable resumable by default options for DDL statements that support the RESUMABLE= syntax

Ref: Database Scoped Configuration - 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

SQL alert : 15 SQL errors. - I setup some SQL alerts in SQL out of box alerts.  No 15 is SQL Errors. I got the alert in...

Jobs running indefinitely - Hi, I have an sql server 2017 enterprise and sometimes (not always), when it runs a job (the jobs he...

Database backup without Filestream data - Hi, My database size is 250 GB, big size due to filestream data. How can take database backup and restore without filestream...


SQL Server 2017 : SQL Server 2017 - Development

Need to display a column with deleted overlapping dates - I want to get rid of the following overlapping records from the table and display the below output. Also, I...


SQL Server 2016 : SQL Server 2016 - Administration

SQL SERVER STANDARD 2016 INSTALLATION FAILURE - SQL Server Standard 2016 setup fails with the following error. Any help in resolving this issue will be very much...

SQL Server 2016 - Tran log growing huge - Forum, We recently deployed a new server SQL 2016 (first)and built an Availability group, now the problem is the TRN log...


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

SELECT WHERE produces strange results.... - Hello: If I enter the following, I get data: SELECT TOP (1000)   ,   ,   ,   ,   ,   ,   ,   ,   ,

Inside Microsoft SQL Server 2008:T-SQL Programming - new release? - Hello, Does anyone know something about new release of "Inside Microsoft SQL Server 2008:T-SQL Programming", if at all?  Thanks


SQL Server 2014 : Administration - SQL Server 2014

Weird Backup Behavior - Hi Guys, Has anyone noticed on sql 2014 that if you stop a full backup after starting it, that the next...

Point in time recovery with backups from different sources - Users came to me recently with a corrupted database after server crash. They have a full backup from a 3rd...


SQL Server 2012 : SQL Server 2012 - T-SQL

Getting two different fields from two different tables - I have a procedure which takes two parameters and I want to get the primary key values of those two...

How to use functions to update columns? - I am a beginner with using functions, I need to update only one column (which is the FIRST null it...

Phone Number Validation - Greetings, I'm working on a project where we have to take a list of unique phone numbers in our database and...

Insert Trigger help with syntax and using "inserted" - Hello, This is my first time trying to write a trigger. I have two tables, a Jobs table and a Jobs_aggregate...

Index scan operator - Dear All, I have a query which has an Index scan operator and I wanted to know how to change it...


SQL Server 2008 : SQL Server 2008 - General

Trace shuts sql service down - Greeting All, I've come across a server that, when I start a trace, within moments the SQL service shuts itself...


Reporting Services : Reporting Services

Highlighting of changeable data using a "colour table" - I am a lone, and originally accidental, DBA, so would very much appreciate feedback on a solution I have come...


Programming : Powershell

Move files without moving subfolder files - I have been tasked with building a powershell file mover to move files (which could have any name and possibly...


Data Warehousing : Integration Services

Is combining multiple SQL Server Database and Integration Services projects in one master solution a bad idea? - tl;dr:  How do you organize your multiple SSDT projects that comprise a single solution? I've got a DW with these logical...


SQL Server 2005 : Administering

what is difference between restore and recovery? - can any one reply me please

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