SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Resuming Indexes on Old Databases

The Voice of the DBA

Bare Metal

At the first job I had as a DBA, I had to build a new server. This was in the days of SQL Server 4.2, and I was combination DBA, sysadmin, and general help desk at a small company. With a software developer consultant, we ran some tests on various machines and then ordered a collection of parts from Compaq. Back in this time, they would only customer parts of the server. We unpacked our boxes with the server, extra drive bay, various SCSI drives, and extra RAM. We assembled and tested the machine and eventually put it into production.

In the years since then, I got out of the hardware business and left that to others. For awhile I worked in organizations with IT staff dedicated to building machines, but at some point we stopped doing that. The growth of VMWare and other hypervisors changed the paradigm for most organizations. For more than a decade, all the servers I've connected to are virtual machines running on hardware that my employer or a cloud provider owns and manages as a node in a cluster.

Early on there were concerns about the overhead of using hypervisors and virtual machines. When many early workloads moved to VMs, lots of organizations left database servers on bare metal to squeeze every bit of performance out of the system that was possible. Over the years, improvements in hypervisors as well as the software used to connect storage and networks together seem to have rendered those conversations obsolete.

Or have they?

Today I'm wondering if any of you still have servers on bare metal. Are there systems that you continue to install the OS and database server software directly on the hardware? Or are there systems where you still ensure that there is only one VM, the database server VM, on a hardware node?

The cloud has changed a lot of these conversations, since everything is a VM. Even many local data centers will rent you a VM, something that wasn't possible when we first moved SQL Server Central out of a friend's basement into a co-location facility. Back then we owned our servers and I installed them in data center racks. I would like to think we'd have moved our site to the cloud in some way over time, though maybe not. Maybe I'd still be managing a couple servers in a room in Denver.

Are any of your organizations still running legacy systems in the same way you would have in 1999? Let us know today.

Steve Jones - SSC Editor

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

Featured Contents

Get the Most Current Row From a Detail Table

Thomas Franz from SQLServerCentral

This article compares the performance of finding the most recent row in a child table for updates, looking at four possible solutions.

Influencing Successful Cloud Transformational Change

Additional Articles from MSSQLTips.com

By developing a strong business case, building key relationships, using budgeting and forecasting tools, and providing training and support, tech leaders can effectively sell the benefits of the cloud and influence change.

Enterprises that adopt Database DevOps save an average of $4.3M per year (Video)

Additional Articles from SQLServerCentral

If treated well, the database can be a major accelerator in your business’s efficiency. It can be the star performer in your migration to the cloud, in your move to micro services and other change initiatives. Find out how the database can be the hero in your digital transformation or change initiative.

From the SQL Server Central Blogs - Battle of the Query Hints in Query Store

Grant Fritchey from The Scary DBA

I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there’s already a query hint on a query, what happens...

From the SQL Server Central Blogs - T-SQL Tuesday #163 – The Best Career Advice

Steve Jones - SSC Editor from The Voice of the DBA

We’re a little delayed this month. Both the host and I forgot about this. So far in June, I’ve been in Fort Lauderdale, Las Vegas, Denver, and Cambridge for...

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):
	Resuming Indexes on Old Databases
I have a SQL Server 2019 developer edition instance with a database that is set to compatibility mode 130. I run this code: CREATE NONCLUSTERED INDEX [NC] ON [dbo].[RandomTestData] ( [ID] ASC, [DateOfBirth] ASC )WITH (ONLINE = ON,RESUMABLE = ON) GO What happens?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Carlo Romagnano)

OUTER APPLY

What does the last query return?

declare @t table(i int) 
insert into @t
SELECT * FROM (VALUES
('1')
,('2')
,('3')
,(NULL)
) AS V([i])


SELECT t.i
      ,MSG.msg
  FROM @t t
OUTER APPLY
(
    SELECT msg = CASE t.i
                    WHEN 1 THEN 'ONE'
                    WHEN 2 THEN 'TWO'
                    WHEN 3 THEN 'THREE'
                    ELSE 1/0 --CAST(1/0 AS VARCHAR(10))
                 END
) MSG

Answer: The command raises a syntax error 'Conversion failed when converting the varchar value 'ONE' to data type int.'.

Explanation: No rows are returned because in the CASE statement there's a problem in the conversion from VARCHAR to INT. From the CASE statement docs: Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression P.S. All possible return values are parsed, even if whose case are not true. Ref: CASE - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16#return-types

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

Performance Tuning - Since I consider myself a learning DBA, have few years of fine tuning experience, I struggle to find to root cause if someone complains about the app slowness. These are some of the tools I use to find out. I have a VM, 8 cores, 64 GB RAM, 6 DBs. Largest DB is only 4 […]

