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

Data Modeling in a New World

The world is changing in some fundamental ways as we recognize and deal with some of the social changes that are occurring regularly. We have had new countries appear and old ones disappear in my lifetime. Borders are redrawn and political systems change. Many of those are just data changes that we can import from some recognized source.

There are other changes that might change the way we model our database schemas. One example is the concept of gender, which has changed from the simple male and female of my youth. I haven't thought much of this, and many of you might not have as well. No matter what your opinion of gender fluidity is, you should still understand how this might impact software and your database systems.

I was thinking about this while reading an article on designing forms for gender diversity. While the form is important, equally important is how we handle this in the database. A single character might not be enough, but even if you encode various values, what do you use? I haven't seen a standard and likely this area of data capture is still evolving.

I suspect that ISO/IEC 5218 needs to be updated, and until it does, I think many of us that work on schema models ought to think about keeping a list. The UK has recommendations, but they don't appear to have much granularity for the different ways people identify. New Zealand is slightly better, but this does appear to be a thorny question from the modeling perspective.

Much of what we capture gender for is some marketing purpose. This allows data analysts to better understand behavior as a person's gender sometimes provides some understanding of other behavior. While adding in new pronouns, titles, and genders can increase complexity, it's also important from the standpoint of having data that is useful.

With that in mind, it makes sense to not use hard coded constraints in tables, and ensure we have a flexible data model that handles the data we are likely to receive, including some sort of not applicable or prefer not to say answer. As data modelers, our goals should be to accurately capture and record data, and that means we need to adapt to a changing world.

Steve Jones - SSC Editor

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

Redgate SQL Prompt
 
 Featured Contents

Gap Analysis Using Algebra

Glen Cooper from SQLServerCentral

Editor: Thanks for this article. I don't see the definition for fnBasic. I see discussion about it, but  not the actual definition. 

Meeting your CCPA needs with Data Classification and Masking

Additional Articles from Redgate

This article will explain how to import the data classification metadata for a SQL Server database into Data Masker, providing a masking plan that you can use to ensure the protection of all this data. By applying the data masking operation as part of an automated database provisioning process, you make it fast, repeatable and auditable.

Analyze SQL Server Results in Excel with Conditional Formatting

Additional Articles from MSSQLTips.com

Learn how to format your SQL Server results in Excel using conditional formatting to quickly identify patterns and issues.

From the SQL Server Central Blogs - SQL Database MI benefits

James Serra from James Serra's Blog

As I see a huge number of customers migrating their on-prem databases to the Azure cloud, the main question they ask is about whether they should go with an...

From the SQL Server Central Blogs - Data Privacy – Playing your part

Martin Catherall from measure twice, cut once.

Tuesday 28th January 2020 was “Data Privacy Day 2020” Some may refer to this as Data Protection Day, but it is really just a day to draw attention to...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The Triple Update

What happens with this code? 
CREATE TABLE RaceResults
(   RunnerID     INT CONSTRAINT RaceResultsPK PRIMARY KEY
  , OverallPlace INT
  , PlaceInAge   INT);
GO

INSERT INTO dbo.RaceResults
    (RunnerID, OverallPlace, PlaceInAge)
VALUES
    (1, 2, 3)
  , (2, 2, 3)
  , (3, 2, 3)
  , (4, 2, 3);
GO

DECLARE @i INT;
UPDATE dbo.RaceResults SET @i=OverallPlace= 1 WHERE RunnerID = 1;
SELECT
     @i, rr.RunnerID, rr.OverallPlace, rr.PlaceInAge
FROM dbo.RaceResults AS rr;
GO

DROP TABLE dbo.RaceResults;

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)

The Original Database

In SQL Server 2017, what value does ORIGINAL_DB_NAME() return for a login?

Answer: The value passed in a connection string with the -d parameter or initial catalog parameter

Explanation: This function returns the name of the database that is passed in with the connection string. If nothing is passed in, a blank is returned. Ref: ORIGINAL_DB_NAME() - https://docs.microsoft.com/en-us/sql/t-sql/functions/original-db-name-transact-sql?view=sql-server-ver15

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
AG Listeners - multiple instances - Hi, I'm looking after a cluster that has 2 instances; one for 2016 and one for 2017. The AG listeners are both configured to use port 1433 and everything is working fine? Customer wants a separate instance of 2017 - how do listener ports relate to SQL ports? In the SQL port world named instances […]
SQL Server 2016 - Development and T-SQL
Datatype differences when restoring a database from one server to the next - Hello, I'm performing a backup/restore operation from a SQL Server 2012 database server to a SQL Server 2016 database server. The database is currently set to SQL Server 2008 compatibility within both database servers using the same 'SQL_Latin1_General_CP1_CI_AS' collation from the server level to column level. Now when the database finished restoring on the 2016 […]
Roll multiple records into 1 - I'm trying to roll multiple records into one replacing null value. IF (SELECT OBJECT_ID('tempdb..#Table50'))is not null DROP TABLE #Table50 CREATE TABLE #Table50 ( MyID int, A int, B int, C int) INSERT INTO #Table50 ( MYID, A, B, C) SELECT 1, NULL,NULL, 53 UNION ALL SELECT 1, NULL,NULL, 54 UNION ALL SELECT 1, NULL,924, NULL […]
SQL UniqueIdentifier question - Hello, Our reporting database truncates and loads latest data during each import and generates Unique Identifiers for Tasks and Project UIDs .   Example: ProjectTable TaskTable We have a SharePoint list where incremental updates are done using TaskUID from Task Table and ProjectUID from Project Table. If with every refresh, Project and Task tables regenerates […]
Extract string from string that contains specific first two characters - Hi I would like to extract from a full string the 'XX....' string. This specific string inst in the same position in my full string. - this string always starts with XX - this string always contains 6 characters   thanks in advance,
Administration - SQL Server 2014
Upgrading SQL 2014 to SQL 2017 cluster - Hi All, Not sure whether to post it here or in SQL 2017, but here goes.... We have a Win 2012 R2 / SQL 2014 cluster for a mission critical database. It's a 3 node cluster with nodes in different subnets. As DR we logship to a 4th off site server. We want to upgrade […]
SQL Server 2012 - T-SQL
View Dependencies - I have SQL Server 2012 and running the following query. THe results do not include all dependencies. I have a stored proc that calls a view, but that does notshwo up. How do I get full dependency list? SELECT ReferencingObjectType = o1.type, ReferencingObjectName = o1.type_desc, ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name, ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name, ReferencedObjectType = o2.type, ReferencedObjectName […]
SQL Server 2019 - Administration
MIGRATE SQL 2014 to 2019 (not UPGRADE) - Anyone had experience migrating a SQL Server to the latest version? We have 2 servers, SQL 2014 SP3-CU4 + Windows Server 2012 R2 and would want to migrate both server to SQL 2019 + Windows Server 2016/2019. Have to point out the following: The servers has a transactional replication running. The subscriber server is also […]
SQL Server installation with British English - What little hair I have left is being torn out in chunks trying to get to the right part of Microsoft to get a reasonable response, so I'm hoping you helpful lot here can answer my problem. When I try to install SQL Server 2019 and look at the instance properties, I see it has […]
Availability groups and sql agent - I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node. I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario so my plan was to […]
SQL Server 2008 - General
Continuously current list of attached files in SQL Server - This isn't strictly a SQL Server question, but there are some pretty sharp people on here, and I'm probably not the only one with a problem like this.   I have a database that has associated files in a folder/subfolder structure on the server – Windows Server Standard, 2007, SP2. The associated files are mostly […]
Integration Services
SSIS Class not registered - Hello, I installed SSIS on a server by itself, it is 2016 version 13.0 version I followed permission directions from the link below: https://docs.microsoft.com/en-us/sql/integration-services/grant-permissions-to-integration-services-service?view=sql-server-2014 I tried to connect to SQL management studio version 16.3 version, to SSMS 18.4, and I get the following error message, I attached it. not sure why or what to do […]
SQL Server 2005 General Discussion
Attachments EMail - I'm having trouble trying to get my query results loaded into my excel attachment can anyone help.   DECLARE @sub VARCHAR(100) DECLARE @qry VARCHAR(1000) DECLARE @msg VARCHAR(250) DECLARE @query NVARCHAR(1000) DECLARE @query_attachment_filename NVARCHAR(520) SELECT @sub = 'DC Weekly Transfer' SELECT @msg = 'This is Just a test.' SELECT @query_attachment_filename = 'dcweekly.csv' Declare @nOrgID int = […]
my query results are not in my excell attachment can anyone help - Hello, I have a query here that is sending a excell attachment to a email the only problem is my results are not being put into that attachment also how do I distinguish this when I create a Agent Job what parts do I put where in my agent job.   DECLARE @sub VARCHAR(100) DECLARE […]
General
Trying to Sum by different levels - Hello everyone, If any one could please help me with this? I'm trying to add additional sum by Family and Sum by Store level Trying to get this result It seem that i only know how to use group by at the end of my statement.
 

 

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

 

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