June 16, 2022 at 3:59 pm
Hello:
I am creating a new database and suddenly I have a doubt.
In most applications, there is always a series of tables that are loaded with data and never changed again.
They are tables of the type: Countries, States, Cities, etc.
These types of tables are optimized once and maintenance plans will not touch them again.
In addition, these types of tables are not usually large, so the number of pages is not high, and, again, the maintenance plans will not take care of them.
Thus, once the table has been created and its data inserted, its optimization is easy:
ALTER INDEX ALL ON bod.Countries REBUILD
GO
UPDATE STATISTICS dbo.Countries WITH FULLSCAN, COLUMNS
GO
And that's it.
Well, at least that's my idea. There will always be someone who can shed much more light on the subject.
But, what happens if said table has LOBs? For example, a table of Certificates with varbinary(max) fields that are also stored in a different filegroup.
How would be the optimization script that generates its indexes correctly, statistics and that takes up the minimum space?
It would be like this?
ALTER INDEX ALL ON dbo.Certificates REORGANIZE WITH (LOB_COMPACTION = ON)
GO
ALTER INDEX ALL ON dbo.Certificates REBUILD
GO
UPDATE STATISTICS dbo.Certificates WITH FULLSCAN, COLUMNS
GO
Or, in this other way?
ALTER INDEX ALL ON dbo.Certificates REBUILD
GO
ALTER INDEX ALL ON dbo.Certificates REORGANIZE WITH (LOB_COMPACTION = ON)
GO
UPDATE STATISTICS dbo.Certificates WITH FULLSCAN, COLUMNS
GO
In other words, REBUILD or REORGANIZE first?
June 16, 2022 at 5:03 pm
REORGANIZE first.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2022 at 7:59 pm
Since these tables are constant, you can pack them very tightly and not worry about a fill factor. Just declare a primary key and keep things simple.
:
CREATE TABLE Lookup
(lookup_code CHAR(3) NOT NULL PRIMARY KEY,
lookup_value VARCHAR(15) NOT NULL);
INSERT INTO Lookups
VALUES ('USA' , 'United States'),
('CAN', 'Canada'), ..;
Please post DDL and follow ANSI/ISO standards when asking for help.
June 16, 2022 at 9:41 pm
This is about type tables in general, not about your question. I would call type tables a well-known design pattern. I have never seen a database without them. Over the years I developed a generic type table that holds all of the types for the system. This increases complexity slightly which can be offset by good design and some well written stored procedures. Something to consider in any case.
June 16, 2022 at 10:27 pm
Thank you very much, ScottPletcher.
Your answer matches my assumptions.
June 16, 2022 at 10:41 pm
Your answer is interesting, GeorgeCopeland.
In Spain, these types of tables are called master tables.
However, I don't fully understand your idea. Just a generic table with stored procedures? And the efficiency? Does it work properly in an OLTP?
Could you elaborate a little more on your idea?
If two people have an apple and they exchange it, they still have an apple each. If two people each have one idea and exchange them, now both people have two ideas each.
George Bernard Shaw
June 17, 2022 at 4:29 pm
At its simplest, the MasterType table would have an ID, a Description, and a TypeIndex. All of the rows for, say, CustomerType would have TypeIndex of, say 1. Note that you could enhance this design, for example, by adding a SortOrder column. Notice that you could use this same table to document all of the TypeIndexes in the table. Implement stored procs to insert and fetch. This table, even when loaded with all of your types, will be tiny. It would be easy to smoke performance for this implementation, for example, by keeping the table in memory.
June 17, 2022 at 4:57 pm
Be careful! The problems with a generic lookup table, structured like this:
( table_id smallint NOT NULL, data_code varchar(50) NOT NULL, description varchar(1000) NULL )
are:
(1) the data_code (key) must be made varchar as a lowest-common-denominator for common lookup; this complicates comparisons, esp. for decimal values. You could, instead, make the column type sql_variant (which I have done), but that is less performant and raises other issues.
(2) declared referential integrity (DRI) is now more complicated, because you have to include the table_id as part of the DRI.
For example, if I have dedicated CustomerType lookup table, that table will be:
dbo.CustomerTypes ( CustomerType tinyint NOT NULL, Description varchar(100) NOT NULL )
and the DRI will be simply:
dbo.Customers ( ..., CustomerType tinyint NOT NULL FOREIGN KEY REFERENCES dbo.CustomerTypes ( CustomerType ), ... )
But, if using a shared lookup table, CustomerType has a related table_id (or "TypeIndex") of 1 along with the actual CustomerType. Now how do you do DRI? You have to add another column to the main table to include that value and then do something like this:
dbo.Customers ( ..., CustomerTypeTypeIndex smallint NOT NULL DEFAULT 1, CustomerType tinyint NOT NULL, FOREIGN KEY REFERENCES dbo.CustomerTypes ( CustomerTypeTypeIndex, CustomerType ), ... )
The more reference columns you have in the same parent table the more onerous this becomes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2022 at 5:10 pm
Thank you very much for your reply.
Very interesting idea. I have turned my head, I have turned it to the left, to the right, up and down. It's good, and it would save me from having a bunch of small tables, with few records, and no interest at all.
Obviously, Bernard Shaw was very right.
However, my way of thinking leads me towards trying to normalize the database, as long as it doesn't cause a problem with performance. I think, for now, I'll stick with my design. But in future developments I will keep it in mind. Thank you George Copeland.
While I was writing the answer, I got ScottPletcher's answer. And yes, you are absolutely right. Hence my German-type grid reasoning: normalize as long as it is not a problem. Sure enough, thinking about those things is when he turned his head and scratched my neck.
Thank you very much to both. It has been an enriching talk.
June 17, 2022 at 6:41 pm
One True Lookup Table:
In addition to other concerns, to me, I tend to view lookup tables themselves as an entity. If they contain unrelated "things", they are not normalized. And it adds ambiguity.
Don't hesitate to use the tables valid for modeling a domain -- RDBMS engines like SQL Server are very capable of handling large numbers of tables. There can be valid cases for OTLT/EAV tables, but they can be like a "junk drawer".
June 17, 2022 at 6:48 pm
In general I agree, although in a truly normalized design the number of tables can get truly overwhelming (like SAP!).
Certain simple things do not need tables. Instead, you can use CHECKs in the db. For example, a F/M gender code. A C(elsius)/F(arenheit), K(ilograms) vs P(ounds), etc..
I'd urge you to always use a numeric code table, with a FK, for Status. Do not use 'Open', 'In Progess', 'Complete', etc., instead use 1, 5, 9, etc.. Reasons:
(1) Status often changes a lot (and is designed to change a lot), and writing multiple bytes can cause page splits, whereas a tinyint will always take only 1 byte no matter what status you set it to.
(2) It avoids inconsistencies like 'Complete' vs 'Completed' and 'Close' vs 'Closed', etc.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2022 at 12:12 pm
Hi Scott, thanks for the comments. I think that your concerns apply to any table, not just a lookup table. I recommend making keys int. In any case, the table is tiny and performance is extremely good. Have a great day.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply