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

Understanding Your Database

I ran across a neat post from Michael Swart recently. In doing some spring cleaning, he was looking to remove unused, or maybe unnecessary, tables from his database. He published a script that looks through the plan cache to determine what connections exist between tables. He joked that if he doesn’t find any joy when examining a table, he drops it. At least I hope he was joking. If not, I suspect we’ll see a note on LinkedIn soon.

Actually, I’m sure he’s joking, and he makes a good point at the end of the his post. His script isn’t used to make decisions, but rather it provides a place to begin to investigate more about what the table might be used for in an application. It provides a starting point for more questions, such as is there value in removing this table. His company is embracing AWS, and they are becoming more cost conscious. This means keeping less data, and possibly moving cheaper data stores where possible to reduce license and/or hardware costs.

I suspect that other organizations will start to embrace similar attitudes as more move to the pay per month model of the cloud. Many of us rent hardware, and when we do, the recurring costs become an issue. Suddenly we might rethink the amount of data we keep and archive or remove older data, either to reduce costs, or risks. The GDPR brings with it a push to not keep all data in perpetuity.

The goal of better understanding our databases, and specifically tables, makes a lot of sense to me. Far too often I’ve inherited some system and only understood portions of the database. I’ve performed hours of investigation to try and better comprehend how data is stored, retrieved, and manipulated. I’ve found my share of unused tables, often renaming them for months and eventually deleting them.

I do think that we poorly document databases, almost treating them like a file share where we drop a new item when we need it, without thinking through the usage, ensuring others know about the entity, and often forgetting it exists if we don’t regularly use it. A RDBMS isn’t a file share, or at least it’s not an inexpensive one, so we ought to be cognizant of the data we keep and trim unnecessary waste over time.

I empathize with Michael and would relish the challenge to review and trim old tables where I could. However, I also know that often there are tables that won’t “make a difference”, either in cost or any other savings and aren’t necessarily worth the time to investigate and remove. Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time. Unfortunately, I’ve run into plenty of those, which nag me, but really shouldn’t be something I spend time on, and I have to leave them be.

Steve Jones - SSC Editor

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

 
Redgate Webinars
  Featured Contents
Stairway to Advanced T-SQL

Stairway to Advanced T-SQL Level 5: Turning Data On Its Side Using PIVOT Operator

Greg Larsen from SQLServerCentral.com

The PIVOT operator was added to the Transact-SQL language in SQL Server 2005. It allows you to turn your row data on its side, so it can be presented as column data. This is useful when you want take unique column values and have them displayed as column headings, where the column headings are associated with summarized values displayed below each column heading. In this article I will be exploring how to use the PIVOT operator.

How to create and refresh development and test databases automatically, using SQL Clone and SQL Toolbelt

Additional Articles from Redgate

Phil Factor shows how a set of Redgate tools can be used together, via PowerShell, to build a database from object-level source, stock it with data, document it, and then provision any number of test and development servers. Before tearing down and rebuilding a database to a new version, we take care to save any DDL changes made to the existing copy.

SQL and NoSQL Database Features and Differences

Additional Articles from MSSQLTips.com

In this tip we will talk about the features and main differences between SQL and NoSQL databases.

Free eBook: SQL Server Internals: In-Memory OLTP

Press Release from Redgate

In this free eBook, Kalen Delaney explains how Microsoft’s 2016 In-memory OLTP engine works. In her book, learn how to use lock- and latch-free data structures to allow non-blocking data processing, and find out how to migrate existing tables to Hekaton.

From the SQL Server Central Blogs - Power BI Now Has Keyboard Accessible Visual Interactions

Meagan Longoria from SQLServerCentral Blogs

The March 2019 release of Power BI Desktop has brought us keyboard accessible visual interactions. One of Power BI’s natural…

From the SQL Server Central Blogs - Uppercase All String Columns (and in a Single Query!)

Solomon Rutzky from SQLServerCentral Blogs

(last updated: 2019-03-21 @ 15:00 EST / 2019-03-21 @ 19:00 UTC )

Intro
The other day, Steve Jones published a short post with a quick…

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

A new area code

I have this string in R:
> phone <- "(555) 867-5309"
I want to replace the area code with 800. In the US, the first three digits are the area code and the last seven are the main phone number. Which of these does this in R?

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)

Creating My Table

