| A community of more than 1,600,000 database professionals and growing |
| | | Understanding a Database I ran across a post that asked a good question, one which I want to ask you today: how do you learn about a database? I've run into quite a few databases in my career. Some were third party systems, like Dynamics and JD Edwards World. Some were databases that custom designed and built by developers and database modelers of widely varying skills. Some were well built in order to normalize data and define referential integrity, and other databases were put together in a piecemeal fashion over time, lacking keys and consistent naming. I'll leave it to you to guess if there were more of the former or the latter. When a developer or DBA comes across a database, what's the way that they can decode what fields and columns mean? Certainly names help at times, especially when the purpose of the database is understood, but all too often the names don't quite make sense. This is especially true in many vendor databases. The one common theme I've seen in many databases is that there is no data dictionary provided by anyone. Trying to understand a database has been a trial and error detective task for me in the past. Usually this starts when I need to do some work that is requested by users: write a report, change data, etc. In these cases, I often will ask users to access certain data related to the change from their application while I run Extended Events and note which entities are accessed. I can then start looking for data elements, and note which columns might be mapped to which fields in an application. Often I've built a data dictionary of sorts outside of the database using something like ErWin, ER/Studio, or another tool. That has been somewhat flawed, as it's hard to share the information with others. These days I think I'd make extensive use of Extended Properties to document what I learned, so that all my knowledge is available for anyone else that needed to work on the system. They can just look at the properties for various entities. If you've got other methods, share them with us today. I'm sure there are plenty of DBAs and developers out there that would like some tips and tricks for decoding a database design. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
| | The Voice of the DBA Podcast Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn. 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 | | | | | NEW 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 |
|
|
|
| | | | | J. Drew Allen from SQLServerCentral.com One common request often seen in T-SQL is working with queues of information. In this piece, J. Drew Allen will show to use windowing functions and the OVER clause to process a FIFO queue. More » |
| | Grant Fritchey shows how to use Data Masker to obfuscate address data, wile ensuring that the masked data retains the characteristics and distribution of real address information. More » |
| | Additional Articles from Brent Ozar Unlimited Blog Books Online says the FAST_FORWARD option has "performance optimizations enabled," but they didn't say if they were good. It turns out they're not. More » |
| | Another post for me that is simple and hopefully serves as an example for people trying to get blogging as... More » |
| | Brian Kelley from SQLServerCentral Blogs Have you ever been in a meeting where the attendees seemed to go down one rabbit trail after another? At... More » |
|
|
| | | Today's Question What does this code return? DECLARE @m MONEY; SELECT @m = \; SELECT @m |
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. 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): What's the shortest variable name?
Answer: declare @ Explanation: Try this: DECLARE @ INT = 1000000; SELECT @ AS 'One Million' Not sure I'd do this, but it works. » Discuss this question and answer on the forums |
|
|
| | | | Jonathan Roberts from SQLServerCentral.com This is an improvement to my last script: http://www.sqlservercentral.com/scripts/Tally+Table/155213/ Instead of deleteting rows from a table variable it uses a NOT EXISTS to filter out rows that are not prime, it also has a better filter to eliminate some obvious non-primes when populating the table variable @N. Initially I tried writing the process as a script using temporary tables instead of table variables; when the script is written like this it runs approximately twice as fast (a demonstration of why temporary tables are better than table variables). But a table vaued function cannot have temporary tables in its code. Of course, the fastest way to get lists of prime numbers, in SQL Server, is to create a permanant table of primes on your database which can be achieved this using this table valued funtion: IF OBJECT_ID('dbo.Primes','U') IS NOT NULL DROP TABLE dbo.Primes GO CREATE TABLE dbo.Primes(N int PRIMARY KEY CLUSTERED); GO INSERT INTO dbo.Primes(N) SELECT Prime FROM dbo.FastPrimes(100000000,2) -- Wait for 10 minutes to get all 5,761,455 prime numbers less than 100 million into a permanant table table, using 1.3 GB, that you can then use with great speed at your leisure. SELECT * FROM dbo.Primes More »
|
|
|
| | | | Venugopal Saride from SQLServerCentral.com Step 1: Create the view Step 2: Join with sys.sysdatabases and sys.dm_exec_connections check the connection details 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. Entity-Attribute-Value is evil ? how to avoid... - Hi all consider the following scenario:I have a FILE table with known attributes (size, creation date, ...)The user needs to add... Relation table - Hi I have a table that may reference multiple objects (by integer ID) each stored in a different table there are... Parallel Query Plan Choices On (near) identical Environments - Hi All We have one particular query on a particular view that in our production environment insists on selecting a plan... add to variable in case statement - I want to add to a variable in a case statement: SELECT name, city, state, rank = CASE WHEN len(city>10) THEN rank... Using SSIS to Get Data from MSP to On Prem - We have on-prem SQL Servers as source data that is being moved to a SQL instance in a DMZ via... Stored Proc fails as Exec but the SQL that makes it up runs fine - Hi All, I have a stored proc that when I run the SQL hard coding the parameter values it runs fine... Job Failing : Process Exit Code 1. The step failed. - Hi All, Im executing SSIS package by using Batchfile and scheduled batch using SQL job agent. Now I got error " Process Exit... Need index suggestion - Hi All, Need index suggestion for the below query which can improve the performance. (Assume that table has enough data) SELECT... sql performance question - Hi All, Recently, I have undergone a computer based exam where in they asked below question.Need some inputs from experts. Below correlated... poor performing report query against table without a PK - The table is practically a heap because its' clustered index is on a column that allows NULLs, and there are... Case when inside of partition by? - Hi, I have the following situation: CREATE TABLE #TAB1( ID INT ,MY_STATE VARCHAR(10) ,EVENT_TS DATETIME ) INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS) VALUES (1, 'STATE_1', GETDATE()) ... Issues with filling in the blanks from a calendar table. - I have an employee activity table that is difficult to determine how much time is actualy spent on an activity... Slow SQL Script - Hi there, I want to select some information from a few tables and it is very slow that causes timeout. Kindly refer... OS + SQL Server binaries on same drive? - Hello everyone, My question deals with best practices. When installing SQL Server 2008, should the OS and SQL Server binaries be... How do I know what my Report Server is called (i.e. when prompted in Report Builder) - This is a dumb question from a newbie .. sorry .. How do I know what my Report Server is called? (i.e.... The parameterized query expects the parameter '@Id', which was not supplied. - Hi,I am writing a software I use method (parameters.add) but when I am adding data to a database I get... SSIS Design Patterns and examples - Dear Forum, I was looking for a source / reference for SSIS Design Patterns and examples. e.g. some common designs for clone / re-use... Linked server for Oracle - Hi, I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and... Web Service CLR resulting in Timeouts - Hi all - not sure if this is the proper forum for this post, since it kinda spans several different areas,... Error 50000 - saw several jobs failed on the SQL Server, but from the job history, I couldn't see any reason for the... |
|
| This email has been sent to newsletter@newslettercollector.com. 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 |
|
|
