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

SSMS or SSDT

This editorial was originally published on Mar 27, 2015. It is being republished as Steve is traveling.

I was asked recently if I thought more people used SSMS or SSDT. I really had no idea, so I thought this would make a good poll.

Do you use SSDT or SSMS for your database work?

I'm sure there are plenty of developers that work on SQL Server from Visual Studio, so if you do that, let us know. Please feel free to pass this along to friends as well. I'm curious what people use.

SSDT grew out of the Data Dude, a project at Microsoft to give us a better development tool for SQL Server. It was a neat project, and I think it produced something that has become very popular because it made database development easier for some people, mostly .NET developers.

However many of us that work with SQL Server have spent most of our time in Management Studio, and while it could use some work, it has become a comfortable environment in which to write T-SQL code. A few of us remember Enterprise Manager, and perhaps a few others still bemoan the demise of isql/w.

I'm wondering if there is a trend here. I've tended to stick with SSMS, but if most of you are using SSDT, perhaps I should invest some time in learning it better. Let us know this week what you use, why, and what advantages it gives you.

Steve Jones - SSC Editor

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

 
Redgate University
  Featured Contents

Watching Out for Deferred Name Resolution

Tony Davis from SQLServerCentral

Deferred Name Resolution can be useful in writing ...

SQL For Cosmos DB – Handling Complex JSON Structures

Additional Articles from SimpleTalk

The JSON documents that are part of Cosmos DB document collections can be complex with arrays and nesting. In this article, Adam Aspin shows you how to query them with SQL.

From the SQL Server Central Blogs - Demo: Predictive Modeling using R and SQL Server Machine Learning Services

Arjun Sivadasan from SQL Roadie

Late last year, I wrote a series of articles about Predictive Modeling using R and SQL Server Machine Learning Services. At the time, I thought MLS was an underutilized...

From the SQL Server Central Blogs - Container: More on Volumes

Grant Fritchey from The Scary DBA

In my last post I showed how you can create a volume with your container. I then showed a few things you can with a container using a volume....

 

  Question of the Day

Today's question (by Thomas Franz):

 

Temporary data and Differential Backup size

I fill and empty some temporary staging tables inside my database between a FULL backup and a DIFFERENTIAL backup. Will this increase my differential backup size? This is the type of work I am doing:
IF DB_ID('test') IS NULL CREATE DATABASE test
GO
USE test
GO
-- each row will be 4011 byte long, so only 2 records will fit onto one page
CREATE TABLE dbo.test_for_backup (id INT NOT NULL IDENTITY PRIMARY KEY, txt CHAR(4000) NOT NULL)
GO
-- create the FULL backup
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest1.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- insert 10k records, it will fill 5000 pages a 8kB = 40,000 kB = 3.9 MB
INSERT INTO dbo.test_for_backup (txt)
SELECT CAST(ROW_NUMBER() OVER (ORDER BY t1.n) AS VARCHAR(10)) AS txt
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t1(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t2(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t3(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t4(n)

-- remove the records again
TRUNCATE TABLE dbo.test_for_backup

-- what do you think, how many pages the DIFFERENTIAL backup will process?
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest_diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Think you know the answer? Click here, and find out if you are right.

 

Redgate SQL Provision
 

 

  Yesterday's Question of the Day (by Junior Galvão - MVP)

Using temporary tables with OUTPUT clause

I execute the code block below:

-- Create Table MyTable --
Create Table MyTable
 (ID SmallInt Identity Primary Key Clustered,
  Numbers Int,
  CreationDate Date,
  ManipulationDate Date)
Go
  
-- Insert Rows --
Insert Into MyTable(Numbers, CreationDate, ManipulationDate) 
 Values(10, GETDATE(), GETDATE()+1),
       (20, GETDATE(), GETDATE()+2),
       (30, GETDATE(), GETDATE()+3),
       (40, GETDATE(), GETDATE()+4)
Go

-- Create Temporary Table #MyTempTable --
Create Table #MyTempTable
 (ID SmallInt Identity Primary Key Clustered,
  Numbers Int,
  CreationDate Date,
  ManipulationDate Date)
Go

-- Update Rows with Output clause --
Update MyTable
Set ManipulationDate=DateAdd(d,5,ManipulationDate)

Output Inserted.Numbers, 
       Inserted.CreationDate, 
	   Inserted.ManipulationDate
 Into #MyTempTable

Go

What will be the returned  in SQL Server 2017?

 

Answer: (4 rows affected)

Explanation: The correct answer is: (4 rows affected) Explanation: Unlike the Select Into command, we do not need to create the previous table that will receive the data processed by the Select command.

When we are using the OUTPUT clause, it is mandatory to create this table so that we can insert the results for the output table.
 
According to the Microsoft documentation: output_table - Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.
 
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017
 
If you want to verify, delete the temporary table #MyTempTable, and rerun the Update command.
 
The following error message will be returned:

Msg 208, Level 16, State 0, Line 26 Invalid object name '#MyTempTable'.

 
 

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
Legacy Cardinality Estimation - hello , what is your best practice for the Legacy Cardinality Estimation parameter The default value of the Legacy Cardinality Estimation is OFF What will be the impact if I proceed to activate it ? thanks
Install failed ... but didn't? - I finally sat down to install 2017 on a clustered server and got a failure message that said "setup attempted to perform an unauthorized operation." So I opened up the detail.txt file and found the relevant section: (01) 2019-06-10 12:48:56 Slp: ---------------------------------------------------------------------- (01) 2019-06-10 12:48:56 Slp: Running Action: AS_Telemetry_Install_StartupFinalize_Cpu64 (01) 2019-06-10 12:48:56 Slp: Action Data: […]
SQL Server 2016 - Administration
CImageHelper::Init () Failed load of symbol file with path = - This error has occurred 2-3 times each night for the past 4 nights CImageHelper::Init () Failed load of symbol file with path = A:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\;A:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BINN Error Code 0xc0000004 This is SQL 2016 enterprise 16 cores, 384 GB RAM. It's a three node cluster in an availability group.  This is the […]
Upgrade from SQL Server 2008R2 to SQL Server 2016 sp 2 performance issues - We have just upgraded our production SQL Server from 2008R2 to 2016 sp 2 and are experiencing some performance issues on our background processes.  We did not change the compatibility levels on our databases so they are still 100. The Legacy Cardinality Estimation is off and so is the query optimizer fixes. We left the […]
setup log shipping or replication from 2014 to 2016 - Hi all,   I have an on premise VM on which SQL server 2014 standard edition running which has databases in 3 TB approx, its a prod environment which is used to run mostly analysis and report kinda query. it gets input data from third party replication tool continuously.   There  is one more VM […]
SQL Server 2016 - Development and T-SQL
Retry logic for deadlock in while loop - Hi DBAs, I have a procedure to perform certain operations (insert and update in a table) in while loop with if...else conditions. At times, the operation fails with deadlock for any iteration. I am planning to implement retry logic but want to know how to do it with while loop. Please help me. In simple […]
How to update a table that causes temporary duplicate index - I have a table with a column that stores a set of sequential numbers and is an index with no duplicates. i need to know how to update those number by adding a select value to it. example: the column in question has 0,1,2,3,4,5,6,7,8,9,10.... lets say i need to add 5 to that column for […]
Administration - SQL Server 2014
Locating SSIS packages within SSISDB - A colleague is away travelling and an SSIS package they manage has started failing. I have opened up SSIS on the server and cannot see this package listed under either within file system or MSDB. When I look at the failing SQL job the step executes the package at a location that begins with /ssisdb/ […]
SQL Server 2012 - T-SQL
SQL ROW COUNT AND CTE HELP PLEASE - Hello, I am new with this I need help with this CTE, I need a report of all the Invoices paid, I am using 3 tables here but this CTE with ROW COUNT is taking the duplicates await. Usually you paid vendor twice a month or three times. so I need to see all records […]
Reporting Services
How to increase the timeout against a MySQL database - I've been tasked with writing a SSRS report against a MySQL database. This is the first time I've ever worked with MySQL, so new experiences for me. With the help and recommendation from one of my colleagues, I've written a SSRS report against the largest table in the database, as a proof-of-concept type of thing. […]
SSRS 2016
change the color each time the name of the server changes - hello , I have a report ssrs which gives me the state of the backup I would like to change the color of the line at each I change the name of the server who has an idea on a tip that can answer my need    
Header alignment issue - Hello, I have used a rectangle in report header and a tablix in my report body. If I fix the alignment in report preview, report pdf gets misaligned and vice versa. I have tried following- Set report width according to page width and margins manually setting the position Using table instead of a matrix   […]
Powershell
Powershell script to get the local adminstrators liston multiple windows servers - Hi   Could you please provide a PowerShell script to loop through multiple servers and get the list of local administrators of all servers? invoke-command {net localgroup administrators} -comp The above powershell command lists from one server. please provide a script to run on multiple servers.   Thanks San      
Integration Services
PSFTP using SSIS - hi   i m using psftp utilitie to post file on sftp server using execute process task.Its running fine on my local machine when i schedule job in sql server agent , it just keep running and block whole DB. is there a permission issue, we r using a service account to schedule job that […]
SSIS 2016 Excel stops working - Hello, I am using SSIS 2016 and Visual studio 2015 to create an SSIS package that just gets an excel document, and imports it into a SQL table, using the connection a variable (hope this makes sense or using correct vocabulary). However almost every other day or week, 1 column from the excel keeps erroring […]
 

 

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

 

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