I create a new user in my database that only has the dbo schema with this code:

CREATE USER JoeDev FOR LOGIN JoeDev
GO
GRANT CREATE TABLE TO JoeDev
GO

The user logs in and executes this:

CREATE TABLE JoeDev.Articles (someid INT)

What happens?

Answer: The statement produces an error

Explanation: While the user has the CREATE TABLE permission, they do not have the CREATE SCHEMA permission, which is required to create an object in a new schema.

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.


Best Practices for Stored Procedure Design - Hi, I’m developing a stored procedure that expects a parameter of N string values, each separated by a comma. Depending on the string, I’m planning to run a unique query. The structure is the same for all of the unique queries. Ultimately, I am planning to insert each result set into a temp table. I […]
Nolock on SQL 2005 - Does anyone know for certain if SQL 2005 supports the nolock hint on a plan guide?      Gettting a complaint about hint syntax that it hates in 2005 but loves in 2008+  
How to compare three tables using a single column (inner Join is not working) - Hi All, I have a table named AccountProfile which has a column named AccountNumber along with other columns. I have another two tables named AbsPlan and RitPlan. These two tables has the same column named AccountNumber along with other columns like AccountEffectivedate , AccountStatus (Open/Closed) etc. AccountNumber is unique in AccountProfile table whereas it has […]
Split Row Into Multiple Based on Column Values - I’m working on a data migration project, moving from one ERP system to another.  The way a Sales Ledger account works is different between the two systems and I need to split out accounts from the legacy system into multiple in the new system.  The legacy system has a marker in multiple columns depending on […]
Multiple Inserts in a column without overwriting - Hi, I have a requirement to insert values in the Comments column based on meeting certain conditions. If there are multiple conditions then multiple inserts need to be made in the Comments column The issue faced is if there are multiple comments to be added, then only the recent update works. I need to make […]
Need to store more then two ids in single column - Hi Sir, I have a requirement to store if one main id have more then two child ids in single column like If main id is 1234 and it has two child ids like 2222 and 3333 then output should be in single row instead of two or more rows. Current result is, Main ID […]
Datadog - Hi, Anybody using this to pull in any metrics/results etc? I’d like to start running some SQL to look at the last time something was put into a table and alert if over a certain threshold etc?
Is SQL SERVER the best DBMS? - My take: For the windows and *nix worlds, yes Historically Oracle used to be the better solution, but over the course of my career this its how it panned out: You unpack and install Oracle. You unpack and install MS-SQL. Same hardware, same OS. The DBA and sysadmin would beat on the Oracle install for days, […]
Reverse engineering data model in Azure MSSQL - Hi, I have a database that has been created in Azure MSSQL Server. It has a lot of tables with Primary/Foreign Keys. I’d like to create an ER diagram automatically. Are there tools available that can do that? Please advise. Thank you!
Suggestion for newsletter format - I loved the old newsletter format:  To the left there was a table of contents that you could glance at to find topics of interest.  As a very busy developer, this helped me zoom in on what I was interested in.  The old format made very efficient use of space:  It was single-spaced and took […]
A new topic of code formatting - Copying text from a post Dear Experts, i need help in calculating time difference between two dates portion by Date, the difference will be calculated with the first day last Attendance = 1 with very next Attendance =1 of next day. and same will repeat second day last Attendance = 1 with very next Attendance […]
Prepopulate users Favourites in Reporting Services Web Portal - We’ve created a hierarchy of folders in the reporting services web portal for SQL Server 2016 to control security around reports at folder and subfolder level but some end users prefer to see all their reports in one long list! I’ve pointed out they can add their favourite reports to the Favourites page but is […]
Script which reports a user has access to in reporting services - Does anyone have a script I can run to return a list of all reports a user has access to please?
Forums Top Menu Options - Can we have a description on what the options for the forums display are? Most Popular, Most Replies, and Most Active seem almost the same thing. Are Latest Topics defined by the date/time the OP was posted or the last reply? If they’re defined by the OP, can we have an option for Latest Replies […]
Creating users with limited access - Hi all We’ve got a SQL-only account with too many permissions (and everybody knows the password!) that’s used for everything and I’m trying to have a tidy-up. It’s linked to an AD account (unfortunately with the same password that’s also got too many permissions on the server). What I want to do :- Create an […]
 

 

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

 

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