SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Database Transaction

The Voice of the DBA

30 Years of SQL Server on Windows

I've spent over 30 years working with SQL Server. I saw a post recently from Bob Ward who is celebrating 30 years at Microsoft, noting this is also the 30th anniversary of SQL Server on Windows. It's been an honor for me to get to know Bob, learn from him, and spend time socially at various events around the world. He's truly been a force in improving the SQL Server platform through the years.

While SQL Server has been on Windows for 30 years, I've got a bit more than 30 years working with the platform as I actually started managing a SQL Server instance on OS/2. With the addition of Linux, I've now run SQL Server on three different server OS hosts: OS/2, Windows, and Linux. That's somewhat amazing to me, especially as one of my April Fools jokes came true. This was one of the most-read articles on SQL Server Central for a long time, and perhaps it influenced enough people at Microsoft to build and release a Linux version.

Over three decades, the growth of the platform has been amazing. We've had the core engine lose tables in memory and then get them back with In-Memory objects. We have seen the heavyweight trace evolve into Extended Events and SQL Audit. The language has grown with many new ANSI features that help us write better code. We've deprecated a number of objects and added the ability to create our own with the CLR. We've added new data types, not all of which are named well, but we still have the ability to process more types of data natively. We've even added new services, with SSIS, SSRS, and SSAS packaged into the platform.

Not a lot has been removed, but a few things are gone, like Notification Services. Replication doesn't seem to have changed a lot in 30 years, which is a bit disappointing to me, but there have been a lot of improvements in HADR capabilities. We even have other companies (Amazon and Google) selling their own version of SQL Server as a platform, where you don't have to manage the instance.

In many ways, I've found SQL Server to be easier to use, but also much harder to learn about because the breadth of what's in the product is so wide now. I can't imagine anyone actually knows the entire product at a very deep level. I've also found myself more disappointed with the experiments Microsoft makes when adding features and not continuing to invest and evolve them (or even fix all the bugs).

There certainly have been plenty of bugs, security issues, and maddening moments in working with SQL Server, but it's been a great 30 years of a career working on the Windows version. I likely won't do that for another 30 years, but I hope those of you making a living working with the platform enjoy it as much as I do.

Steve Jones - SSC Editor

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

Featured Contents

The PASS Data Community Summit First Timer Guide

Steve Jones - SSC Editor from SQLServerCentral

The 2023 First-Timer guide to the PASS Data Community Summit is available from Edwin Sarmiento

Time-Series Data Analysis with Apache Druid

Rahul Gupta from SQLServerCentral

Learn the basics of Apache Druid and how it can be used to ingest data into a data lake.

Is your organization using MongoDB? Help the Redgate Flyway team understand your pain points

Additional Articles from Redgate

Is MongoDB in use within your organization? The Flyway development team is adding MongoDB support into Flyway and would like to better understand the current pain points. If you are able help, or are interested in finding out more, please participate in our 5-minute survey.

From the SQL Server Central Blogs - Summit 2023 Is Nearly Here!

alevyinroc from FLX SQL

All week, my phone has been reminding me (via photo memories) of the amazing experience I had at PASS Summit 2017. This can mean only one thing - PASS...

From the SQL Server Central Blogs - Game Night at the PASS Summit

Steve Jones - SSC Editor from The Voice of the DBA

Games Night is back at the Summit, this time on Wednesday night in a large space for 200 people plus to enjoy some fun with friends and colleagues. This...

Microsoft Power Platform Up and Running: Learn to Analyze Data, Create Solutions, Automate Processes, and Develop Virtual Agents with Low Code Programming

Site Owners from SQLServerCentral

The book begins with the basics, explaining what low-code and no-code are and showing how to maximize efficiency in creating business applications for one's organization. Next, the book describes the Microsoft Power Platform's foundation, as well as all its components and services. Readers will begin with practical exercises right away, beginning with provisioning a Power Platform environment. Next, the book delves deeper into the Power Platform components such as Power Apps, Power Automate, Power BI, and Power Virtual Agents. Towards the end, the book explains practical exercises for each feature or service where you will gradually build a small business solution for a fictitious organization, Project Wizards, Inc.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Database Transaction
What happens if I run this? BEGIN TRANSACTION CREATE DATABASE newdb ROLLBACK TRANSACTION
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)

Changing the PK - I

I've got this table:

CREATE TABLE Invoice
( InvoiceID   INT NOT NULL IDENTITY(1,1) CONSTRAINT InvoicePK PRIMARY KEY
, InvoiceDate DATE
, CustomerID  INT);
GO

I want to change the data type of the PK to a character type. I run this code:

BEGIN TRAN
DECLARE @e INT = 0
ALTER TABLE dbo.Invoice DROP CONSTRAINT InvoicePK
IF @@ERROR<> 0 
 SELECT @e = 1
ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceID VARCHAR(20) NOT NULL
IF @@ERROR<> 0 
 SELECT @e = 1
ALTER TABLE dbo.Invoice ADD CONSTRAINT InvoicePK PRIMARY KEY (InvoiceID)
IF @@ERROR<> 0 
 SELECT @e = 1
IF @e = 0
COMMIT
ELSE 
ROLLBACK

Does this work?

Answer: No, because you cannot remove the identity property

Explanation: This doesn't work. Once the identity property is set, it can't be removed. This means that changing the datatype needs to be a numeric. For this code, we get this error: Msg 2749, Level 16, State 3, Line 27 Identity column 'InvoiceID' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable. Ref: IDENTITY PROPERTY - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver16

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

SQL2017 DBMirroring Query store not working on mirrored databases - I have noticed on our SQL2017 mirrored databases Query store is not working. ( Moving to AGs is not an option. ) Settings in use: ALTER DATABASE [MyMirroredDb] SET QUERY_STORE = ON GO ALTER DATABASE [MyMirroredDb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, […]

SQL Server 2016 - Administration

SQL Server Transactional Replication-Snapshot Initialization for existing REPL - Hi, This is Syed JB, I have an existing Transactional Replication. Unfortunately, for some reasons Replication has been removed for some time. But, now, we have to rebuild the Transactional Replication. Here is the problem, Subscriber has the historical data and for this I can’t push the snapshot and without the initialization, subscriber subscription […]

SQL 2012 - General

SQL Scripts automatically invoked - When detecting the processes in the SQL Server 2012, I found there was a SQL scrit executed automatically automatically many times, the following as attachment, do you know when the script will be executed and when it will be executed? thanks a lot!

SQL Server 2019 - Administration

Log shipping time_since_last_backup - I am using SQL server 2019, I have a database setup transaction log shipping on Server A, and secondary on Server B. I have a need to setup another database to also use transaction log shipping, also primary db is on ServerA and secondary is on Server B. When I setup the Alert monitor instance […]

SQL replication error. - Hi I´m having two SQL server cluster ( lets say A and B. ) with a number of instances ( lets say A1, A2,.. , B1,B2...) in two different sites. I want to replicate A\A1 database Test to server B\B1 database Test and server B\B1 database test2 to server A\A1 database Test2 ( hope […]

Sorry posted in admin section - Sorry posted in admin section.

SQL Server 2019 - Development

Find a word from string - Hi All I would like to get rows wherever it has "restricted" . If I write condition with like '%restricted%' it is showing me all. create table #tbl_datafile_list (Autogrow varchar(500)) go insert into #tbl_datafile_list values ('64.0 MB, unrestricted growth'), ('By 10 percent, restricted growth to 500000.0 MB'), ('By 10 percent, unrestricted growth'), ('1.0 MB, unrestricted […]

Has SQL Server Introduced a way for Converting HTML to non-HTML stored as VARCHA - If a table stores in a column of type VARCHAR the textual equivalent of an html page, does SQL Server have some but in function or process for converting that into non-html text, just regular text with carriage returns and the like? I've seen a few custom solutions for this but noting native to SQL […]

SQL Azure - Administration

IX fragmentation and Updating Stats - Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%. I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as […]

Good Azure cost calculator - Azure calculator doesn't give a good estimate as it cannot differentiate between HA/ DR and primary replica. I have looked around , I couldn't find a good way to come up with an estimate, wondering how others have done this before?

shrink data file with low priority - so total size of DB on Azure is 1 TB, but when I add all tables ( Data file ) and log file the total size is 100 GB. So I need to reclaim the 900 GB ( release storage to the OS ) . MS recommends running DBCC shrinkfile with wait at low priority […]

Reporting Services

HSTS - need to enable in SSRS 2022 - Hi, My sys admin says I must enable HSTS in SSRS to pass penetration tests. Using this as guide: Enabling HSTS and selecting most secure ciphers and protocols for HTTPS for ConfigMgr I change the custom header property and restarted the ssrs service. Problem is my sysadmin is saying that is still not passing the HSTS […]

Analysis Services

Product Hierarchy in a Matrix with 8+ measures on Values-- Perfomance - I'm not new to PowerBI, but I'm new to this problem. I'm working for a company that's creating a PowerBI solution on Azure for a retail company. They have a proper star schema (Sales fact in the middle, and then these dimensions: Product (hierarchy, 8 levels), Date, Store Hierarchy. And then they have like 8 […]

SQL Server 2022 - Administration

None of the IP addresses configured for the availability group listener - Hi , Receiving below error when trying to add listener to AG. Any suggestions please. ALTER AVAILABILITY GROUP xxxx ADD LISTENER N'xxxx' ( WITH IP ( ('xx.xx.xx.xx', 'xxx.xxx.xxx.xxx') ), PORT=1433 ); Error: Msg 19456, Level 16, State 0, Line 1 None of the IP addresses configured for the availability group listener can be hosted […]

SQL Server 2022 - Development

How to Efficiently Find and Remove Duplicates in a Large SQL Database? - I have a large SQL database with millions of records, and I've identified duplicate entries. What's the most efficient way to find and remove these duplicates without compromising database performance or data integrity? Are there any best practices or SQL queries that can help in this situation?" This question addresses a common database maintenance task […]

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.