Unit Testing with SQL Server (SQLServerCentral 3/1/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Unit Testing with SQL Server]( - [Getting Started with Power BI - Part 1]( - [Calling all Oracle Database users]( - [Why make?]( (From the SQLServerCentral Blogs) - [What is my why?–T-SQL Tuesday #111]( (From the SQLServerCentral Blogs) The Voice of the DBA Schema Security How many of you grant access to schemas in your databases? I'm curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to "dbo". That was the default action for many developers for years, and it's still a habit of mine. Without SQL Prompt to add the schema to my queries, I'd still be producing code that followed that practice. Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn't have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects. This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data. This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to [learn a bit more about how schemas work]( and the ways in which they may make your work easier. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 2.9MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( Featured Contents  [] [Unit Testing with SQL Server]( Alessandro Alpi from [SQLServerCentral.com]() Learn how to begin unit testing with tSQLt and SQL Server. [More »](Testing/123900/) ---------------------------------------------------------------  [] [Getting Started with Power BI - Part 1]( Additional Articles from [MSSQLTips.com]() In this tip we will show how Power BI works and how to create a simple report from scratch and the options available for creating reports.[More »]( ---------------------------------------------------------------  [] [Calling all Oracle Database users]( We’re excited to announce that Redgate is doubling its efforts on Oracle and committed to aligning its DevOps solution across both SQL Server and Oracle. Please take a few minutes to complete this short survey to stay up-to-date on our Oracle developments.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Why make?]( Bert Wagner from [SQLServerCentral Blogs]( This post is a response to this month’s T-SQL Tuesday #111 prompt by Andy Leonard. T-SQL Tuesday is a way...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [What is my why?–T-SQL Tuesday #111]( Steve Jones from [SQLServerCentral Blogs]( This is an interesting question from Andy Leonard for T-SQL Tuesday: What Is Your “Why”? He is this month’s host...[More »]( Question of the Day Today's Question (by Steve Jones): On SQL Server 2017, I have a brand new database with no objects. I run this code: CREATE PROCEDURE GetOne AS SELECT 1 GO CREATE PROCEDURE GetOne;2 AS SELECT 2 GO I now run this. DROP PROCEDURE GetOne What happens when I run this? EXEC dbo.GetOne;2 Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: Stored Procedures. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server]( Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server helps you bring the productivity and user-satisfaction of flexible and responsive applications to your organization safely and securely. Your organization’s increased ability to respond to rapidly changing business scenarios will build competitive advantage in an increasingly crowded and competitive global marketplace. With a focus on new applications and modern database architecture, this edition illustrates that dynamic SQL continues to evolve and be a valuable tool for administration, performance optimization, and analytics. [Get your copy from Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have created this table in SQL Server 2017. CREATE TABLE dbo.SalesOrderHeader ( OrderKey TINYINT IDENTITY(1, 1) , CustomerName VARCHAR(30) ) GO INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Andy') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Brian') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Steve') GO I now have completed testing, so I do this: TRUNCATE TABLE dbo.SalesOrderHeader GO DBCC CHECKIDENT('dbo.SalesOrderHeader', RESEED, 0) GO If I run this code, what value is returned for IDENT_CURRENT() and what value is added for the OrderKey for the row with Azure? SELECT IDENT_CURRENT('dbo.SalesOrderHeader') GO INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Azure') SELECT * FROM dbo.SalesOrderHeader AS soh GO Answer: IDENT_CURRENT = 0 and OrderKey = 0 Explanation: In this case, when the RESEED takes place, the current value is set to 0. When all rows are removed from the table, the SEED is used for the identity value. Ref: IDENT_CURRENT() - [click here]( DBCC CHECKIDENT() - [click here]( Identity Property - [click here]( --------------------------------------------------------------- [» 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]( : [SQL Server 2017 - Administration]( [SQL View - Add users and grant permissions syntax assistance]( - I have a SQL View that I need to add to a clients server. I need help adding users to... [Excessive disk usage. SS2017 Express.]( - I've got some dbs here that seem to be taking up more space than they should. In one inistance if... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [TSQL Help with a Stored Procedure.]( - I need help with my below mentioned Stored Procedure - specific to the field in my dataset. Per my clients specifications,... [WHERE Clause exclusions, best practices.]( - Hello, I was just wondering if anyone has a good idea on how to implement a set of filters that we... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [DBA left and they shut off his AD account]( - Our DBA left and they shut off his account. Of course, things started failing all over the place so I... [Always On]( - I know we can have 4 secondary database using Always on. However, i am little unsure that 4 secondary means... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Nested while select on same table]( - Hi I try to get fieldnames from the subquery to print a script for adding default constraints to all tables. It does... [How to use STUFF and FOR XML PATH to get a list of column names IN ORDER BY COLUMN_ID]( - I am writing some fairly complicated dynamic SQL for a stored procedure that needs to be multi-purpose. As such, I... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [SQl 2014 instance is DOWN all USER databases will not restore and SQLAGENT will not start. Urgent help required..]( - VMware ESXI 6.5 Windows 2012 R2 Data Center Cluster 2 Nodes SQL  2014 Enterprise I have iSCSI drives used for failover all works... [Verify Read Only Routing using Profiler]( - How can I verify the readonly routing is working fine with Profiler? I connected to listener using Application Intent=Read Only and... [ODBC Driver 17 for SQL Server - Linked Server failure]( - I have a problem where my linked server works for about 12 minutes and then fails. Sorry I do not... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [why did WHERE EXISTS delete the contents of whole table?]( - the inner query returns only 36 rows, so I expected the DELETE WHERE EXISTS to delete only 36 rows. But... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Various ODBC File Data Type Images must be written in the proper format on to disk. SQL Server 2012 Enterprise]( - Hello Folks. In a table are stored over 1 million ODBC documents (doc, pdf, excel ). I want to write all this... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Find last record in last 12 months of current record]( - I need to build a solution and for brevity I'm going to use the example of customers buying products. I want... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [ssrs 2010 shared dataset]( - In an existing ssrs 2010 report there is a shared dataset that I would like to know how it works.... --------------------------------------------------------------- [Programming]( : [XML]( [Importing an XML file into a sql table]( - Edit to make things more understandable, I wish to import my XML file into an sql table. The XML has a... --------------------------------------------------------------- [Programming]( : [Powershell]( [List edition and version of all SQL Server machines over the Network ...]( - In our organization, we have more than 100 SQL Server machines on different regions over the Network/LAN/WAN. We have restricted... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [SSIS 2017 connection to Access .accdb file]( - Sorry, I'm an SSIS noob. I've connected to .MDB files in the past using the Jet 4.0 connector, and it... [Tips for avoiding stored passwords with DontSaveSensitive?]( - Hi all, We have an in development data warehouse project in SSIS 2012 and I am trying to set up our... --------------------------------------------------------------- [SQLServerCentral.com]( : [SQLServerCentral.com Website Issues]( [Please move the "CLR Integration and Programming" forum to the "Programming" group]( - Hello. If at all possible, please move the "CLR Integration and Programming" forum to be in the "Programming" area / group. It... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com