jwcolby
jwcolby at colbyconsulting.com
Fri Nov 23 14:27:28 CST 2007
I do love to hear you rant Arthur! ;-) John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Friday, November 23, 2007 3:12 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server 2005 - Set default data types To my knowledge, there are two ways, one costing money and the other not. I have ranted about the first approach before, but I'll rant again. Tools such as ERwin, PowerDesigner, DeZign and so on incorporate the concept of Domains. Think of a domain as a column spec that is not tied to a table. Once you've defined a few of these, then when defining tables you can use said Domains as if they were a native data type. This is fantastic. You can, for example, redefine a domain to be char(50) from varchar(100) and then regenerate the db with all occurrences of said domain automatically converting. The no-fee way to do this is to modify the modeldb database that is installed and from which all subsequent dbs are modelled. Thus its name. You can create a table herein that contains lots of various column definitions, right down to all their various specs. That table will get created in every subsequent db you create. From there, you can go into Modify (said table), select a row of interest, copy and paste it into your table of real interest. This is a clumsy but free way to achieve what you want. As for me, I prefer to pay the money and get the right design tools. My personal favourite is PowerDesigner, but in the past several jobs I've had, ERwin was the order of the day, so the fact is that I have become more adept at ERwin than at PowerDesigner. I will say this: I wouldn't even dream of designing a db comprising more than 20 tables without such a tool in hand, and the more tables the more I adhere to this principle. I've done some rather large designs involving 500 or so tables, and anyone who would try this without such a tool in hand is IMO in need of a new profession. Scaling this notion down to strictly Access MDB back-ends, I hold to the principle that a false table should be created that contains all the foreign-key specs and simple column specs for all the tables. For example, the false table could contain a column called CustomerID, that is a combo-box with two columns, the first width being zero, the caption reading "Customer" and so on. Spend a lot of time on this false table. Define everything that you can. Thereafter, for every new table you create you can open the false table in design mode, select the relevant columns for any new table, copy and paste. Do it once, then inherit. Admittedly, this no-charge approach is a tad clumsy, and revisions to the false domains don't automatically cascade, which is the reason why I so ardently believe in tools such as PowerDesigner, ERwin and DeZign. They make the db revisions effortless. Imagine 30 occurrences of a domain called Name (in Products, in Customers, in Vendors, and so on). Change the domain definition to varchar(50) and regen the db and every occurrence is updated. That is power. Change a phone-number spec in every table from char(10) to varchar(20) in one swoop. Yes! This is not to slight the "change modeldb" approach. I have written about this (c.f. TechRepublic.com) and it's especially effective if you're a freelancer who does relatively similar apps over and over. (I.e. your apps all have tables called Customers, Orders, OrderDetails and Products). Thoroughly define them once, place them in modeldb, and then every subsequent db you create will inherit these definitions. Of course you can delete the irrelevant ones and modify the saved ones to suit the immediate problem. The Domains facility just takes this concept one level deeper. You can define columns for re-use, rather than just tables. Arthur On 11/23/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > > Is there a way to set the defaults for data types in SQL Server 2005 > when creating tables. IOW, if I set a column to char() can I make it > Char(1) instead of Char(10)? If so how? > > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > Arthur Fuller > Sent: Friday, November 23, 2007 12:08 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] SQL Server 2005 - View to table > > No you wouldn't! SELECT INTO creates the table. > > On 11/23/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > > > > Indeed why not? The only real reason is that newTable does not > > exist yet and I would have to create it by hand. > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com