How to Improve Database Design to Speed Up SQL Queries (2023-05-15)

SQLServerCentral Newsletter for May 15, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Improve Database Design to Speed Up SQL Queries]( - [7 ways businesses can improve the quality and speed of database releases]( - [Star Schema vs Snowflake Schema Considerations]( - [From the SQL Server Central Blogs - The first two chapters of my book are available!]( - [From the SQL Server Central Blogs - Create First Azure Data Factory Pipeline]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Refreshing Modules]( The Voice of the DBA  Finding Your Own Career This editorial was originally published on Dec 11, 2019. It is being republished as Steve is out of town. Building a career is hard work. It takes time and focus to move along a path that you choose for yourself. What I have seen across a few decades of work is that most people don't bother to find a path, and are happy to wander from job to job, finding new positions or projects when they are forced to do so. As much as I advocate for others to continually learn, there's nothing wrong with wandering randomly through a career, sticking with a job or employer as long as you can. The one thought that I'd like to see everyone adopt is a conscious decision to do so. Make the choice to have the career you want, and the life you want, whatever that is for you. Recently on Twitter, there has been a lot of career advice that I've seen posted. One very interesting item was that a developer shouldn't compare themselves to anyone else. They should be proud of what they've done and work towards the goals and achievements that mean the most to them. Good advice, but often hard to follow. Most of us only see a fraction of the world, and a limited view of potential opportunities for languages, projects, positions, and skills. We often do look at others' accomplishments and then try to model ourselves in some way to be like others. Not completely but we may choose to learn a skill another has, or pass a test that someone else completed, or maybe go work for the company that a colleague has enjoyed. There's nothing wrong with that. Think about what others do and decide what aspects of that you'd like to adopt. As much as I've coached others to think for themselves, make their own decisions, and be proud of the things they accomplish, I also want them striving forward. That often requires a goal and an ideal, which we often find in others we admire. Compare yourself to others, but don't judge yourself by their standards. Decide on your own if you're living up to the measurements that make sense to you. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to Improve Database Design to Speed Up SQL Queries]( Vikram Bachu from SQLServerCentral This article examines some basic database design principles that help ensure your queries can execute more quickly. [External Article]( [7 ways businesses can improve the quality and speed of database releases]( Additional Articles from Redgate This article explains how these ‘ephemeral databases’ can be used to improve development and testing processes in ways that reduce the number of bugs entering the deployment pipeline, drive up the quality of database releases, and so improve the reliability and speed of database deployments to production. [External Article]( [Star Schema vs Snowflake Schema Considerations]( Additional Articles from MSSQLTips.com Sometimes people say to avoid the snowflake schema and stick to a clean star schema. Why is that? Is something wrong with creating a snowflake schema? [Blog Post]( From the SQL Server Central Blogs - [The first two chapters of my book are available!]( James Serra from James Serra's Blog As I have mentioned in prior blog posts, I have been writing a data architecture book, which I started last November. The title of the book is “Deciphering Data... [Blog Post]( From the SQL Server Central Blogs - [Create First Azure Data Factory Pipeline]( dharmendra.keshari from SQL Geek In my previous post, we explored the basics of creating First Azure Data Factory. Today, I want to delve deeper into the process of creating and configuring pipelines within... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Refreshing Modules What does sp_refreshsqlmodule do in SQL Server 2022? 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) SQL Server 2022 VLF Changes In SQL Server 2014, if a log file grew by less than 1/8 of the current file size, only one VLF was created. If the growth was more than 1/8 of the current size, then there would be more VLFs. These were the rules: - <64MB growth, 4 VLFs created - >=64MB and <1GB, 8VLFs created - >1GB, 16 VLFs created. In S<64MB, QL Server 2022, the engine tries to be more efficient. How have things changed? Answer: For growth < 64MB 1 VLF. The rules are otherwise the same Explanation: The rules are the same for > 64MB. For < 64MB, only 1VLF is created. Ref: Virtual Log Files - [ [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 2016 - Administration [Index Creation for large table failed.]( - I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job. CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST] ( [GSN_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date]) However the creation […] SQL Server 2016 - Development and T-SQL [Extracting Data from XML file]( - Hi all, I have a XML file which consists this type of data: 12345 1 ABC XYZ TEST TYPE TEST REFNUM 2016-11-30 TESTCOMMENT TEST DESIGN TEST NAT TEST TYPE EXACT 1964-07-17 […] [How To Enumerate Hierarchical Info on SP calls]( - NOTE: Forgive me if I am using the wrong terminology with regards to this. \ I am trying to get a list of all The SP's (Stored Procedures) that are called by an SP. For example let's say I have the following SP's: LF5_Job1, LF5_JOb2, LF5_CalcResFees, F5_CalcCommFees & LF5_PopulatFees The SP LF5_Job contains one 'EXECUTE' […] Administration - SQL Server 2014 [DBCC CHECKDB - LATCH_EX]( - Hello. I posted previously about DBCC CHECKDB and Backups and changes are under review - thanks for feedback on here. This is related - but a very specific question about DBCC CHECKDB - here is what I am seeing. DBCC CHECKDB runs against Database A in SQL Instance A. On certain days of the month […] SQL Server 2019 - Administration [What can set the recovery model of a database?]( - Hi I have had an incident where a database log has grown to a large size. Normally I would backup the logs however the recovery model has been set to SIMPLE. I am under the assumption that only a human can set this option. I have checked the logs and cannot find any alter database […] [Log File continue growing]( - Hello everyone, i read a lot about growing Log Files on this forum but can´t find a solution for my problem. I moved a database from one server to another, cause there were several performance problems on that old server. Since i moved the database the lfd file is continue growing. Database (mdf) file is […] SQL Server 2019 - Development [how to get the max date in the table]( - I have a below table with the values cscode   StartDate    EndDate 0011  5/9/2022   10/1/2022 0011  6/17/2022  1/1/2023 0011  5/9/2022   10/1/2022 The date column is varchar. I need to take the latest mm/dd/yyyy using EndDate. For ex, in the above table I need to […] [Delete in ERREOR with an FK on the same table]( - Hello I do not know why my delete does not work despite I respect my FK constraint The FK constraint is based on the same table ALTER TABLE [dbo].[EVACUATION] WITH NOCHECK ADD CONSTRAINT [FK_EVA_DECH] FOREIGN KEY([COD_EXP_CHG], [COD_CEL_CHG], [NUM_DOS_CHG], [NUM_EVT_CHG]) REFERENCES [dbo].[EVACUATION] ([COD_EXP], [COD_CEL], [NUM_DOS], [NUM_EVT]) GO ALTER TABLE [dbo].[EVACUATION] CHECK CONSTRAINT [FK_EVA_DECH] GO Here is […] [SSIS Logging]( - Hi everyone I am having some issues with SSIS logging. I recall setting it up but I don't remember how I did it last time. It was an option in SSIS somewhere but I cannot seem to find it now. I took a look at sysssislog table and I see that it did log in […] SQL Azure - Administration [SQL Managed Instance Accelerated Database Recovery]( - ADR feature is optional in Onpremise where as in SQL ManagedInstance, it is enabled by default. In the Below link performance comparison has been done by Niko Neugebauer. There's about 8% decrease in the performance for Insert/Update/Delete, when ADR is enabled. Accelerated Database Recovery so there will be atleast 8% performance degradation if we migrate […] [Query Oracle DB from Azure SQL]( -  Folks, I'm investigating the possibility of migrating from Azure VM with SQL to Azure SQL Managed Instance but have hit a roadblock. We depend heavily on data from Oracle DB accessed through Linked Servers, both for queries and also direct imports to internal tables. My understanding is that Azure SQL does not support OLE […] Powershell [How to Insert API data into database using Powershell]( - Hello, I am trying to teach myself how to INSERT data from an API into a SQL Server DB using PowerShell. For this exercise, I am using a public API which is: ‚ What I would like to do is insert this into a table using a Stored Procedure. Below is my code. I think […] Analysis Services [using PowerQuery with SSAS Tabular]( - I have a stupid question... I can do all the PowerQuery stuff in PowerBI, but can I move those transforms to SSAS Tabular somewhere, so that the transformations will be done and then just persist the results in my data warehouse/SSAS Tabular model? I know I can do this in PowerBI, but how do you […] Strategies and Ideas [Injest user-provided Excel?]( - In today's episode of "Products that Should Exist (and maybe do)": Have you come across a solution (preferably a web-based portal) that allows an end user to upload an Excel file, digests the data, identifies tabular data, walks the user through column mapping to structured data, performs data validation, highlights invalid data, allows editing of […] SQL Server 2022 - Development [Want to load data on a staging table.]( - In the past, I loaded data off excel, and it was difficult to manipulate in SQL. Someone suggested loading the data on a staging area next time to avoid hassles. Now, I have more of such similar data from excel to import to SQL server, and need to load on a staging table. I have […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -