Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-05-22 : 23:55:42
|
If you were supposed to import from a text file into a temp table, would you design the temp table as below OR a table with all columns fixed CHAR columns? Which one the import process would be faster? [tblTEMP] ID INT Col1 VarChar(12) Col2 VarChar(50)Col3 FloatCol4 IntCol5 VarChar(20)Col6 Float Canada DBA |
|
|
raclede
Posting Yak Master
180 Posts |
Posted - 2005-05-23 : 00:48:54
|
| well.. specifying data type for each columns would be more optimized rather than just making all columns CHAR, unless value for each columns varies unexpectedly.. that is why data types are available.. like in your example above it is more appropriate to define the datatypes rather than just making it char.. Also when defining datatypes for each column you must handle all possible error you might encountered.Cursors are for those who doesn't know how to use SQL K.I.S.S. - Keep it simple stupidraclede™ |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-23 : 01:06:43
|
| Makes sense. I doubted may be getting all columns as CHAR then the import process would be faster.Canada DBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-23 : 01:10:13
|
 This is an odd thought. Declaring as VARCHAR is always better unless the fields are actually always a set size. If you were at the edge of the database files needing to grow though, and having varchar instead of char kept the files from needing to grow, then your import could be significantly faster with varchar fields.Overall though, just stick with best practices. If it's a variable length field, declare it as such.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-23 : 01:48:36
|
| How about the Float data type in my example? Defining float is better or I define it as VarChar?Canada DBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-23 : 02:17:49
|
| You should never define a numeric as a varchar if you can avoid it. I would seriously questions whether you shouldn't be using a decimal, int, etc though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-05-23 : 08:34:55
|
| Interesting! I thought having VarChar for numeric fields in the temp table will modify the import's speed than having Float fields.Canada DBA |
|
|
|
|
|
|
Table design for faster import from a text file