The Basics of Using GENERATE_SERIES() (2023-10-09)

SQLServerCentral Newsletter for October 9, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [The Basics of Using GENERATE_SERIES()]( - [Alternatives To SQL Server 2022 Built-in function GREATEST]( - [SQL Athena Introduction - How it works, Use Cases and Advantages]( - [From the SQL Server Central Blogs - Penetration Testing your SQL Server, Part 1 – The Recon Mission]( - [From the SQL Server Central Blogs - Quick Tip: Taking advantage of Read-Only SQL Server replicas in your C# application]( - [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Question of the Day - [Optimized Locking]( The Voice of the DBA  How Much Code Can You Review? This editorial was originally published on Jan 27, 2017. It is being re-run as Steve is on vacation. When I was in college, I mostly wrote my own code for assignments (and fun), turning it in when I thought it was done. At some point I had a class that started to adopt more software engineering practices, and I was forced to get my code reviewed by a peer before handing it in. This was an interruption to my workflow (and more work since I had to review other code), but it did allow find mistakes I had made and seemed to improve my grade. In one of my first development positions, I had to get two people to review my code before I could submit it for deployment. A humbling and painful process at first, but over time I became used to the idea. I'm not sure the quality of code improved, but it was more readable, more in-line with everyone else's code, and as we learned techniques that worked well, all developers adopted them quickly. These days as I talk with various customers and clients, I find that code reviews are handled very inconsistently. Some companies require them, some have automated processes, some have ad hoc reviews, and everyone has ways to circumvent the system. Certainly emergency patches need to be made at times, often with little review, but I will say that it seems the more common a set process and practice is, the less issues a customer has. If you review code, or have your own code reviewed, on a regular basis, I have question for you. How much code can you review for a deployment? The question I'm asking is about your process and habits. Is it worth reviewing every bit of code? Perhaps there are objects that you don't worry about as much as others, or certain developers that receive more (or less) review of their code. Perhaps seniority makes a difference as to the level of scrutiny. If there are problems found in QA and changes made, is there a way to re-review code? I'm wondering today about your process. Share what you can, and if you want to do it anonymously, please feel free to send me a note at sjones (at this domain). Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [The Basics of Using GENERATE_SERIES()]( Bogdan from SQLServerCentral This article shows how GENERATE_SERIES can be used with a few practical examples and a warning on parameters. [External Article]( [Alternatives To SQL Server 2022 Built-in function GREATEST]( Additional Articles from SimpleTalk If you haven’t already heard, SQL Server 2022 introduced a new built-in system function called GREATEST. Simply put, it is to a set of columns, variables, expressions etc. what the MAX function is to a set of values (i.e., rows) of a single column or expression. [External Article]( [SQL Athena Introduction - How it works, Use Cases and Advantages]( Additional Articles from MSSQLTips.com Learn about Amazon Athena, how it works, why you would use it, and some of the advantages of Athena over traditional relational databases. From the SQL Server Central Blogs - [Penetration Testing your SQL Server, Part 1 – The Recon Mission]( david.fowler 42596 from SQL Undercover Since witnessing a rather nasty cyber attack around a year ago, I’ve been thinking quite a bit about security. Do we really know how secure our SQL Servers are?... [Blog Post]( From the SQL Server Central Blogs - [Quick Tip: Taking advantage of Read-Only SQL Server replicas in your C# application]( sqlrunner from SQLRunner If you happen to be using C# there is a very easy way to set your application up to take advantage of Read-Only replicas. Using this connection string: (Some portions... [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Steve Jones - SSC Editor from SQLServerCentral Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Optimized Locking In Azure SQL Database, what are the two main components of optimized locking? 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) Double Standby I have a SQL Server 2019 instance and I run this: USE [master] RESTORE DATABASE [sandbox4] FROM DISK = N'D:\SQLBackup\New folder\sandbox_20210308.bak' WITH FILE = 1, MOVE N'Sandbox' TO N'D:\SQLServerData\SQL2017\Sandbox4.mdf', MOVE N'Sandbox_log' TO N'D:\SQLServerData\SQL2017\Sandbox4_log.ldf', STANDBY = N'D:\SQLBackup\New folder\sandbox_RollbackUndo_2023-08-07_11-51-39.bak', NOUNLOAD, STATS = 5 GO This works and I see the database in standby/read-only. Now I run this: RESTORE LOG [sandbox4] FROM DISK = N'D:\SQLBackup\New folder\sandbox_20210308_1301.trn' WITH STANDBY = N'D:\SQLBackup\New folder\sandbox_RollbackUndo2.bak', NOUNLOAD, STATS = 5 GO What happens? Answer: This succeeds and the database is in standby/read-only with the log backup applied. Explanation: This works fine. When the restore operation starts, the first standby file is deleted and a new one created. The name doesn't matter. Ref: Restore 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 [cost threshold for parallelism value to be set]( - Hi Everyone, Why and when should be set 'cost threshold for parallelism'? We are on SQL 2017 Enterprise Edition. What should be the value set for cost threshold for parallelism instead of default value 5 and why ? what benefit do we get if we set a high value and what should be the starting […] [Identifying the process that is backing the databases to virtual device]( - We have about 20 SQL Servers. We use Net backup or Native backup for SQL Server database backups. When I query the msdb to find the backups, 2 servers have backup method different. In addition to the native backup some other process is backing up the databases to virtual device. I am struggling to identify […] [SQL Migration - Identifying Connections - Best approach]( - I have one SQL Database server which is old version and planning to migrate to SQL2017. We have about 10 databases on this server. I am seeing connections to only one database from sp_who2. Looks the connections to other databases are closing immediately. Question 1: Other than running SQL Profiler, what are the options available […] SQL Server 2016 - Administration [Does multi-subnet with standardeditionofsqlserver supports DB Mirror Or AG group]( - Does multi subnet failover with standard edition of sqlserver supports DB Mirror or AG groups 1. can db mirror configured in different multi subnet with standard edition 2. can db always on configured in different multi subnet with standard edition SQL Server 2016 - Development and T-SQL [get week number of month]( - I want to query a table that has a datetime column ([CreateDateTime]) and get the week number for that specific month. 2023-03-01 09:30:00 2023-03-12 11:15:22 2023-03-22 14:22:18 2023-04-02 15:12:30 I want to get these results from the above dates: 1 3 4 1  [CXConsumer wait type issue]( - hello, my query is select * into #TmpA from tblA (nolock). tblA contains 116000000 records. This query sometime takes 6 minutes and some time takes less 2 minutes. I observed CXConsumer wait types mostly and sometimes CXPacket. On observing the execution plan, parallel execution is taking place. kindly guide what can be done in this […] SQL Server 2019 - Administration [High tempdb usage alternatives]( - Hi We have an stored procedure which runs frequently and it contain logic in which data are first dumped in temproray table and then moved to physical table .due to large data and frequent run of sp .tempdb database get filled up quickly .We cannot take server restart everytime so whats alternative to temp variables […] [high latency in sql server transactional replication]( - Hi I am new to replication and we have database of size around 2TB and its has been set up for replication .there are 4 subscriber to it. Tables(articles) of database has been divided into 14 publishers. Issue sometime we face high latency on server with lakhs of undistributed command so what should i check. […] [upgrade evalution editon reporting services to Stanadard]( - Hi All, I installed reporting services evaluation edition for testing purpose, but now 180 day trial period is expired and I am unable to generate reports. Is it possible to upgrade it with SQL server 2019 standard edition, without disturbing the existing configuration. I Installed SQL server 2019 database engine on same machine, but while […] [Delegated rights to Enable/Disable TSQL Jobs (Specific Jobs)]( - All, In attempts thinking that I am smarter than your average bear, I attempted to do the right thing and write a wrapper for users to be able to enable/disable certain jobs. Trying to avoid the pitfalls of allowing users access to modify everything in a job and avoid code elevation, I wrote this test […] SQL Server 2019 - Development [Convert columns result into rows with comma and single quotes]( - Hi All, Hi All, I want to convert columns result into rows with comma. Ex: Result should be - 'SQL1','ABCD100','XYZ123' #code: create table #tbl_db (name varchar(100)) insert into #tbl_db values ('SQL1') insert into #tbl_db values ('ABCD100') insert into #tbl_db values ('XYZ123') select * from #tbl_db Result needed: 'SQL1','ABCD100','XYZ123'  SQL Azure - Administration [post steps for Table PARTIONING]( - Any one did Table partitioning in Prod, please list steps here. we have a transaction DB with 4 Existing Large 60 M row Tables, WITHOUT partition storing Transaction Data from 2019 to 2023. They asked me to come up with solution to partition this data , on azure MI ( assume only one FG is […] Strategies and Ideas [SSIS vs TSQL sp]( - Hey All, I'm working on a new DW design and I'm learning DW on the fly right now, so I don't have much experience here. Anyway, we are currently in the planning stages. We are considering not using SSIS, and just writing our own SP to handle all DML logic for the EDW. I'm just […] Employers and Employees [Master Data - Relational or Dimensional modeling?]( - Dears, In my company, we have certain master data domains like customer; Vendor; Product; Materials; Bill of Materials. So far we have been doing our modeling using relational modeling in the cloud. It consists of entire tables that we push from SAP ECC and SAP MDG like MARA; MVKT, etc... then we have views on […] SQL Server 2022 - Development [Hello i am new to Sql and idk why i get this error and also not getting results.]( - CREATE DATABASE dbsubjects; CREATE TABLE tblstudent( ID INTEGER PRIMARY KEY, TheName Varchar (100), TheAddress VarChar (100), Marks Real, Comments Varchar (100)); insert into tblstudent values (1001, 'Imon', 'FRI', 90,'Great'); Msg 213, Level 16, State 1, Line 9 Column name or number of supplied values does not match table definition.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -