 | A community of more than 1,600,000 database professionals and growing |
| | | Learning Data Modeling Last week I wrote about database design tests in interviews. Many of you noted that you hadn't been asked to perform any design in tests, and plenty of you noticed that ER diagrams are a rarity. Those experiences match mine and are a little disappointing for our industry. Even for software developers, many of whom don't build databases, I'd expect that ER diagrams to be helpful and plentiful. Our clients are often developers and report writing businesspeople. Why don't they demand better documentation? If they were to ask for us to produce ER diagrams and documentation, I'd hope most of you could produce one, even using SSMS or some other tool. However, can you design the storage for your business requirements in a way that you would feel comfortable presenting to your peers? Do you think you can build a database that efficiently stores data, performs well, and is easy for application developers to work with? If so, I have a question. What resources would you recommend for others to learn about data modeling? We have a dearth of database design articles on SQLServerCentral, though we do have a Stairway Series on Database Design. I don't know how easy it is for most developers, or even database architects, to read and understand. I realize that modeling and designing databases can be complex topics, and it does take some effort to learn to do it well, but I also believe that we need practical, easy to approach articles that lead people through the concepts. I'd love to see more articles from real world people that discuss designing sections of databases. How do you handle scheduling systems? Inventory tracking? Customer details? If anyone wants to write articles that might lead a reader along (such as Paul White or Brandie Tarvin did), we'd love to have them s ubmitted at SQLServerCentral. Join the debate, and respond to today's editorial on the forums |
| The Voice of the DBA Podcast Listen to the MP3 Audio ( 3.2MB) 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 |  | | Could your SQL coding be more efficient? Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. Check out the tips |
|  | | 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 |
|
|
|
| | |  | Mike Fal from SQLServerCentral.com Start learning Powershell (PoSh) for SQL Server and improve your ability to write utilities that will make your job easier. More » |
 | Additional Articles from SQLPerformance.com Jonathan Kehayias discusses SQL Server on VMware, and how to monitor CPU performance – with or without access to vCenter. More » |
 | Tim Mitchell from SQLServerCentral Blogs Having the right tools for the job makes the work much more efficient. However, for those just starting out in... More » |
| | Kenneth Fisher from SQLServerCentral Blogs I did a SQL crossword last month (not my first one either) and it was pretty popular so I asked... More » |
|
|
| | | Today's Question (by Darko Martinovic): If you like to add comments to the database objects created in your databases, you will us what? |
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: Designing database. 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 | | Pro Power BI Desktop This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. |  |
|
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Stewart Campbell): You are tasked with identifying the maximum length of data contained in some of the larger tables in your database. To achieve this, you write the following script: DECLARE @TableName SYSNAME = 'TheTable', @Col SYSNAME, @SQL NVARCHAR(1000) = '', @Max INT; DROP TABLE IF EXISTS #Cols; CREATE TABLE #Cols (ColumnName SYSNAME, MaxLen INT); INSERT #Cols (ColumnName) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_Name = @TableName AND DATA_TYPE LIKE '%varchar'; DECLARE MaxCol CURSOR LOCAL FAST_FORWARD FOR SELECT ColumnName FROM #Cols; OPEN MaxCol; FETCH NEXT FROM MaxCol INTO @Col; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = N'SELECT @Max = MAX(DATALENGTH(' + QUOTENAME(@Col) + ')) FROM ' + QUOTENAME(@TableName); EXEC sp_executesql @SQL, N'@Max INT OUTPUT', @Max OUTPUT; UPDATE #Cols SET MaxLen = @Max WHERE ColumnName = @Col; FETCH NEXT FROM MaxCol INTO @Col; END CLOSE MaxCol; DEALLOCATE MaxCol; SELECT * FROM #Cols; What, in your opinion, will be the result of the script execution? Assumptions: - Running SQL Server 2016 or greater
- TheTable exists, has nvarchar and/or varchar columns, and is populated with copious amounts of data.
Answer: Successful execution, MaxLen column populated with values Explanation: sp_executesql is a very handy procedure, that makes management of dynamic SQL much easier. It makes it possible to, for example, populate variables from the results of dynamic SQL execution, for use firther down in the procedure. However, when dealing with dynamic SQL, care must always be taken to prevent SQL injection (i.e. all input sanitised) - actually, it would be better to avoid it altogether, if at all possible. Ref: sp_executesql: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql
» 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 suddenly crashes - Hello Everyone, I have a Windows Server 2016 server which is running SQL Server 2016. My application interacts with the server and...
Cursor Performance - OK so we have an application that has gone through an upgrade which also saw the database move from a... SQL Streaks - Hi I wish to calculate several flags for the streaks in SQL I have added the tables and the desired table I...
Procedure plan changed suddenly - Hello, Is there anyway to find how suddenly a procedure plan is changed. I understand that there can be multiple reasons... Splitting TempDB - Hi, I noticed yesterday that I have the following setup for one of my production virtual database servers: 1 socket, 8...
Reading from a view intoa table is taking forever! - I have the following setup 2 linked servers - on one server (sql2014) is rather complicated view - 5400 rows the view is stacked... General advice needed - database design - Is this a bad way to design a pricing table? - Suppose we are selling bottles of water that can be bought in sizes of 1 to 10 litres (1, 2,... Multiple Values for Single Parameter in User Defined Function - Did anyone come across this scenario where a developer/analyst comes in and pass multiple values for Parameter so he/she can...
SQL Server 2012 SP3 - Hi guys, We have 3 instances bidirectionally replicated (transactional). One of them crashed. Sql dump files were created every 2 seconds saying... How to make relation between tables to calculate cost of flight and transfer and hotel and excursion per every day ? - I need to design database for Tourism Company organize more tours based on the following tables Hotels HotelID Hotel Name 11 &
how to optimise a view having 3 to 4 derived tabels. - hi, ( i need suggestion on coding practice) i have a situation , i have one view , whoes structure is like select x.* from... Please help on reformatting this table into another format using TSQL - Hi guys, I will really need you guys help on reformatting a table into another format for export in CSV file. I... Cross Apply Timing Out on Large (1bn rows) Table - I have a small table Members (1000 rows) that contains member information. I have a large view History (1bn rows) that...
Temporary Functions? - I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again....
SQL behaving strangely on different servers - Hi All, I have a sql statement working on static tables (no Insert,Update,Delete) on our testing server it's running very fast...
Create Multiple SSRS Subscriptions for a Parameterised SSRS Report - Programmatically / Dynamically / Quickly - Hi We have 470 subscriptions in total, of which 224 are for the same parameterised ssrs report but pass different parameter... variably hide/show tablix objects based on conditional expression - I have a parameter MonthlySet having INT datatype with possible values 1,2, or 3 and I have a report with...
Dataset - Aggregate data contained in different groups. - Hi, I've create a data source which returns the data I need. within the data source I have 3 groups - which are...
Expression - Hi, How I can execute a store procedure using expression in SSIS 2005? Thanks Munna
Order by Clause in Union - My ORDER BY clause after joining data using UNION is not giving me correct result. Anyone has any idea why? SELECT... |
|
| This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected].
| 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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: [email protected] |
|
|