January 8, 2016 at 6:48 am
Hi Guys,
What tool should I use to create tables as part of a db design(from logical/conceptual model). I'm new to development/design.
DB model has been created on Visio 2010.
Please share your thoughts.
Many thanks!
January 8, 2016 at 7:35 am
SQL!$@w$0ME (1/8/2016)
Hi Guys,What tool should I use to create tables as part of a db design(from logical/conceptual model). I'm new to development/design.
DB model has been created on Visio 2010.
Please share your thoughts.
Many thanks!
I'm not sure, but I believe that Visio can be used for that.
A quick Google search showed me this: https://support.microsoft.com/en-us/kb/319984, I'm not sure if it applies to you.
If you don't have a lot of tables, you could do it manually which will allow you to review each step before creating the objects. SSMS has a designer tool, but it'll generate changes as soon as you save them.
January 8, 2016 at 7:40 am
You can create the tables directly from Visio. It's been awhile, and I don't have a copy of Visio to walk you through how to do it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 8, 2016 at 7:41 am
The best tool for creating tables from a logical model is an experiened database developer who knows what a clustered index and foreign key are and how to use them. Personally, I hand code all my DDL, and it's better than anything a layman would create using a GUI based logical/physical modeling tools like ERWin.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 8, 2016 at 7:58 am
Visio works, but IIRC in the more recent versions it can no longer do reverse-engineering, which TOTALLY sucks!!
My favorite tool for database (and all other DEV/IT Pro modeling) BY FAR is ERStudio by Embarcadero. They have "cheaper" licensing if you just get a SQL Server license too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 8, 2016 at 11:50 am
Amen to that! the reverse engineering of ERDs etc used to be one of my favorite parts of Visio... why MS decided it was no longer useful is a mystery!
January 8, 2016 at 12:26 pm
I don't have anybody paying for my tools and I can't justify something like ER Studio so I may soon be investing in Devarts dbForge Studio for SQL Server.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 12:35 pm
Eric M Russell (1/8/2016)
The best tool for creating tables from a logical model is an experiened database developer who knows what a clustered index and foreign key are and how to use them. Personally, I hand code all my DDL, and it's better than anything a layman would create using a GUI based logical/physical modeling tools like ERWin.
Agreed 100%.
January 8, 2016 at 12:57 pm
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.
Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2016 at 1:03 pm
Jeff Moden (1/8/2016)
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
+1
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 1:07 pm
Jeff Moden (1/8/2016)
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
Just remembered how I suggested to one client last year that if they changed the data type for one column from some ridiculous Float to the proper Varchar, they could save something like 9 GB just for that one column in that one table.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 1:37 pm
Alvin Ramard (1/8/2016)
Jeff Moden (1/8/2016)
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
Just remembered how I suggested to one client last year that if they changed the data type for one column from some ridiculous Float to the proper Varchar, they could save something like 9 GB just for that one column in that one table.
I once had a guy explain to me why all integer columns (there were about 50 of them) on a 200 GB table were setup as 8 byte BigInts and all date/time columns were VarChar(30). Why? As he explained it, the new 64bit processors and servers (64bit was the new thing at the time) could compute 64bit intergers more efficiently than 32bit or 8bit integers. Also, by storing date/time values in VarChar columns, he could select by period using an expression such as: WHERE INSERT_DATE LIKE '2015/02%'. Well, not only did the performance suck, but ETL load was inserting YYYY-MM-DD HH:MM:SSSS format using UTC time, while the application was inserting in MM/DD/YYYY HH:MM:SS format using local EST time, and if a query using date/time conversion overlapped, it would throw an error.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 8, 2016 at 2:00 pm
I'm sure we can all agree that no tool can prevent bad designs.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 6:40 pm
Alvin Ramard (1/8/2016)
Jeff Moden (1/8/2016)
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
Just remembered how I suggested to one client last year that if they changed the data type for one column from some ridiculous Float to the proper Varchar, they could save something like 9 GB just for that one column in that one table.
It's amazing to me that more people don't even know about such things never mind consider them. I went through something similar with a lead "developer" that a previous boss hired and he justified all the NVARCHAR(256) and NUMERIC(18,0) stuff by reciting Knuth's famous and frequently misused quote about pre-optimization being the root of all evil.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2016 at 7:57 am
Jeff Moden (1/8/2016)
Alvin Ramard (1/8/2016)
Jeff Moden (1/8/2016)
I especially hate it when a Developer that doesn't know, creates tables using Visual Studio or has VS auto-magically create the tables from objects because almost everything comes out as NVARCHAR(256) or NUMERIC(18,0) for INTs. It's a huge waste especially when it picks NVARCHAR(anything) for something like a zip code or phone number (all digits) or for something like an IsActive column, which only needs a 1 or a 0 and certainly doesn't need NUMERIC(18,0) for such a small thing.Agree 100% with the idea that someone experienced in the art can single handedly do a much better job than any inexperienced person using a tool.
Just remembered how I suggested to one client last year that if they changed the data type for one column from some ridiculous Float to the proper Varchar, they could save something like 9 GB just for that one column in that one table.
It's amazing to me that more people don't even know about such things never mind consider them. I went through something similar with a lead "developer" that a previous boss hired and he justified all the NVARCHAR(256) and NUMERIC(18,0) stuff by reciting Knuth's famous and frequently misused quote about pre-optimization being the root of all evil.
<humor>Well, I kind of see his point. Too much pre-optimization is evil. If all databases were modeled correctly in the first iteration, just think how many of us would be out of a job.</humor> 😛
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply