从单个查找表创建视图的最佳实践是什么？

Question

我有一个相当大的表，主要由查找id的引用一个查找表组成。我知道这通常不被认为是最佳实践，但不幸的是，这个方面超出了我的控制范围。

我希望做的是创建一个视图，将查找id转换为相应的描述(这个特定表中的25-30个查找列)。根据我所读到的，我的选择似乎如下。我的问题是，这些方法(或其他我没有考虑过的方法)中哪一种会提供性能、可读性和可扩展性的最佳组合。

1. 只需使用单独的表别名为每个列创建一个左联接。
2. 使用子查询返回所需查找的内联表值函数
3. 使用新的2019 SQL Server对Scalar UDF进行优化的内联标量值函数。

1似乎是最直截了当的，但维护和读取起来会很麻烦，并且无法将其转换到我需要此功能的其他表。

2似乎更可扩展，但不是太多，我不确定与#1相比，性能会受到多大的影响。

3是我的首选，因为它非常可扩展，并使视图更加可读性。不过，我知道这是MSSQL的一个新添加，因此我加倍关注潜在的性能问题vs1。

是否有人对这一特定方法有任何意见或建议？

编辑:这里有一个例子，突出了使用直联接的潜在负担。这个特殊的观点要求我把简短的代码和描述结合在一起。我认为内联的UDF会使它更加可读性和灵活性，但是在我完成所有转换它的工作之前，我只想了解一下它是否是一个实际的选择。

SELECT dbo.Clients.client_id, dbo.Clients.ssn, dbo.ClientEpisode.admission_date, dbo.Clients.rin, ISNULL(admission_type.lookup_code, admit_type.lookup_code) 
                     AS admission_type, dbo.ClientEpisode.initial_contact_date, dbo.ClientEpisode.assessment_date, dbo.Clients.first_name, dbo.Clients.last_name, 
                     dbo.Clients.middle_initial, dbo.Clients.dob, dbo.Clients.address_1 + ' ' + ISNULL(dbo.Clients.address_2, '') AS address, dbo.Clients.city, dbo.Clients.state, 
                     dbo.Clients.zip_code, geocode.lookup_code AS geocode, race.lookup_code + ' - ' + race.lookup_desc AS race, 
                     hispanic_origin.lookup_code + ' - ' + hispanic_origin.lookup_desc AS hispanic_origin, dbo.Clients.gender, dbo.Clients.veteran_status, 
                     marital_status.lookup_code + ' - ' + marital_status.lookup_desc AS marital_status, dbo.Clients.family_size, dbo.Clients.num_receiving_child_support, 
                     dbo.Clients.num_of_children, dbo.Clients.num_removed_from_custody, pregnant.lookup_code + ' - ' + pregnant.lookup_desc AS pregnant, 
                     livingarrange.lookup_code + ' - ' + livingarrange.lookup_desc AS livingarrange, paysource.lookup_code + ' - ' + paysource.lookup_desc AS paysource, 
                     medicaidmco.lookup_code + ' - ' + medicaidmco.lookup_desc AS medicaidmco, empstatus.lookup_code + ' - ' + empstatus.lookup_desc AS empstatus, 
                     unemployment.lookup_code + ' - ' + unemployment.lookup_desc AS unemployment, schooljob.lookup_code + ' - ' + schooljob.lookup_desc AS schooljob, 
                     dbo.Clients.highest_grade_completed AS education, dbo.Clients.annual_income, income_source.lookup_code + ' - ' + income_source.lookup_desc AS income_source,
                      hearing_status.lookup_code + ' - ' + hearing_status.lookup_desc AS hearing_status, 
                     family_income_override.lookup_code + ' - ' + family_income_override.lookup_desc AS family_income_override, 
                     preferred_language.lookup_code + ' - ' + preferred_language.lookup_desc AS preferred_language, dbo.Clients.english_speaking, 
                     priorepisodes.lookup_code + ' - ' + priorepisodes.lookup_desc AS prior_treatment_episodes, 
                     referral_source.lookup_code + ' - ' + referral_source.lookup_desc AS referral_source, referral_office.lookup_code + ' - ' + referral_office.lookup_desc AS referral_office,
                      dbo.ClientEpisode.dcfs_involvement, dbo.ClientEpisode.misa_involvement, dbo.ClientAdmissionData.numarrests, dbo.ClientAdmissionData.numgroups, 
                     attendgroups.lookup_code AS attendgroups, dbo.Clients.support_in_recovery, problemarea.lookup_code + ' - ' + problemarea.lookup_desc AS problemarea, 
                     primdrug.lookup_code + ' - ' + primdrug.lookup_desc AS primdrug, primdrugfreq.lookup_code + ' - ' + primdrugfreq.lookup_desc AS primdrugfreq, 
                     primdrugrout.lookup_code + ' - ' + primdrugrout.lookup_desc AS primdrugrout, primdrugsevr.lookup_code + ' - ' + primdrugsevr.lookup_desc AS primdrugsevr, 
                     dbo.ClientAdmissionData.primdrugage, secdrug.lookup_code + ' - ' + secdrug.lookup_desc AS secdrug, 
                     secdrugfreq.lookup_code + ' - ' + secdrugfreq.lookup_desc AS secdrugfreq, secdrugrout.lookup_code + ' - ' + secdrugrout.lookup_desc AS secdrugrout, 
                     secdrugsevr.lookup_code + ' - ' + secdrugsevr.lookup_desc AS secdrugsevr, dbo.ClientAdmissionData.secdrugage, 
                     tertdrug.lookup_code + ' - ' + tertdrug.lookup_desc AS tertdrug, tertdrugfreq.lookup_code + ' - ' + tertdrugfreq.lookup_desc AS tertdrugfreq, 
                     tertdrugrout.lookup_code + ' - ' + tertdrugrout.lookup_desc AS tertdrugrout, tertdrugsevr.lookup_code + ' - ' + tertdrugsevr.lookup_desc AS tertdrugsevr, 
                     dbo.ClientAdmissionData.tertdrugage, dbo.ClientAdmissionData.currentlevel, dbo.ClientAdmissionData.leveldate, dbo.ClientAdmissionData.formtype, 
                     dbo.ClientAdmissionData.date_entered, dbo.ClientAdmissionData.emp_name FROM dbo.ClientEpisode INNER JOIN
                     dbo.ClientAdmissionData ON dbo.ClientEpisode.episode_id = dbo.ClientAdmissionData.episode_id INNER JOIN
                     dbo.Clients ON dbo.ClientAdmissionData.client_id = dbo.Clients.client_id LEFT OUTER JOIN
                     dbo.LookupDict AS geocode ON dbo.ClientAdmissionData.geocode = geocode.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS admission_type ON dbo.ClientEpisode.admission_type = admission_type.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS admit_type ON dbo.ClientAdmissionData.admittype = admit_type.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS race ON dbo.Clients.race = race.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS hispanic_origin ON dbo.Clients.hispanic_origin = hispanic_origin.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS pregnant ON dbo.ClientAdmissionData.pregnant = pregnant.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS livingarrange ON dbo.ClientAdmissionData.livingarrang = livingarrange.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS medicaidmco ON dbo.ClientEpisode.medicaid_mco_provider = medicaidmco.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS paysource ON dbo.ClientEpisode.pay_source = paysource.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS empstatus ON dbo.ClientAdmissionData.employment = empstatus.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS unemployment ON dbo.ClientAdmissionData.unemployment = unemployment.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS schooljob ON dbo.ClientAdmissionData.schooljob = schooljob.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS family_income_override ON dbo.Clients.family_income_override = family_income_override.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS income_source ON dbo.Clients.income_source = income_source.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS preferred_language ON dbo.Clients.preferred_language = preferred_language.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS hearing_status ON dbo.Clients.hearing_status = hearing_status.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS referral_source ON dbo.ClientEpisode.referral_source = referral_source.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS referral_office ON dbo.ClientEpisode.referral_office = referral_office.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS priorepisodes ON dbo.Clients.prior_treatment_episodes = priorepisodes.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS problemarea ON dbo.ClientAdmissionData.problemarea = problemarea.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS primdrug ON dbo.ClientAdmissionData.primdrug = primdrug.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS primdrugfreq ON dbo.ClientAdmissionData.primdrugfreq = primdrugfreq.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS primdrugrout ON dbo.ClientAdmissionData.primdrugrout = primdrugrout.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS primdrugsevr ON dbo.ClientAdmissionData.primdrugsevr = primdrugsevr.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS secdrug ON dbo.ClientAdmissionData.secdrug = secdrug.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS secdrugfreq ON dbo.ClientAdmissionData.secdrugfreq = secdrugfreq.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS secdrugrout ON dbo.ClientAdmissionData.secdrugrout = secdrugrout.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS secdrugsevr ON dbo.ClientAdmissionData.secdrugsevr = secdrugsevr.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS tertdrug ON dbo.ClientAdmissionData.tertdrug = tertdrug.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS tertdrugfreq ON dbo.ClientAdmissionData.tertdrugfreq = tertdrugfreq.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS tertdrugrout ON dbo.ClientAdmissionData.tertdrugrout = tertdrugrout.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS tertdrugsevr ON dbo.ClientAdmissionData.tertdrugsevr = tertdrugsevr.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS marital_status ON dbo.Clients.marital_status = marital_status.lookup_id LEFT OUTER JOIN
                     dbo.LookupDict AS attendgroups ON dbo.ClientAdmissionData.attendgroups = attendgroups.lookup_id

Answer 1

我显然遗漏了一些东西，因为我看不出标量UDF如何更“可扩展”--除非您的意思是您可以更改数据模型，而不是更改视图。

我不认为为此目的使用左联接会有什么麻烦，所以视图似乎是一个显而易见的解决方案。如果确实使用标量UDF，那么使用这些UDF计算列似乎是一种很好的方法。

尽管如此，还有两个更重要的考虑因素。如果性能是一个问题，那么尝试不同的方法(在合理大小的数据上)，看看哪一种是最好的。第二，你觉得最舒服的是什么？您需要编写、维护、记录和测试代码。