The other answers make valid points & I've up-voted them. I'd like to address some of your other concerns.
"I understand that there are some rules .. but .. this just seems too much".
The point of normalization is to remove update anomalies. Ideally if one fact in the real world changes then one column of one row in one table must change. If the schema is not sufficiently normalized a change in the real world may require multiple writes to the DB with the risk of inconsistencies creeping in.
And yes, normalization does have costs. Some of these are design-time (because we have to think through the dependencies), some are code-time (more complicated queries to write) and some are run-time (additional query optimization costs). The cost of not normalizing, however, is wrong data and that's a thing we try hard to avoid.
"wouldn't it be a bit more efficient"
To be more efficient you try to improve the amount if X received for a given amount of Y. What are your X and Y?
A design is generally easier to comprehend if one thing performs one function. I'd argue that having specific, small tables with well-defined semantics, and good names, is easier to learn. So this is efficient for comprehension with respect to time.
When writing queries it is again more comprehensible to reference specific entities rather than pull meaning from abstract representation. Which if the following is simpler to understand?
where dbo.Sex.GenerCode = 'M'
OR
where dbo.GenericCode.SubType = 'Gender'
and dbo.GenericCode.Value = 'M'
The run-time performance of queries depends largely on the optimizer. If we work with it and allow it to reason succinctly about the SQL we generally get good results. Once again having small specific tables with constraints and indexes specific to their semantics and use-cases will, generally speaking, produce a better, i.e. faster running, results. Consider an example where we have three sets of codes - one for Sex, one for US states and one for countries. The first will have 2 rows (in your usage) the second 50 and the third about 140. These are very different cardinalities so likely to produce different query plans. Given the code 'MA' should the optimizer treat that as "Male", "Massachusetts" or "Morocco"? Again the "queries per second" efficiency is likely to be higher with separate tables. I'm not saying that this combined schema will never work, just that it's putting obstacles in place which need not be there.
I can think of one place where removing or combining the tables would be more efficient. That's in the resource used by the system catalog. This is the internal system area where SQL Server holds the definitions of tables i.e. what columns they have, data types, constraints etc. It is distinct from the user data which the table holds. All modern DBMS have something similar. Removing the cruft will make this smaller. The saving, however, will be infinitesimal, negligible, unmeasurable. It's not worth considering.
"the database would store data in one table together"
SQL Server does not work that way. The unit of IO is an 8kb page of data. Each page belongs to exactly one table. If the table has sufficient rows to need more than one page (unlikely for lookup tables) then having them on adjacent disk sectors can speed large queries as the sectors can be read sequentially as they pass under the disk head. If you use SSD or NVMe this not a concern. Most references to lookup tables will be single-row not large range scans.
Some DBMS recognize that table A and table B are often joined so try to store the corresponding parts adjacently on disk. SQL Server does not.
Many data warehouse designs will deliberately denormalize lookup values specifically to put together on-disk the related values and benefit from sequential scans and no joins. This is a legitimate technique for DW but not for OLTP.
I've jumped around a bit. I hope it makes sense. If I'm not clear please leave a comment and I'll try to explain.
Sex
. Well, I know some people will want 5 different personal pronouns for themselves, but leaving this aside since I come from Eastern Europe where we're far from this, this table will always have just two rows for men and women. So I don't really see much sense in having it as a separate table.CHECK
constraint if it's only ever 2 values. However, there could be method in the madness - OK, it's 2 values for now - and that may be an out-there example, but in the long-term, it's a valid way to do things and the most flexible and powerful. All you have to do is add another record to a table - no searching ENUMs or verifying CHECK constraints - it's done once in the database and forever hold your peace!count(*)
method will always give you a low amount, as one row per NC index + 1 row for a heap / clustered index will be returned. Did you mean to use the rowcnt column?rowcnt
column and if I understand, I should use this in the where clause, so it would beselect count(*) from sysobjects t, sysindexes i where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) where i.rowcnt in (0, 1, 2);
I know you mentionedcount(*)
, but how else should I count the output? My assumption is the data is correct before applyingcount(*)
function on it.