SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Swapping values of variables

The Voice of the DBA

Data Modeling Information

Data modeling is something that we should all be doing when altering the schema in our databases. I'd like to think that most people spend time here, but I don't think that's the case. I think plenty of people think "I need to store a piece of data" and they pick a string or numeric datatype and start stuffing in values. If in doubt, just pick a string. It's why I think we have lots of dates stored in string columns because that was someone's first thought.

There was a post recently that talked about storing data in its highest form. It was interesting to me because these are the type of decisions I try to make when designing a table. What is the best form in which to store data? The authors talk about picking not only a type that easily converts, but the fields that make it easiest to work with the data in different ways.

I do think that the aggregations or calculations that we need to perform should influence your data type. If you are measuring something, use a numeric. In fact, in their example of movie times, integer is probably the best type. While many databases and languages have time datatypes, some represent a measure of time (timespan), while others represent a clock (T-SQL time). Either might work for movies, but in aggregations, the T-SQL time will have issues beyond 24 hours. An integer is a better choice, assuming we don't care about seconds.

The second part of the post looks at multiple values, in this case customer loyalty points earned and redeemed. A simple running sum is what we might store in a database, though the application class might need two fields. Of course, modern software often totals these things for a customer as part of gamification and inducement to engage more, so maybe a data store would also want to store the title earned and redeemed, with a calculation to show the balance.

The one thing that I might add for developers to a post about modeling is the need to consider operations at scale. While using a bit more or less storage often doesn't matter for any row or any operation on a singleton set of data, when we scale across millions of rows, little things matter. Consider how your data might be aggregated and what happens if you have millions of rows to work on. There a better design decision can out perform a poor one by many orders of magnitude.

That and generate lots of data to test. You ought to know how to quickly mock up a million rows to check your queries.You might have a million rows in production.

Steve Jones - SSC Editor

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

Featured Contents

SQL Server 2012 AlwaysOn Groups and FCIs Part 4

Perry Whittle from SQLServerCentral.com

This is Part 4 of a series on AlwaysOn and FCI integration in SQL Server. In this article we will learn how to add the iSCSI disk storage to our SQL Server nodes and build the cluster.

Firebase and Supabase: Key differences you need to know

Additional Articles from SimpleTalk

Firebase is a NoSQL database, while Supabase is a relational database. The type of software you’re constructing determines which database is best for your project.

PASS Data Community Summit registrations are open!

Additional Articles from Redgate

In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post to learn more.

From the SQL Server Central Blogs - Using Azure Data Factory to read and process REST API datasets

Rayis Imayev from Data Adventures

(2023-Apr-10) Yes, Azure Data Factory (ADF) can be used to access and process REST API datasets by retrieving data from web-based applications.To use ADF for this purpose, you can simply...

From the SQL Server Central Blogs - New Job -> Week 4 -> Set Up Auditing

hellosqlkitty from SQLKitty

This is part of a series of new job blog posts. You can find them all here. The main goal for week 4: set up auditing for all Azure...

SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey

SQL Server Execution Plans, Third Edition, by Grant Fritchey

Grant Fritchey from SQLServerCentral

Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.

Question of the Day

Today's question (by Thomas Franz):
	Swapping values of variables
I have two variables and want to swap / exchange their values, what will be the result of the following statements DECLARE @i INT = 1 , @j INT = 2 -- swap variables SELECT @i = @j , @j = @i SELECT @i AS i, @j AS j
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)

Multiple Defaults

I want to add two columns to one of my tables, but include defaults for them. Can I do this?

ALTER TABLE dbo.Test1 ADD CustCount INT DEFAULT 0, CustName VARCHAR(20) DEFAULT 'N/A'

Answer: Yes, this works fine

Explanation: This works fine. Ref: ALTER TABLE - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#arguments

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

Can not Truncate the database because “Secondary has no log was added” - I got an error when trying to truncate the database: "Database can not shrink until all secondaries have moved past the point where the log was added". When I check the status of the secondary on the primary machine (Always On High Availability Dashboard), it has heath: good and Synchronization state: Synchronizing. But the status […]

SQL Server 2016 - Development and T-SQL

Hierarchy related calculation - Hello all, I have a hierarchy of businesses. example can be created as follows: create table #temp (LeafLvl varchar(20), L1 varchar(20), L2 varchar(20), L3 varchar(20), L4 varchar(20), L5 varchar(20)) insert into #temp (leafLvl, L1, L2, L3, L4, L5) select 'L2-1', 'Top', NULL, NULL, NULL, NULL UNION ALL select 'L3-1', 'Top', 'L2-1', NULL, NULL, NULL UNION […]

SQL Server 2019 - Administration

Looking for recompiling stored procedures - I have been tasked with the job of finding the top X number of stored procedures that are being recompiled regularly on a group of servers. I've tried several times to Google this and keep coming across "How To" articles on setting stored procedures for recompile, which is not what I want. I'm trying to […]

if then else issue - I'm trying to use the if then else to check if a member exist in a server role. if it does, do X, else do Y. but not getting the desire outcome. BEGIN DECLARE @member nvarchar(50); USE [master] SELECT @member = members.name FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id […]

SQL Server 2019 - Development

Count Number of Records - Hi I am trying to write a SQL query that returns the number of working days between two dates. The approach I am taking is the following... Financial markets are open on non-holidays so doing a select distinct on trade dates would give me the list of all non-holiday dates. I could create a separate […]

Find the records based on group by Student ID - Data looks like below Status 1 is Active and 2 is Clean For one Student ID we may have one active, one clean status; I want to find the records only with Clean status for a student ID. Student Id Student_Status 12345 […]

problem of slowness in my requests - hello, I am having a major performance issue , with this part of code which is still blocked on my server , Attached is the part of the code that is causing the problem. DELETE R FROM #RESULTS_DEM AS R INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM WHERE ( NOT EXISTS ( SELECT […]

Joining CTEs Causing Performance Issues - Hi everyone I am not sure why the query is taking so long to run. If I run each CTE separately then it runs in less than 2 seconds. However, when I join them they take forever to run. I stop the query after about 10 minutes. I am pretty sure I am joining them […]

Need to create a .xml file in specific format using SQL Query. - I have a table of data that I need to create a .xml file from in a specific format. CREATE TABLE [dbo].[XML_TABLE]( [ProductID] [nvarchar](25) NULL, [Name] [nvarchar](25) NULL, [ParentID] [nvarchar](51) NULL, [AttributeType] [nvarchar](10) NULL, [AttributeID] [nvarchar](255) NULL, [AttributeValue] [nvarchar](4000) NULL ) ON [PRIMARY] GO Here are some values to insert: INSERT INTO WORK.DBO.XML_TABLE([ProductID], [Name], [ParentID], […]

SQL Azure - Development

Copy DB from Prod to Test !! - Good morning. I would like your advice. I work on Azure Sql. We have 3 environments (Dev, Test and Prod) Each environment has a specific tenant. Every month, I would like to copy the database from Production to the Test environment. What solution do you recommend to perform this task? Regards

SSRS 2014

Open linked report in new tab with multiple parameters (javascript) - I have a report that links to another report on the server, while the way I have it currently works the users are requesting that it opens in a new window. The report being linked to has two multi select parameters ="javascript:void(window.open('http://east-rpdb01/reports/report/Testing/Profitability%20Report%20All%20Clients&FiscalYear=" & Fields!FiscalYear.Value &"'))" I am close to getting this but not sure […]

General

cross join sql - I'm attempting to use INNER JOIN to join numerous tables. Here is the code: IF OBJECT_ID('tempdb..#tmpRecData') IS NOT NULL DROP TABLE #tmpRecData --STEP 1 SELECT DISTINCT pr.ChainID, pr.StoreID, pr.SupplierID, pr.ProductID, MAX(CAST(pr.ActiveLastDate AS date)) AS 'Active Date' --ChainID, SupplierID, StoreID, InvoiceDate, InvoiceNumber, SupplierInvoiceDate, SupplierInvoiceNumber INTO #tmpRecData FROM dbo.[ProductPrices_Retailer] AS pr LEFT JOIN ProductIdentifiers iden ON pr.ProductID […]

Anything that is NOT about SQL!

Need information on past SQL Summits, please... - I'm endeavoring to convince my employer to send me to SQL Summit this year, but I'm going to need to "sell" that it would be worthwhile for them to do this. And sell it not just to my supervisor, but to his supervisor and to her superior. Now, I fully expect that there's going to […]

SQL Server 2022 - Development

The place of SMALLDATETIME - SMALLDATETIME is a datatype that gets very little love online and few people recommend it. Kendra Little is right to warn about the rounding up/down of seconds. Microsoft explicitly recommends DATETIME2 over SMALLDATETIME on the Microsoft Learn page. However, it is only 4 bytes in length and gives accuracy to a minute and DATETIME2(0) is […]

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.