Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Muting the Immutable

This editorial was originally published on Aug 26, 2015. It is being re-run as Steve is on holiday.

The idea of 'static data', 'enumerations', reference data', 'seed data' or 'domain data' is rather alien to relational databases because it is unnecessary to invent such special categories. However, such data is easily accommodated, as are the many other types that the average application programmer will never come across. RDBMSs are naturally good at coping with data that is perceived as having special requirements, importance or significance. However, it is often raised as a problem when building and deploying databases.

A lot of database developers, for example, hit the problem of having data that is, or should be, immutable. You wouldn't, for example, want to change ISO values for countries, but you've got to have them for the database to work. It is basically part of the version of the database. Normally, we just use a post-build script that inserts the correct data into a table, and then, for 'reference' data, give all database roles read-only access to the table. By doing this we ensure that the version of the data is tied to the version of the database and can't be changed. 'Seed' data is treated much the same way but with different user-access.

However, this doesn't suit some build systems, such as SSDT. It makes the mistake of believing that a build should contain only DDL. It restricts the users to writing only DDL build scripts and spits out any DML it finds. It is an awkward restriction. We can insert the necessary static data as a separate post-deployment DML script, added to the DACPAC, but there is no User Interface to make it easy. We are at the level of .NET and DACfx, papering over the cracks.

There are, of course, ways round this problem. For example, it isn't hard to create a view that, in its DDL script, contains data

CREATE VIEW TheDaysOfWeek
AS
    SELECT  DayNumber ,
            TheDayName ,
            'Cornish' AS TheLanguage
    FROM    ( VALUES ( 1, 'dydd Sul'), ( 2, 'dydd Llun'), ( 3, 'dydd Mawrth'),
            ( 4, 'dydd Mercher'), ( 5, 'dydd Iau'), ( 6, 'dydd Gwener'),
            ( 7, 'dydd Sadwrn') ) f ( DayNumber, TheDayName );
GO

You won't be able to change the data without re-creating the view. This, of course, can get unwieldy and 1000 rows is a practical limit. However it is surprisingly efficient where the number of rows is reasonable. The point is that the data is in the source DDL of the object and so, again, the version of the data is automatically tied to the version of the database. However, though this helps 'enumerations' or 'reference data', this doesn't solve the problems of 'seed' data in SSDT.

There are plenty of ways of circumventing local restrictions of what you can, or can't, do in the database build process, but it would be much better if these arbitrary restrictions weren't there in the first place. The build script must provide whatever is necessary for the data. If we understand the data, its restrictions, ownership, security, constraints, mutability, and so on in all its richness and variety, and deal with this in the build script, then so many such problems just seem to evaporate.

Phil Factor.

Phil Factor

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

Redgate SQL Monitor
 
  Featured Contents

Vagrant and SQL Server 2017 on Linux

David.Poole from SQLServerCentral.com

Vagrant provides a mechanism to build and configure a virtual Linux box running SQL Server 2017

Simple SQL Change Automation Scripting: Connections, iProjects and Builds

Additional Articles from Redgate

Phil Factor delves into some of SQL Change Automation's data objects, or artifacts, covering the DatabaseConnection, iProject and iBuildArtifact objects. He explains the useful information they contain and how to use them in a PowerShell-automated database delivery process.

Welcome to the DBA Training Plan.

Additional Articles from Brent Ozar Unlimited Blog

8 years ago, Brent launched an email series with a 6-Month DBA Training Plan. he sent one email per week, bringing people up to speed on the most important topics that nobody taught ’em along the way. It’s hard to believe it’s been 8 years! This month, he's revisiting the emails, updating their content, and publishing ’em as blog posts too to make ’em easier to find. Buckle up: here come 24 straight blog posts to take you from zero to…well, at least a hero who’s smart enough to wear the underpants on the inside.

From the SQL Server Central Blogs - SQL Homework – August 2019 – Getting the data out

Kenneth.Fisher from SQLStudies

Everything else aside two of the most important tasks of a database are to get data in, and get data ... Continue reading

From the SQL Server Central Blogs - Copy Files to a Docker Container

Steve Jones - SSC Editor from The Voice of the DBA

One of the things that’s handy when working with containers is being able to move files in and out of the container. Certainly you can do this from a...

 

  Question of the Day

Today's question (by Evgeny Garaev):

 

Resumable online index rebuild

In which version of SQL Server did Microsoft introduce resumeable online index rebuild?

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Dropping the External Table

I have created this table in one of my Azure SQL Databases:

CREATE EXTERNAL TABLE MyTable
( SomeKey int
, SomeValue varchar(100)
)
WITH (DATA_SOURCE = OtherDB);

Now I want to remove it. How do I do this?

Answer: DROP EXTERNAL TABLE MyTable;

Explanation: The DROP command is used, but EXTERNAL TABLE must be specified. This does not affect the data source. Ref: DROP EXTERNAL TABLE - https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-external-table-transact-sql?view=sql-server-2017

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 - Administration
Will SQL Server supports Nepal Calendar - Hi, I have a requirement where i need to store nepal calendar date in a date column in sql table. Nepal calendar is totally different from regular english calendar whereas it has 32 days also in some month. Is it possible to Install SQL with region and calendar settings as Nepal. Thanks in Advance! Chelladurai […]
SQL Server 2017 - Development
OPTION(QUERYTRACEON 9481), Dynamic SQL & DBCC TRACEON error. - I'm in a bit of an odd-ball situation...  a) We are in the process of upgrading all SQL Server instances to 2017. b) More than a few of the impacted databases are using legacy Compatability Levels/Cardinality Estimator. c) We would like to have all databases on the current (2017) CL and using the most recent […]
SQL Server 2016 - Administration
Windows Failover Cluster - Hi All, I have an SQL 2016 AlwaysOn AG setup. There are 3 nodes in total (2 on prem, 1 in Azure) and a FSW in another DC. We had some network communication issues this weekend and the FSW went offline but eventually came back online. Since then, we have gotten an alert from Veeam […]
Monitoring SQL Server - Due to some limitations we may have to monitor all of our sql servers with two different monitoring tools. Would that we bad idea and what other issues i could come into?
Full Text Index Population - Hi All, We have situation where we have a document DB with a Full-Text Index storing approx. 6 million rows and a document was uploaded, but, was not indexed. On further examination we noticed a number of errors in the FT log, so, after trying to address the errors without success, we dropped the index […]
SQL Server 2016 - Development and T-SQL
Performance Debate - Looking for Opinions on if this area of a C# application should be modified in how it hands things off to the DB. The table in question has two columns (ListId,PersonId)   there can be multiple PersonID with different list ID's The UI of the application allows you to go in and view all these Lists […]
better DB Design tools? - I am using Visual studio to design databases for applications / data warehousing rather than just using the SSMS designer like I did in the past, but now I really miss the ability to quickly see relationships etc from a diagram.  We are using visual studio Sqlproj so that we can get the benefits of […]
Row number not exactly working - Using row number function with partitioning, I do not get the desired result. Attached image shows what the code is outputting at the moment. The last column (Expected) is what I need the values to show. Rows 1-3 show correct row number (RID). Rows 4-17 show correct row number (RID). Row 18 is the problem, […]
Group by on 120 pivotted columns - Hi, I have the next case with a challenge on grouping records on 120 pivotted columns. Objectnames are in Dutch but hopefully that is no problem. The records in our source table have questionaire outputs for patients in our hospital. Every record holds the answer of one question in a questionaire. The questionaire that we […]
Administration - SQL Server 2014
Log File Growing on Replication Subscriber - The title really says it all here.  I've seen a lot of references as to why the log could grow on the publisher but I can't really find anything that explains why it would happen on the subscriber. Our data warehouse server holds copies of several of our production databases all of which are in […]
Is it possible to use the Profiler on a SQL Server 2014 Web/Express edition? - Hello, I'm a bit confused as to whether or not it is possible to use the SQL Profiler on the Web/Express edition of SQL Server. It says "No", but with this foot note: SQL Server Web, SQL Server Express, SQL Server Express with Tools, and SQL Server Express with Advanced Services can be profiled using […]
Development - SQL Server 2014
SQL Collation different in tables and columns - Hi, I'm getting an infuriating issue where I have my default collation of the server set to SQL_Latin1_General_CP1_CI_AS. For some unknown reason some tables in different databases have a different collation AND some columns within those tables have a different collation again. It's driving me mad. Any idea how I can stop this from happening? […]
SQL 2012 - General
BUY HIGH QUALITY COUNTERFEIT CURRENCIES MONEY AT (WHATSAPP +13232503649) - BUY HIGH QUALITY COUNTERFEIT CURRENCIES MONEY AT (WHATSAPP +13232503649)AND PASSPROT,ID,DL AND VISA WITH IELTS CERTIFICATE WITHOUT EXAM. ALL DATA BASE REGISTERED FOR BUYING REAL DOCUMENTS. PASSPORTS,ID CARDS,GREEN CARDS AND DRIVERS LICENSE ETC. Email: wultesiove123@gmail.com WEBSITE:buynuiquebanknotes.com WhatsApp: +13232503649 Why would you buy from us? Our banknotes contain the following security features that make it to be […]
SQL Server 2012 - T-SQL
Restore script enhancement - Hi, Couple of question with below restore script.  This is an existing script and I am trying to understand and improve it. Please share your suggestions. The databases are 3 - 5 TB in size so any performance tips are also welcome. USE [master] GO DENY CONNECT SQL TO [ROLENAME] ----- Q) is it to […]
SSDT
Organising DTSX packages - Hi all   We have a lot of DTSX packages in our solutions. For example, we have a MASTER package that calls other packages to do the actual work (and these may, in turn, call other packages to break the work down into smaller chunks).   Unfortunately, all the packages in Visual Studio that appear  […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -