Is This Database Being Used? (SQLServerCentral 3/29/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Is This Database Being Used?]( - [Help us define the state of SQL Server monitoring in 2019]( - [Are nulls stored in a nonclustered index?]( - [Dipping into the Cookie Jar]( (From the SQLServerCentral Blogs) - [The T-SQL Tuesday #112 Cookie]( (From the SQLServerCentral Blogs) Featured Script - [VIEW to list running and completed jobs]( The Voice of the DBA Trunk Based Database Development Early in my career we had a folder that contained our source code. Each of us developers would copy the folder to our workstation and work on our code. To prevent issues, we had read only access to the main folder. When we thought work was finished, we had to set up a meeting with two other developers, print our code out, and go through a review. If we got two sign offs, we would send our code to a manager to copy into the main folder. Since we were assigned work in different areas, conflicts were rare and this mostly worked, although it was slow. Over time I evolved to use various VCSes (Version Control Systems) for development work, with different paradigms for working in teams. These at times included code reviews, but like comprehensive testing, these were optional (and often ignored) when time was short. As an industry, I think we've gotten better with code reviews in some sense. The popularity of git and the integration of pull requests (PR) has made it more likely that others will review your code as a habit rather than a rare activity. I certainly see constant calls for someone to examine a PR in the Redgate Slack channels. I think this is a good move, but I was listening to a few people talk about trunk based development recently, which has less of a chance for code reviews. In this model, developers commit their changes directly to the copy of code everyone is using. In a PR model, developers work in branches, and only have a PR is reveiwed, is the changed code merged with existing files. Trunk based development is a more trustworthy model. We trust developers to make changes to the code everyone is using. This is often what many of us do in database development when we have shared development databases. A change made by one developer is visible and affects everyone else. While some organizations struggle with conflicts, this does ensure that problems are visible early and that your team must learn to communicate with each other. One way isn't better than the other, and if you aren't familiar with one of them, give it a try. Work on a small PoC with someone else and experiment with how well you can make changes and what conflicts come up. Many of us get stuck developing code in a singular way unless someone forces us to change. The software development process has evolved a bit over the years and you might find that a different style of putting code together may suit your team better. 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]( ( 4.0MB) 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 Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [SQL Provision]( SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps With SQL Provisions virtual cloning technology, databases can be created in seconds using just 1MB of storage, and sensitive data can be anonymized or replaced with realistic data to ensure it is protected as it moves between environments. [Download your free trial]( Featured Contents  [] [Is This Database Being Used?]( Ginger Keys Daniel from [SQLServerCentral.com]() Can we delete this database, or is someone still using it?[More »]( ---------------------------------------------------------------  [] [Help us define the state of SQL Server monitoring in 2019]( Press Release from [Redgate]( Last year we ran the industry’s first ever state of SQL Server monitoring survey, the results not only gave great insight for the community but helped us shape our product development to ensure we were meeting the needs that you were asking for. So what has changed in a year? Tell us for your chance to win a $250 Amazon voucher. [More »]( ---------------------------------------------------------------  [] [Are nulls stored in a nonclustered index?]( Additional Articles from [Brent Ozar Unlimited Blog]( When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Dipping into the Cookie Jar]( Bert Wagner from [SQLServerCentral Blogs]( This post is a response to this month’s T-SQL Tuesday #112 prompt by Shane O’Neill. T-SQL Tuesday is a way for the SQL...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [The T-SQL Tuesday #112 Cookie]( Steve Jones from [SQLServerCentral Blogs]( It’s T-SQL Tuesday again, and I’m writing in response to Shane O’Neil’s invitation about cookies. Read what he asks and...[More »]( Question of the Day Today's Question (by Steve Jones): I have this code: DECLARE @cmd NVARCHAR(1000) = N'SELECT oh.OrderKey, oh.OrderTotal FROM dbo.OrderHeader AS oh WHERE CustomerKey = @CustomerKey'; DECLARE @params NVARCHAR(1000) = '@CustomerKey int'; EXEC sp_executeSQL @cmd, @params What's wrong with it? 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: sp_executesql. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have this code: DECLARE @cust TABLE ( CustomerID INT NOT NULL INDEX custidx CLUSTERED , CustomerName VARCHAR(100) INDEX custnameidx NONCLUSTERED); INSERT @cust SELECT c.CustomerKey, c.CustomerName FROM dbo.Customer AS c; What happens when I run this? Answer: The variable is created and multiple rows are inserted Explanation: Inline indexes are allowed on various structures, including table variables. The table variable is created and the rows inserted from the table. Ref: What are Inline Indexes - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [VIEW to list running and completed jobs]( Dean Bell from [SQLServerCentral.com]() Install the view and run as-is. If you don't have permissions to run 'ad hoc distributed queries', you need to run this: ----------- EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO ----------- I've tested this on 2016 with no issues. -Dean [More »]( 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]( [I'm having trouble an sql command.]( - The question is "Find the names of courses that are prerequisites for other courses. List the course number and name,... [Full-Text Search Not Updating]( - Hi Having issues with FTS. Installed 2017 on Server, RTM- CU9, Os is Windows Server 2016. The issue is that FTS is not updating. Have... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Declare start date as yesterday and end date as today]( - HI Guys, I found the below in a forum which i thought worked great until i drilled down to the data. ... [How to Mark a case as a 're-open' if the same Case Id occurred prior to the one being looked at]( - I have been trying a few various methods to see if I can get this to work, but I haven't... [Creating a non-unique index]( - If you don't specify Unique, then by default a non-unique index is created. But suppose I wanted to explicitly create... [Very complex SQL Server logic to return multiple rows in a query even if there is only a single row of data, based on report date---will explain in post]( - I have a very complex scenario I am trying to work out in SQL server to save having to enter... [get the data from last modified]( - Hi, I am trying to get the output as shown. I am looking for latest modified date rows for each "Type"... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Full Recovery DB]( - I have 300 Servers, How can I Check all the Full recovery DB, if Log Backup is done? In other words I... [SQL 2016 AG Read only]( - Looking for any performance tips for the read only node of AG. I realize memory and tempdb important but any... [Linked Servers]( - I'm trying to determine if improvements have been made to utilizing a Linked Server. I know from the past the... [Security Question: CREATE USER ABCDE FOR LOGIN ABCDE when login doesn't exist]( - So let's say I have a database, ABC, where there is a user ABCDE, and I script out the user... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Horrible performance using sys.dm_os_performance_counters]( - Hi, We are in process of buying a third party application (NewRelic) that does a query the SQL 2016 server with... [Improve performance of a query by milliseconds. Should I loop the query to extrapolate the stats ?]( - Hi, I am a query that takes 0.26 seconds to complete in average. The challenge is the process executes the query... [Help needed in a query]( - Hello All, I need help in getting the info like  Name  User ID How many days until account expires Account is not set to never... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Using SUM OVER without an Order by]( - We capture the MPF at a header level and call it TOTAL_MPF. We also capture Line Level MPF and call it... [Insert to Clustered Index]( - Hello, I have a stored procedure that consists of a relatively simple INSERT INTO SELECT, the table being inserted into... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Split sting by hypens]( - Hi Guys, I have one task to derived column values by splitting the string by hypens below is the DDL, CREATE TABLE... [Recursive CTE Duplication]( - Hi all, I am working on a system that has Business units and a Parent\Child relationship. I am trying to write a... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Moving Datafiles]( - I have a number of databases with spaces in the name of the database. No matter what I try to... --------------------------------------------------------------- [Microsoft Access]( : [Microsoft Access]( [Translating SQL Server error messages into plain english]( - I'm looking for a means to capture error messages coming from SQL server e.g. I'd like to replace this message with... 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