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

Daily Coping Tip

Build a bucket list of things you want to do before you die

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Choosing Sequences Over Identity

When I was building software as a full time job, the choices for automatically generating surrogate keys were the identity property and the NEWID() function. At that time, GUIDs were too cumbersome to deal with, and I often used identity values in tables. I looked for natural keys, but often these were secondary keys for me and I preferred to use numerical values in many tables as PKs and FKs.

In the last few years I've run into a few customers that prefer sequences, which are a separate object in your SQL Server database. There are some challenges with sharded systems and using deployment technologies like SQL Compare, which is one reason I've avoided them.

I was reading Dr. Low's blog recently and he noted that he prefers sequences to identities. It's a good comparison of the reasons why sequences can be easier to manipulate, with some advantages because these are a separate object. I think he makes a good case why we might always want to consider sequences over identity values in new development.

He's convinced me, though now I need to learn a new habit and build new skills to quickly and easily develop sequences for table keys instead of relying on identity values. This is a big change for me, with nearly 30 years of writing identity properties in CREATE TABLE statements.

Learning to adopt new techniques and changing habits of the ways that we grow and change, and certainly how we build better software. That's a tenet of DevOps. Experiment and learn. This is a place where I'll start to grow and see what I think as I build demos and PoCs for customers, giving sequences a chance. I wonder how many of you might rethink using identities in the future as well.

Steve Jones - SSC Editor

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

 
  Featured Contents

How to Resolve a Duplicate Key Error in SSAS

aveek22 from SQLServerCentral

Learn how you can fix the duplicated key error in SSAS without editing the source data.

How to Add a Filter to an Existing SQL Change Automation Project

Additional Articles from SQLServerCentral

Kendra Little demonstrates how to create a SQL Compare filter file and save it to a SQL Change Automation project folder, so that it will be used automatically during development.

Technology Makes a Difference

Additional Articles from SQLServerCentral

As the world deals with the Covid-19 pandemic, technology is making some things better for those of us in isolation. We are also better equipped today to fight the disease. Researchers are using artificial intelligence to quickly develop vaccines and treatments.

From the SQL Server Central Blogs - Stacking Up Datasets in Azure Data Factory

Rayis Imayev from Data Adventures

(2020-Apr-24) Using UNION SQL operator is a very common practice to combine two or more datasets together, it helps to create a single result set from many sourcing ones. Azure Data...

From the SQL Server Central Blogs - SQL Prompt Fixes Always Have an Else

Steve Jones - SSC Editor from The Voice of the DBA

SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide...

 

  Question of the Day

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

 

CASTing Binary Values

I get a binary value from a co-worker that looks like this: 0x00006B61. I am told this is a whole number that is encoded. How should I get the numerical value from this binary value?

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)

May the Fourth 2020

Which of these is not a real vehicle in the Star Wars universe?

Answer: Klingon Bird of Prey

Explanation: The Klingons are from Star Trek, not Star Wars. May the Force be with you. Always. Ref:

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 - Development
Need to Display date wise sales summary. - Hi, I have created a stored procedure like follows select issuedate, SUM((CASE WHEN ISNUMERIC(netpayable)=1 THEN CONVERT(MONEY,netpayable) ELSE 0 END) ) AS TotAmt from tbktym_TicketPrimeDetails where LEFT(CONVERT(DATETIME,issuedate,103),12)>=LEFT(CONVERT(DATETIME,'01-02-2020',103),12) and LEFT(CONVERT(DATETIME,issuedate,103),12)<=LEFT(CONVERT(DATETIME,'03-02-2020',103),12) and cstatus='billed' and branchid=1 group by issuedate The result is showing like 2020-02-03 00:00:00.000 252397.86 2020-02-02 00:00:00.000 500.00 2020-02-01 00:00:00.000 401757.00 2019-02-03 00:00:00.000 119547.00 2019-02-02 00:00:00.000 395913.00 […]
Selecting data between just month and year - I know how to get data between just startdate and enddate, but I need to select it between just month and year, start month is previous July and current year,  so for example, July 2019(startdate) and April 2020(enddate). I am having issues with the between, not sure how to format it. can anyone provide assistance? […]
SSIS Export Help - Hi, Can someone show me (if its possible) how to create an ssis package to take the servername, tablename and todays date as parameters and export this table to a filename with todays date (formatted Filename_ddmmyyy.csv). I've managed to get the dynamic filename but its hard coded to the table and i don't understand how […]
create new table from two others but have consistent date ranges - Hi all, I need your help.  This sounds like a simple problem but it's driving me round the bend. I have two tables that are not technically joined but have a common data column (country) used across the two tables.  Each table has a Start/End date. I am trying to merge the data into a […]
SQL Server 2016 - Administration
AlwaysON cluster suggestion - Hi All, Following is the current setup 2 node OS cluster with AG on different location, primary node if physical and secondary is VM. I would like to move from physical to virtual and from 2 node to 3 node - On primary site 2 node as HA and third node as DR. Which will […]
SQL Server 2016 - Development and T-SQL
Pefromance help for transaction derrivation - Hi, Can anyone help me in fine tuning this or any other suggestions also.I have a table which will have a set of transactions  and reference transactions if any. I am creating a job to purge transactions based on some conditions like date , full completed based on a status etc. If a single transaction […]
Administration - SQL Server 2014
Extended Event To Capture Proc Execution - I configured an extended event to capture the time procedures are executed and below is what I have so far CREATE EVENT SESSION [PROC_EXEC] ON SERVER ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(0) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username) WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master, model, tempdb, msdb, UserDB1, UserDB2'))) ADD TARGET package0.event_file(SET filename=N'C:\PROC_EXEC.xel',metadatafile=N'C:\PROC_EXEC.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO I'm having a few issues […]
Development - SQL Server 2014
I want to find header row dynamically and create table schema with that header. - CREATE TABLE [dbo].[NewTable1]( [18/10/2020 date] [nvarchar](255) NULL ) ON [PRIMARY] GO INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'This is test file') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (NULL) INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'20-20-2020') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (NULL) INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'ID,name,sal') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES (N'10,mahi,2000') INSERT [dbo].[NewTable1] ([18/10/2020 date]) VALUES […]
Convert VBA function to SQL function - I have VBA function need to convert to SQL function. Kindly help me change it. Here is a snippet of the code. Public Function VniToUni(str$) As String Dim VNI$, UNI$, i&, sUni$, arrUNI() As String VNI = "aù,aø,aû,aõ,aï,aâ,aê,aá,aà,aå,aã,aä,aé,aè,aú,aü,aë,AÙ,AØ,AÛ,AÕ,AÏ,AÂ,AÊ,AÁ,AÀ,AÅ,AÃ,AÄ,AÉ,AÈ,AÚ,AÜ,AË,eù,eø,eû,eõ,eï,eâ,eá,eà,eå,eã,eä,EÙ,EØ,EÛ,EÕ,EÏ,EÂ,EÁ,EÀ,EÅ,EÃ,EÄ,í ,ì ,æ ,ó ,ò ,Í ,Ì ,Æ ,Ó ,Ò ,où,oø,oû,oõ,oï,oâ,ô,oá,oà,oå,oã,oä,ôù,ôø,ôû,ôõ,ôï,OÙ,OØ,OÛ,OÕ,OÏ,OÂ,Ô ,OÁ,OÀ,OÅ,OÃ,OÄ,ÔÙ,ÔØ,ÔÛ,ÔÕ,ÔÏ,uù,uø,uû,uõ,uï,ö ,öù,öø,öû,öõ,öï,UÙ,UØ,UÛ,UÕ,UÏ,Ö ,ÖÙ,ÖØ,ÖÛ,ÖÕ,ÖÏ,yù,yø,yû,yõ,î ,YÙ,YØ,YÛ,YÕ,Î ,ñ ,Ñ " […]
SQL 2012 - General
TDE doubts - Hi All, As a part of security compliance are in a process of implementing TDE on all of the prod servers. Server version is SQL 2012 SP4 Enterprise Edition. Followed below steps : -- create Database master key(DMK) in "master" database -- create Certificate in "master" database -- backup the master key and certificate -- […]
SQL Server 2019 - Development
Object naming - I have a pretty "dumb" question, but I want to get the consensus on this   I have a table that is going to store Location data (address, geocoding, etc). Location is a reserved word in SQL Server and I have always lived by "do not pluralize a object name". Anyone have suggestions on naming […]
Constraint in a column - Hi, I have a requirement where my table (ex: TableA having columns Tranno , Trandate, Itemno). Tranno can be repeated in this table as it may have multiple Itemno for one Tranno. But Trandate needs to be only one value for one particular TranNo. Here i need to build a constraint such that for one […]
Analysis Services
Migration from Multidimensionnal Cube to Tabular Model - Hello I'm working on a migration from an on premise ssas multidimensionnal cube to an azure analysis services tabular model. Is there a way , a method or a tool to do it quickly and efficiently? It's a large cube and it will take time to develop it from scratch with tabular model. Thank you […]
Dim trying to process deleted attribute - Hi all   We have a Dim table with an attribute that's causing us problems.   We want to delete the attribute while we work on it but it won't disappear. To delete it, we've found the Dim, opened it and deleted the attribute using right-click/delete. We did uncover an issue where, if you check […]
Integration Services
Dynamic Scheduling of a SSIS Step - Hi All, I need a favor, regarding the below topic: I have an SSIS to update a table on multiple servers and there is a requirement to run the updates (daily/weekly or Monthly) configured in a table on the basis of the different country(CorpRegionID) & Vendor(VendorID). Table structure:   Could you please suggest me the […]
 

 

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

 

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