Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Information Schema Strangeness

There was a Slack thread at Redgate recently where a developer was showing some code where they decided to use the "extra" column from the information_schema.columns view. They were making decisions on how to detect certain metadata about a column based on the data in this column. Apparently, the data in here is overloaded for different options that might be set on a table.

This caught my eye because I had no idea there was an "extra" column in this view. I flipped over to SSMS and decided to check what was being stored in here. To my surprise, there was no "extra" column. As I dug in a little deeper in the thread, I realized the developer was talking about Information_schema.columns in a MySQL database.

That was a surprise to me. While I know different platforms will add features and functionality to their databases, I thought the information_schema views were consistent across platforms. They should give you a set of information you can count on. Apparently, that's not true. You can count on some things, but not all, which means that these aren't consistent structures.

Perhaps it doesn't matter. It seems every product out there will extend the SQL "standard" where they see fit, adding features or functions that suit particular use cases. Commercial vendors do this for profit, and OSS projects likely do this because an individual wants a change. That has resulted in a wide variety of database platforms that meet different needs and solve different problems.

It would be nice if we could write SQL code and be sure it would run on SQL Server, Oracle, Snowflake, PostgreSQL, or any platform. And in many cases, we can. Lots of basic queries are the same. However, what would be the point? I certainly don't want more people in management wanting to switch from one platform to the other, just because they feel like it. I'd imagine that we'd thrash between platforms every time a senior developer or VP decided a system should run on their favorite platform.

A base standard is good, like a base class in programming. However, they aren't always as useful as they seem, and extending them to meet needs is better for us all. I don't need a standard implementation of the SQL language or the information_schema views, it was just a surprise to realize that this is the case.

Steve Jones - SSC Editor

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

 
 Featured Contents
Technical Article

SQL Server 2012 AlwaysOn Groups and FCIs Part 2

Perry Whittle from SQLServerCentral.com

This is Part 2 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.

External Article

The ten habits for highly successful compliant database DevOps

Additional Articles from Redgate

Database DevOps has come of age. Now seen as a key technical practice which can contribute to the successful implementation of DevOps, it stops the database being a bottleneck and makes releases faster and easier.

To implement it successfully, there are ten areas which teams should focus on.

External Article

PASS Data Community Summit 2023 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. Sign up to receive the latest updates in your inbox.

Blog Post

From the SQL Server Central Blogs - SQL Server Quickie #45 – SQL Server Availability Groups

Klaus Aschenbrenner from Klaus Aschenbrenner

Today I have uploaded SQL Server Quickie #45 to YouTube. This time I’m talking about SQL Server Availability Groups.

Blog Post

From the SQL Server Central Blogs - New Job -> Week 1 -> What Tools Do You Need?

hellosqlkitty from SQLKitty

I changed to a new job and started it this week. Before I started, I thought of all the things I thought I would want to do when I...

Pro Encryption in SQL Server 2022

Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data

Additional Articles from SQLServerCentral

This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Pausing an Index

I start an index rebuild as an ALTER INDEX with the resumable option and a max duration specificed in SQL Server 2022. If I want to pause this operation, what can I do?

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)

Adding Multiple Columns

I need to add two columns to my table. I have two sets of code:

-- #1
ALTER TABLE dbo.Test1 ADD CustCount INT
GO
ALTER TABLE dbo.Test1 ADD CustName VARCHAR(20)
GO

or this code:

--  #2
ALTER TABLE dbo.Test1 ADD CustCount INT, CustName VARCHAR(20)
GO

Which set of code can I use?

Answer: Either #1 or #2

Explanation: Either set of code will work. The docs for ADD note: "Specifies that one or more column definitions, computed column definitions, or table constraints are added." Ref: ALTER TABLE ADD - 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
slow processing - Hi Experts, Yesterday, we ran a program and it has processed 1500 records in 2 min. Today, it is running for past 90 minutes and processed only 350 records so far. What should be checked apart from blocking?   Cheers, Bob
SQL Server 2016 - Administration
multiple PAGELATCH_UP waits on data file - Hi Experts, What is meant by PAGELATCH_UP waittype ? what does it trying to tell me? what is PFS ? why INSERTS are blocking each other? As per knowledge, insert shouldn’t block each other? Since they are getting blocked , waiting for LCK and so went to suspended state.  Does this indicate a highly transactional […]
Error: 18456, Severity: 14, State: 149, no matter what I try - Hello experts, I'm seeing a strange issue. A user reported that they could not log in with their AD account. When I checked the logs, I saw the following error: Error: 18456, Severity: 14, State: 149. Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. I went through all the […]
SQL Server 2016 - Development and T-SQL
dynamic update to SP - Looking for suggestions\examples of how to update a SP that the table used changes each month at midnight. The app creates a new detail table it logs information to. example: sqlt_data_1_2023_03 when 04/01 hits a new table is created called sqlt_data_1_2023_04, and how could I use an automated method to update any SP to the […]
Pivot and group by sum sql - Hello,  I am trying to build an output that takes data from a table in the following format and pivots the unit column while summing each chunk of time by the Time_2 Field, for each hour of each day. For example see test table below. The sum of Units field 0.071+0.083+0.072+0.062 for Time_2  for hour […]
same Procedure with same source tables in 2 environments but delay in exec in 1 - Good Morning, I have the below procedure, in both UAT & PROD same data in source tables (record counts also matched for all source tables in both UAT & PROD) but UAT takes only 1 minute and Prod takes 11 minutes. any idea what can I do/check to make it run faster. just fyi I […]
Administration - SQL Server 2014
SSRS 2014 redirects to http instead of https - Hi , I am looking to find a solution for the SSRS 2014 server redirects to http url instead of https for all the subreports and other report parts like subscription , security , datasources etc . What I have done so far on the rsreportserver config file changed the value for from 0 […]
SQL Server 2019 - Administration
Production Rollback - Hi, is it possible to restore an SQL Server DB to an earlier snapshot while preserving the work performed after the snapshot? I was thinking about backing up the transaction log, reverting to the earlier snapshot, then restoring the backed up transaction log. However, I can imagine problems if there are any format changes between […]
SQL Server 2019 - Development
Monitoring third party database to detect updates without modifying the database - Hello folks, Even writing the title, it sounds oxymoronic but bear with me. We have a third party vendor application with it's own front end the business uses. Specific tables from the vendors database are replicated into our SQL server via a vendor managed process. (AWS Data Sync) Historically we have made use of the […]
T-SQL Recursive CTE not completing at last record - I want to be able to recursively go through the contents of a temp table without using a loop and complete at the last record (ID = 4) and perform a set of statements my data looks like this DROP TABLE IF EXISTS [#ChannelInfo] CREATE TABLE [#ChannelInfo]( [ID] [bigint] NULL, [SerialNumber] [nvarchar](60) NULL, [channelid] [int] […]
Use a recursive CTE to produce 1 update statement using dynamic SQL - I want to be able to generate 1  merge statement rather than loop through a lookup table using a single Set based Recursive CTE statement but im struggling on this. I have a set of tables which are update by means of a look up table using a merge statement This is the look up […]
Query to Update balance based on columns from two different rows. - Hello All,  Below is the  my existing table structure from which I am trying to do an update statement. I need a query to update records  for each employee and subtract it from a column value of different row.  For example in the above case  query should update the value in closing balance for […]
Finding duplicates based on count on different fields - Hello everyone 0 I have a Contact table which has columns like ContactID, Nationalidnumber, Firstname, Birthdate, Mobilephone, Emailaddress, CreatedOn and so on. I want to find duplicate contactIDs where two or more rows has: same Nationalidnumber AND same (Birthdate + Mobilephone) combination AND same (Birthdate + Emailaddress) combination AND same (Firstname + Mobilephone) combination AND […]
SQL Azure - Administration
Azure SQL database AAD user management - I have an Azure SQL DB where I have set up the Azure active directory admin as an azure active directory security group.  My AAD account is a member of that group.  Now I am trying to authenticate to that database through SSMS as my AAD account (Azure active directory - Universal with MFA) and […]
SQL Server 2022 - Development
Matching the table - Hi All, I have two table called TableA and TableB. Here I need to match the TableB records from TableA. TableA http://www.funoppia.com www.finkeyz.com w.astronecollege.com http://learningberg.com http://www.mytutorials.co.in http://www.firki.co   TableB funoppia.com http:/finkeyz.com ww.astronecollege.com https://learningberg.com mytutorials.com firki.com I need to find the matching records from TableB in TableA
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -