时间戳

首页 > 解决方案 > 从单个查找表创建视图的最佳实践是什么？

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

问题描述

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

我想做的是创建一个视图，将查找 ID 转换为相应的描述（此特定表中的 25-30 个查找列）。从我读过的内容来看，我的选择似乎如下。我的问题是这些（或我没有想到的任何其他方法）中的哪一个将提供性能、可读性和可扩展性的最佳组合。

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

1 似乎是最简单的，但维护和阅读会很麻烦，并且无法转换为我需要此功能的其他表。

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

3 是我的首选，因为它具有极强的可扩展性并且使视图更具可读性。但是，我知道这是 MSSQL 的新增功能，因此我非常担心潜在的性能问题与 1 相比。

有人对这种特定方法有任何意见/建议吗？

编辑：这是一个突出使用直接连接的潜在负担的示例。这种特殊的观点要求我将短代码和描述组合在一起。我认为内联 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

标签： sqlsql-serverviewlookupsql-server-2019

解决方案

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

我看不出为此目的使用左连接有什么麻烦，因此视图似乎是一个明显的解决方案。如果您确实使用标量 UDF，那么使用这些 UDF 的计算列似乎是一个好方法。

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

推荐阅读