SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Original Database

The Voice of the DBA

The Panjandrum Conundrum

This editorial was originally published on July 10, 2017. It is being re-run as Steve is on holiday.

I once asked a Microsoft employee, in some exasperation at the absence from T-SQL of a much-needed function, how the decision was made to add one. He described a byzantine struggle to convince a panel of sage panjandrums within Microsoft, long on academic status but, I imagine, short on experience in the stark reality of developing database applications under pressure.

I suspect that success in swaying the "judgement of the wise" depended more on your persuasive powers in extolling the virtues of the feature, than on the its real value to the end user. The rich variety of ObjectPropertyEx(), for example, seems to be more about the convenience of 'internal' use rather than for the benefit to us.

By contrast, it has taken twelve years of persuasion to get the expedient String_Agg() aggregate function and Translate() into TSQL (SQL Server 2017). We get a trickle of useful functions. The SQL Server 2012 functions for converting between datatypes, Try_Convert(), Try_Cast() and Try_Parse() were very handy, and everyone loves the String_Split() function in SQL Server 2016. All these have been easy to implement, but have taken a long time to get through the decision-making process.

When you look through the new crop of functions that appear in every release, it typically seems to be a mixture of the sublime and gorblimey. I’m sure that some of you use the IIF function rather than the CASE construct it is translated into, but it isn’t exactly exciting. The Choose() function seems to be a lifebelt thrown to a struggling procedural programmer. The Trim() function still doesn’t behave the same way as in any other language.

Who are the sage mandarins of Microsoft that for so long frowned on String_Agg() yet smiled on IIF? I sometimes wonder if they look at the world through some distorting mirror, as they gaze on the misbegotten implementation of JSON (‘where are the lists?’), and smile at a job well done.

We have, in PASS, a professional association of users of SQL Server, with a mission to spread knowledge about the use of the Microsoft Data Platform. Surely, amongst the worthy dignitaries of this august society, there are many who are uniquely qualified to assign a value to a proposed extension to TSQL. To be sure, we aren’t all professors, but we are mostly graduates of the school of hard knocks, with a life at the hard end of SQL Server development, with impossible deadlines, implacable mangers and improbable developers. There is, surely, the necessary talent that we can elect to listen to a pitch for a new function or feature in Transact SQL, and to ace the decision every time.

Phil Factor.

Phil Factor

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

Featured Contents

Implementing Drill Through Actions in SSAS

Archana from SQLServerCentral

Business users get great value from SSAS cubes. In this article, learn how to create a drill-through action in an SSAS multi-dimensional model cube.

Storage 101: Understanding the Hard-Disk Drive

Additional Articles from SimpleTalk

In this article in the series, Robert Sheldon provides a deep dive into HDDs.

Why you Should Always Specify Whether a Column Accepts Nulls

Additional Articles from Redgate

Phil Factor explains the factors that determine whether a column will allow null values, if you don't specify it explicitly in the column definition. If you rely on the default behavior established by your connection settings, you could be in for some nasty surprises.

From the SQL Server Central Blogs - Let’s Call It What It Is: Complexity Debt

K. Brian Kelley from Databases – Infrastructure – Security

Technical debt get deprioritized too often because business sees the word, “technical.” It doesn’t matter what the true meaning is. And it’s a big problem. It really needs proper...

From the SQL Server Central Blogs - Query Store, Plan Forcing, and DROP/CREATE

Grant Fritchey from The Scary DBA

I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Original Database
In SQL Server 2017, what value does ORIGINAL_DB_NAME() return for a login?
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)

Identity Values

I have this query that I run in my database:

SELECT
               SchemaName = OBJECT_SCHEMA_NAME(t.object_id, DB_ID())
             , TableName = t.name
             , ColumnName = c.name
             , ic.last_value
FROM
               sys.tables AS t
    INNER JOIN sys.columns AS c
        ON t.object_id      = c.object_id
    INNER JOIN sys.identity_columns AS ic
        ON ic.column_id     = c.column_id
           AND ic.object_id = c.object_id
WHERE          c.is_identity = 1;

What does the ic.last_value represent?

Answer: The last value used in an insert in this field from the identity property

Explanation: The sys.identity_columns DMV contains the columns from sys.columns, but also includes other data about identity fields. The last_value is the last value inserted into the table. The next value is the last_value with the increment added to it. Ref: sys.identity_columns - https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-identity-columns-transact-sql?view=sql-server-ver15

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

AG Listeners - multiple instances - Hi, I'm looking after a cluster that has 2 instances; one for 2016 and one for 2017. The AG listeners are both configured to use port 1433 and everything is working fine? Customer wants a separate instance of 2017 - how do listener ports relate to SQL ports? In the SQL port world named instances […]

SQL Server 2016 - Development and T-SQL

Datatype differences when restoring a database from one server to the next - Hello, I'm performing a backup/restore operation from a SQL Server 2012 database server to a SQL Server 2016 database server. The database is currently set to SQL Server 2008 compatibility within both database servers using the same 'SQL_Latin1_General_CP1_CI_AS' collation from the server level to column level. Now when the database finished restoring on the 2016 […]

Roll multiple records into 1 - I'm trying to roll multiple records into one replacing null value. IF (SELECT OBJECT_ID('tempdb..#Table50'))is not null DROP TABLE #Table50 CREATE TABLE #Table50 ( MyID int, A int, B int, C int) INSERT INTO #Table50 ( MYID, A, B, C) SELECT 1, NULL,NULL, 53 UNION ALL SELECT 1, NULL,NULL, 54 UNION ALL SELECT 1, NULL,924, NULL […]

SQL UniqueIdentifier question - Hello, Our reporting database truncates and loads latest data during each import and generates Unique Identifiers for Tasks and Project UIDs . Example: ProjectTable TaskTable We have a SharePoint list where incremental updates are done using TaskUID from Task Table and ProjectUID from Project Table. If with every refresh, Project and Task tables regenerates […]

Extract string from string that contains specific first two characters - Hi I would like to extract from a full string the 'XX....' string. This specific string inst in the same position in my full string. - this string always starts with XX - this string always contains 6 characters thanks in advance,

Administration - SQL Server 2014

Upgrading SQL 2014 to SQL 2017 cluster - Hi All, Not sure whether to post it here or in SQL 2017, but here goes.... We have a Win 2012 R2 / SQL 2014 cluster for a mission critical database. It's a 3 node cluster with nodes in different subnets. As DR we logship to a 4th off site server. We want to upgrade […]

SQL Server 2012 - T-SQL

View Dependencies - I have SQL Server 2012 and running the following query. THe results do not include all dependencies. I have a stored proc that calls a view, but that does notshwo up. How do I get full dependency list? SELECT ReferencingObjectType = o1.type, ReferencingObjectName = o1.type_desc, ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name, ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name, ReferencedObjectType = o2.type, ReferencedObjectName […]

SQL Server 2019 - Administration

MIGRATE SQL 2014 to 2019 (not UPGRADE) - Anyone had experience migrating a SQL Server to the latest version? We have 2 servers, SQL 2014 SP3-CU4 + Windows Server 2012 R2 and would want to migrate both server to SQL 2019 + Windows Server 2016/2019. Have to point out the following: The servers has a transactional replication running. The subscriber server is also […]

SQL Server installation with British English - What little hair I have left is being torn out in chunks trying to get to the right part of Microsoft to get a reasonable response, so I'm hoping you helpful lot here can answer my problem. When I try to install SQL Server 2019 and look at the instance properties, I see it has […]

Availability groups and sql agent - I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node. I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario so my plan was to […]

SQL Server 2008 - General

Continuously current list of attached files in SQL Server - This isn't strictly a SQL Server question, but there are some pretty sharp people on here, and I'm probably not the only one with a problem like this. I have a database that has associated files in a folder/subfolder structure on the server – Windows Server Standard, 2007, SP2. The associated files are mostly […]

Integration Services

SSIS Class not registered - Hello, I installed SSIS on a server by itself, it is 2016 version 13.0 version I followed permission directions from the link below: https://docs.microsoft.com/en-us/sql/integration-services/grant-permissions-to-integration-services-service?view=sql-server-2014 I tried to connect to SQL management studio version 16.3 version, to SSMS 18.4, and I get the following error message, I attached it. not sure why or what to do […]

SQL Server 2005 General Discussion

Attachments EMail - I'm having trouble trying to get my query results loaded into my excel attachment can anyone help. DECLARE @sub VARCHAR(100) DECLARE @qry VARCHAR(1000) DECLARE @msg VARCHAR(250) DECLARE @query NVARCHAR(1000) DECLARE @query_attachment_filename NVARCHAR(520) SELECT @sub = 'DC Weekly Transfer' SELECT @msg = 'This is Just a test.' SELECT @query_attachment_filename = 'dcweekly.csv' Declare @nOrgID int = […]

my query results are not in my excell attachment can anyone help - Hello, I have a query here that is sending a excell attachment to a email the only problem is my results are not being put into that attachment also how do I distinguish this when I create a Agent Job what parts do I put where in my agent job. DECLARE @sub VARCHAR(100) DECLARE […]

General

Trying to Sum by different levels - Hello everyone, If any one could please help me with this? I'm trying to add additional sum by Family and Sum by Store level Trying to get this result It seem that i only know how to use group by at the end of my statement.

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.