SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

The Dangers When Changing Database File Names

Steve Jones - SSC Editor from SQLServerCentral

Recently a friend had an issue with their SQL Server as a result of a file change in their SQL Server database. In this short piece, I want to highlight the dangers of making some changes to an online database that might cause an unexpected outage. This is really the same whether you are changing […]

Refactoring Databases with SQL Prompt

Additional Articles from Redgate

Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such as renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).

The Demise of the DBA

Additional Articles from Database Journal

Today most of the tasks done by traditional DBAs are performed by artificial intelligence systems or the database itself. With so few important tasks left to perform, are DBAs really needed anymore?

From the SQL Server Central Blogs - What about orphaned windows users?

SQLPals from Mission: SQL Homeostasis

I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution...

From the SQL Server Central Blogs - The Azure-SSIS IR Startup Time

Koen Verbeeck from Koen Verbeeck

If you want to run SSIS packages in Azure Data Factory, you need the Azure SSIS Integration Runtime (quite the mouthful), which is basically a cluster of virtual machines...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	New statistics
I create a table and add some data with this code: CREATE TABLE dbo.Ages (AGE INT) ; INSERT INTO dbo.Ages (AGE) SELECT TOP 188 ABS(CHECKSUM(NEWID())) % 10 FROM sys.objects ; SELECT AGE FROM dbo.Ages ; Auto create statistics is enabled, as is auto update statistics. What happens when I run this code? SELECT object_id , name , stats_id , auto_created , user_created , no_recompute , has_filter , filter_definition , is_temporary , is_incremental FROM sys.stats WHERE object_id = OBJECT_ID(N'dbo.ages', N'U') ;
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)

The special columns

If I run sp_special_columns on a table, what is returned?

Answer: The columns that uniquely identify a row or columns updated automatically

Explanation: This procedure returns the columns that uniquely identify a row (primary key columns) or those that are automatically updated when a value changes in the row. Ref: sp_special_columns - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-special-columns-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

Featured Script

Count non-NULL rows and get definition

Thom A from SQLServerCentral

Get a count of non NULL rows.

CREATE OR ALTER PROC dbo.CountAndDefinition @Schema sysname, @Table sysname AS
BEGIN
DECLARE @SQL nvarchar(MAX)
SET @SQL = N'WITH Counts AS (' + NCHAR(13) + NCHAR(10) +
N' SELECT @Schema AS SchemaName,' + NCHAR(13) + NCHAR(10) +
N' @Table AS TableName,' +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' COUNT(' + CASE WHEN C.DATA_TYPE IN ('text','image') THEN '1' ELSE QUOTENAME(C.COLUMN_NAME) END + N') AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = @Schema
AND C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,14,N'') + NCHAR(13) + NCHAR(10) +
N' FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N')' + NCHAR(13) + NCHAR(10) +
N'SELECT V.ColumnName,' + NCHAR(13) + NCHAR(10) +
N' V.NonNullCount,' + NCHAR(13) + NCHAR(10) +
N' ISC.DATA_TYPE + ISNULL(NULLIF(DT.S,''(*)''),'''') AS Datatype,' + NCHAR(13) + NCHAR(10) +
N' K.KeyType' + NCHAR(13) + NCHAR(10) +
N'FROM Counts C' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY(VALUES' + STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' (N' + QUOTENAME(C.COLUMN_NAME,'''') + N',C.' + QUOTENAME(C.COLUMN_NAME) + N')'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @Table
--AND C.DATA_TYPE NOT IN ('text','image')
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,26,N'') + N')V(ColumnName,NonNullCount)' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.COLUMNS ISC ON C.SchemaName = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND C.TableName = ISC.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND V.ColumnName = ISC.COLUMN_NAME' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY (VALUES(''('' + STUFF(CONCAT('','' + CASE ISC.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN ''MAX'' ELSE CONVERT(varchar(4),ISC.CHARACTER_MAXIMUM_LENGTH) END,' + NCHAR(13) + NCHAR(10)+
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_PRECISION) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT LIKE ''%int'' THEN CONVERT(varchar(4),ISC.NUMERIC_SCALE) END,' + NCHAR(13) + NCHAR(10) +
N' '','' + CASE WHEN ISC.DATA_TYPE NOT IN (''datetime'',''smalldatetime'') THEN CONVERT(varchar(4),ISC.DATETIME_PRECISION) END),1,1,'''') + '')'')) DT(S)' + NCHAR(13) + NCHAR(10) +
N' OUTER APPLY(SELECT TC.CONSTRAINT_TYPE AS KeyType ' + NCHAR(13) + NCHAR(10) +
N' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC' + NCHAR(13) + NCHAR(10) +
N' JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = KCU.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
N' AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME' + NCHAR(13) + NCHAR(10) +
N' WHERE KCU.COLUMN_NAME = V.ColumnName' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_SCHEMA = ISC.TABLE_SCHEMA' + NCHAR(13) + NCHAR(10) +
N' AND TC.TABLE_NAME = ISC.TABLE_NAME) K;';
PRINT @SQL; --you will need to use the SELECT here if @SQL is over 4,000 characters
--SELECT @SQL;
EXEC sp_executesql @SQL, N'@Schema sysname,@Table sysname',@Schema = @Schema, @Table = @Table;
END;

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

How do I GRANT permissions on these? - "Accidental" DBA here and we are trying to improve our security. We have created a new user that has far more limited security for a particular purpose than the one previously used, but I need to GRANT the following to the new user: User needs to SELECT on sys.procedures and be able to execute OBJECT_DEFINITION() […]

DW - Data Extraction RI issue - We are taking approach for Extracting data from source to target but have your advice. Our approach is: 1) Drop the RI Constraints 2) Truncate the Tables 3) ReCreate the RI Constraints 4) Extract data from source to target Now confusion is during this process before or during recreating RI constraints if we loose DB connection or […]

SQL Server 2016 - Administration

Key Lookups - I'm trying to come up with a script that will identify key lookups that have a certain percentage of the query cost. Like if the key lookup is 50% of the query cost then I want the result set.

SQL Server 2016 - Development and T-SQL

Procedure to find record in same table in multiple databases - I need to be able to return a recordset of server name and database name for all databases that have the 'humres' table which contains a record in the table with a column 'usr_id' with value of a passed in username param. i.e. create proc getDBs(username varchar(8)) AS ( ..... )

index usage help - Hi All, Will any index will be used if we use functions on a column referred in the where, what is the best way to re-write this condition? SELECT..... WHERE to_char(lastUpdatedDate,'YYYY-MM-DD')>=@dt Please suggest. Thanks, Sam

System.Net.WebException: 'The request failed with HTTP status 403: Forbidden.' - Post was not responded to here, so I moved it here: https://www.sqlservercentral.com/forums/topic/system-net-webexception-the-request-failed-with-http-status-403-forbidden-2

Administration - SQL Server 2014

Question related to AG's - Hi All, We are using SQL Server 2014 Enterprise Edition. We have 2 node Availability groups for high Availability purpose. One node as primary and one for secondary. Now the plan is to have a DR setup which is a standalone sql instance which is remotely located. Can we setup log shipping between an AG […]

The MSSQL Standard edition, does it limit the number of Work Threads? - I have a server with 128 logical processors, 64 bits server, and with a Standard edition of SQL Server 2014, and my question is this: Considering that SQL Server connects an ERP with a high number of simultaneous connections (approximately 1,000), and the characteristic "Maximum calculation capacity" for each of the editions of SQL Server, […]

Development - SQL Server 2014

simple query question, using ROLLUP instead of COMPUTE - A database was upgraded from 2008 to 2014 sqlserver, a report that was running using COMPUTE to summarize totals quit working, as this function was deprecated with SS 2012. I wrote this query some years ago, adapted from an Oracle database I was pulling similar information from. The original query gave nice totals by SystemUser, […]

SQL Server 2012 - T-SQL

Indexing question - Hi All, What is the main difference between normal index rebuild and online index rebuilds? is there any performance benefits ? does it avoid blocking on large tables ? What actually happens behind in offline index rebuild and online index rebuild. Trying to see if anyone can explain in layman terms. Its quite overwhelming if […]

SQL Azure - Development

Azure Data Migration - Hi New to Azure and looking for the best options/approach to transfer on premise data to Azure BLOB (initial load about 1 - 5 TB) and also incremental changes thereafter. Can you please provide the best options available? Thanks

SSRS 2016

System.Net.WebException: 'The request failed with HTTP status 403: Forbidden.' - When my application code goes to set the parameters for a reportviewer control for an SSRS report, I get the System.Net.WebException: 'The request failed with HTTP status 403: Forbidden.' error Not sure why. I've set the credentials to the default network credentials, and I know have rights to see that report. Wondering why I can't […]

Integration Services

Executing Flat file query - Hi , I have table/procedure/function script in flat file, i'm looking for solution to execute scripts (flat file) in SQL server db using SSIS. I have tried to use foreach loop - flat tile connection and execute SQL task - seems incorrect. Any help much appreciated, Thank you!

SSIS version - I recently opened a SSIS soltion that is developed by another developer. And I modified a package in it, then saved in subversion. But I open again, we cannot open the script component. And later we found out that in the source file of the ssis package, my version is different version than the other […]

SQLServerCentral.com Announcements

Status Update 2 Jul 2019 - Things have slowed down, so not a lot of progress to report, but a few new items logged, so worth tracking here again. Fixes Admin payment report time zones match browsers contribution center performance issue and wording changes Search is getting close to a beta launch, but still waiting on some Google crawls. They haven't […]

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.