SQL Server 2016 - Development and T-SQL

UNION statement is inconsistent - Hi I have a weird problem that I can't find the cause for. I have the statement below, it consists of three individual statements combined with UNION. In the production site, first SELECT outputs one row, middle SELECT nothing, and last SELECT 9 rows. The full statement with UNION varies in returning 1 or 10 […]

Administration - SQL Server 2014

Performance Dashboard Error: 'CHECK_DEPENDENCIES' - Hey all, I am trying to drill down into the performance report on our 2014 box to see what is maxing the CPU and I get the error : "A data source instance has not been supplied for the data source 'CHECK_DEPENDENCIES' Done some digging and I have found an article that states this reporting […]

SQL Server 2019 - Administration

SQL Cluster Active/Passive Services stopped on dormant node - Correct me if I'm wrong here but on a SQL Server 2019 cluster (active/passive deployment) ----- where NODE1 of the Cluster is ACTIVE and NODE2 of the Cluster is PASSIVE (dormant) ----- the SQL Server services (except for SSIS) will show as STOPPED on the PASSIVE (dormant) NODE2. We have a vendor asking about this […]

Index creation on partitioned table - Hi I have a large partitioned table and in order to carry out some maintenance on the leading partition I needed to switch it into another table. As part of te maintenance I needed to drop and recreate the indexes In test (a fairly recent restore of production) things went smoothly, to create the indexes […]

SQL Server 2019 - Development

Arithmetic overflow error when using POWER function - Hello, Any help appreciated. This code should perform a internal rate of return (IRR) calculation. It generates correct data based on what was provided to me using Excel's IRR function. It looks like as GETDATE() gets close to my hard coded date the error is generated. Any suggestion to prevent the error would be appreciated. […]

Preventing Arithmetic overflow error when using POWER function - Hello, Any help appreciated. This code should perform a internal rate of return (IRR) calculation. It generates correct data based on what was provided to me using Excel's IRR function. It looks like as GETDATE() gets close to my hard coded date the error is generated. Any suggestion to prevent the error would be appreciated. […]

SQL Azure - Administration

For SQL Managed Instance - Alternative from using xp_cmdshell? - Hi, Looking to see what people have done if in the past they used xp_cmdshell but now are on SQL Managed Instance where it is not supported. Thanks, Daniel

How to read pdf file metadata from an Azure Blob location? - Anyone recommend the best way to accomplish this? Powershell? Or can you do this via external data sources? All I need to do is read pdf file metadata from folders in azure blob storage so I can get the filename and date file created for audit purposes. Thanks in advance!

Strategies and Ideas

How to model this... - Let's say the main fact table stores financial agreements, along with various attributes and measures, like the lender, the amount loaned, the amount still owed, etc.... We have a fairly well working DW whereby the one and only fact table models the above. But for some financial agreements, the agreement isn't a monetary loan but […]

SQL Server 2022 - Administration

SQL Server running on Linux, instance starts almost 2 mins after the service - SQL Server (2022) running on Linux (SuSE 15.4), instance starts almost 2 mins after the service. When the system starts, systemctl status mssql-server.service reports, something like Active: active (running) since Wed 2023-06-28 15:47:57 UTC; 2min 43s ago However, sql server is not available immidately, has to wait almost 2 mins before can be queried. When […]

SQL Server 2022 - Development

Error 823: Error number: 21, Severity: 24, State: 1 at start of SSM 19.1 - Hello all, I have installed MSSQL 2022 Developer on a new Laptop with Win11 Prof. Databases are located on partition D:\DB\MsSQL\SQL2022\Data Sometimes start of SSM (or my application) is running without any problem When the failure 823 came up first time, I deinstallend and installed complete MSSQL 2022 and SSM But than (around one week […]

SQL Server Predicate - Hi, I need to get clear idea abut SQL Server Predicates, according to Microsoft document it describes there are only 4 predicates available in sql server. https://learn.microsoft.com/en-us/sql/t-sql/queries/predicates?view=sql-server-ver16 CONTAINS FREETEXT IS [NOT] DISTINCT FROM IS [NOT] NULL but my drought is, why below expression are not categorize as Predicates because all of the below expression that […]

how to identify sql scripts not in stored procs w/o using trace - I'm storing off various stats about procs on a daily basis. On e of them stores each sql statement in each proc w/ runtimes and other information. My issue is there are power bi reports and sql job step that are not using procs but have embedded the sql scripts directly in them . […]

issue with text column in dbo.syscomments - I wrote a process to store off all sql objects daily. There is one that stores off all stored procedures contents daily. The text column in the dbo.syscomments table is being stored locally in another table whose column is a varchar(max). It stores each proc in 4000 char chunks. So there will be one record […]

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.