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

A Poor Data Model

Recently there was some online complaints about social security numbers (SSNs) in the US being duplicated and re-used by individuals. This is really political gamesmanship, so ignore the political part. Just know that social security numbers appear to be one of the contenders used in many data models.

I found a good piece about how SSNs aren't unique, and have a mess of problems. Despite this, many people seem to want to use SSNs as a primary or alternate key in their database systems. They also aren't well secured in many systems, even though we should consider this sensitive PII data.

As we have moved many analog systems to digital ones, we often find that our assumptions about the rules governing data aren't that well defined. I've worked in many systems where data elements were assumed to be unique in some way, but they actually weren't. I've seen invoices, POs, and other "unique numbers" actually duplicated because of simple mistakes by humans. When we try to enforce uniqueness in a database, we end up with problems. Often we actually need to drop keys and make exceptions because the data must be entered.

This has led many people to not create unique constraints or even foreign keys in their systems, and I understand why they don't. There are real problems when we assume the real world has the same strict structures we implement in code. I've seen systems go online and then FKs removed because of poor data quality. It might be a mistake, but it's also a reality when we find there is existing child data without a parent. We might create a pseudo-parent at times, but we might also decide not to do so if that creates other problems.

I suspect over time the real world will migrate some of their problematic keys to something more robust. However, some that are used widely in older systems, like SSNs, are unlikely to change in my lifetime. There are far too many places where this is in use and I am not sure that there is any consensus to undertake the massive amount of work to implement something else. I suspect even trying to add digits to the value is a task we'll put off indefinitely.

Be careful of using natural keys in your data models unless you are sure they are really a natural key. To me, I'm better off with some surrogate key in the event that my "natural key" turns out to not have the uniqueness I expected.

Steve Jones - SSC Editor

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

 
  Featured Contents
SQLServerCentral Article

Determining Whether Home Addresses Fall Within a Tornado Polygon Using Spatial Functions

Abhinay Malipeddi from SQLServerCentral

Geographic information systems (GIS) play a critical role in emergency response planning and risk assessment. One of the key challenges in this field is determining whether a specific location falls within an area of interest. This capability is especially valuable for property and casualty (P&C) insurers, who need to assess their insured property exposure when severe weather events such as tornadoes occur.

Technical Article

Improve Query Performance when SQL Server Ignores Nonclustered Index

Additional Articles from SQLServerCentral

Do you wonder why SQL Server ignores an index? Do you think about how you can deal with this problem? When you include too many columns in a query you decrease index selectivity. So, if you write a query like “Select * From …” the chance of choosing your nonclustered index will decrease.

Blog Post

From the SQL Server Central Blogs - Monday Monitor Tips: VLF Alerts

Steve Jones - SSC Editor from The Voice of the DBA

A recent change made to Redgate Monitor to add a new alert for VLF count. This post looks at the change. This is part of a series of posts...

Blog Post

From the SQL Server Central Blogs - Azure SQL offerings

James Serra from James Serra's Blog

There are three Azure SQL products with so many different deployment options, service tiers, and compute tiers that it can get quite confusing when choosing the right option for...

Introduction to PostgreSQL for the data professional

Introduction to PostgreSQL for the data professional

Site Owners from SQLServerCentral

Adoption and use of PostgreSQL is growing all the time. From mom-and-pop shops to large enterprises, more data is being managed by PostgreSQL. In turn, this means that more data professionals need to learn PostgreSQL even when they have experience with other databases. While the documentation around PostgreSQL is detailed and technically rich, finding a simple, clear path to learning what it is, what it does, and how to use it can be challenging. This book seeks to help with that challenge.

 

  Question of the Day

Today's question (by MMartin1):

 

Dateadd with a numeric value

What is returned from the following query ?
declare @currentDateTime DATETIME = '2025-01-01 00:00:00.000' select dateadd(hour, 3.0/2, @currentDateTime); 

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

 

 

  Yesterday's Question of the Day (by Alessandro Mortola)

Rows estimation with OPENJSON

Consider the following script for a Sql Server database with Compatibility Level at least 130 (Sql Server 2016):

create table tjson ( id int primary key, j1 varchar(max), j2 varchar(max), j3 varchar(max)); insert into tjson (id, j1, j2, j3) values (1, '[{"c11":"value11A", "c12":"value12A"},{"c11":"value11B", "c12":"value12B"}]', '[{"c21":"value21A", "c22":"value22A"},{"c21":"value21B", "c22":"value22B"}]', '[{"c31":"value31A", "c32":"value32A"},{"c31":"value31B", "c32":"value32B"}]');

How many rows does the Query Optimizer estimate for the following query?

select id, c1.c11, c2.c21, c2.c22, c3.c31, c3.c32 from tjson cross apply openjson(j1) with( c11 varchar(50) '$.c11', c12 varchar(50) '$.c12') c1 cross apply openjson(j2) with( c21 varchar(50) '$.c21', c22 varchar(50) '$.c22') c2 cross apply openjson(j3) with( c31 varchar(50) '$.c31', c32 varchar(50) '$.c32') c3;

 

Answer: 125000

Explanation: Although not officially documented, SQL Server uses a fixed estimate of 50 rows for the OPENJSON table-valued function. You can have a look at the following links related to this topic:

Considered that the tjson table has only 1 row, that every time OPENJSON is applied multiplies by 50 and that there are three OPENJSON functions in the query, the final estimate is POWER(50, 3), that is 125000.

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.


Development - SQL Server 2014
Different number of records returned from SSMS and C# ExecuteReader - I am using the EXACT same query in SQL Server Management Studio (2014) as I am in my C# program, but I get different results. How is this possible? SELECT tblWTHistory.Date, tblWTHistory.EstCompDate, tblOperator.BadgeID, tblOperator.OperatorName, tblWTHistory.WONum, tblWTHistory.WOType, tblWTHistory.PartNum, tblWTHistory.CellReceivedQty, tblWTHistory.OpNum, tblWorkCenter.WorkCenterName, tblWorkType.WorkTypeName, tblWTHistory.WorkQty, tblWTStatus.StatusName, tblWTHistory.ReasonID, tblWTReasons.ReasonName, tblWTHistory.Rework, tblWTHistory.OpComment FROM tblWTHistory INNER JOIN tblWTReasons ON tblWTHistory.ReasonID = […]
SQL Server 2019 - Administration
Column Encryption/Exporting Windows Certificate - So random question about Windows Certificates -- I administer and develop a SQL Server DB App -- Back End is on an Azure Server/Front End is MS Access, about 35 End Users. We employ column encryption on a handful of fields, and re encrypt every 3 months or so.  When this encryption is done by […]
Sync SQL Server and Kafka in real time - Hello, Let say, my .NET API has been inserted successfully into SQL Server Database. It is possible, this data also inserted in Kafka in real time ? This transaction should be done in .NET API or should be done in SQL Server by configuration ? Please help me to understand more and better
SQL Server 2019 - Development
Unable to delete records from table - Hi, Im unable to delete records from a table.Im a little surprised as have not witnessed this. The SQL server runs on MSSQL 2019 RTM. I use the below queries. select top (5) * from Journal where createddatetime<'2006-01-01' -- To figure out the records below year 2006 Fetched the 5 top queries Delete from Journal […]
Is anybody using Windows 2022 clustered SQL Failover cluster ( 2019 CU30 ) - How do you configure the cluster resources to be able to use Distributed Transactions ? Documentation ( 01/15/2014 ) is unclear and doesn't show a working solution. Begin Distributed Transaction fails ! Msg 8501, Level 16, State 3, Line 12 MSDTC on server 'VirtSQLServer\MyInstanceName' is unavailable.
Query Help to show record in single row - Hi All, need help with an SQL query. We have an existing table that's already formatted this way, and I am having trouble extracting the data in single record. Current Output: EXPECTED OUTPUT: The EndDate and EndTime I want it displayed in the same row where BName like '% Start' I tried to do this through […]
MSDTC not available on Windows 2022 clustered SQL 2019 CU30 - I connect to this clustered instance and run : begin distributed transaction Msg 8501, Level 16, State 3, Line 2 MSDTC on server 'ClusteredTargetServer\Instance' is unavailable. I have configured the clustered msdtc of this instance:   Test-Dtc -LocalComputerName $env:COMPUTERNAME -Verbose VERBOSE: ": Firewall rule for "RPC Endpoint Mapper" is enabled." VERBOSE: ": Firewall rule for […]
General
What Version MS SQL Server and SSMS Am I Running? and Git with MSSSQL - Information I've run across searching other topics led me to want to find out what version I'm running.  This was motivated by trying to learn if git/github would be good tools for version control on my database which led to the question of version. I found an article that told me to run SELECT @@version.  […]
Several Questions: Relationships and SSMS and Data Diagrams - I'm struggling setting relationships among tables.  I understand the one-to-many, many-to-one, many-to-many, and self-referential concepts.  It's implementing them that I find a bit vexing. I've used queries like this successfully: ALTER TABLE clan.parents ADD CONSTRAINT FK_FatherID_PeopleID FOREIGN KEY (parentsFatherId) REFERENCES clan.people (peopleID); How can I view relationships after I've completed queries like the above?  After […]
SQL Server 2022 - Administration
Backup taking too much time - Hi Experts, One of my Large database having about 4TB in size is taking almost 3 days to complete the backup . To reduce the backup I stripped the same to 4 different cluster drives and still its taking the same time. The data stored is all documents and it uses filestream. Below are the […]
Keeping QUERIES for Using Again and Adding DB to Git/Github and VS Code - While I know that it will speed my learning to type queries as I need them, sometimes I have put other people's queries to good use, queries that I lack the skills to create but will likely use in the future.  Alas, I find no way to store and retrieve queries making them very accessable.  No […]
Found a Tool Very Helpful for Beginner - I struggled with remembering the conventions I'd chosen when creating column names and data types.  Likewise I struggle - continually - with relationships and keeping track of Foreign Key assignments.  While I have not fully resolved the last issue, I stumbled upon a query that will list all the data elements in a database along […]
SQL Server 2022 - Development
After an Oracle migration, write out a function into a View SELECT statement - This is the function oracle.xxxfloat BEGIN IF @first IS NULL OR @second IS NULL RETURN NULL IF @first < @second RETURN @first RETURN @second END Here is the select statement getting column2, where I need to write the function 'oracle.xxxfloat' inline as part of the select so I can phaze out the above function. In […]
Convert stored procedure into a Table-valued function - <sorry, duplicate> =(  
Convert stored procedure into a Table-valued function - Feel like I'm missing something blatantly obvious. I am trying to return the cost of each of a set of ingredients as of a specific date (so I can sum them in the next step, for example). It works fine if I create it as a stored procedure, but when I try to create a […]
 

 

RSS FeedTwitter

This email has been sent to newsletter@newslettercollector.com. 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

 

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