数据库逻辑删除的sql语句_SQL查询优化的数据库设计和逻辑
断⾔
数据库逻辑删除的sql语句
Database design and Logical Asseveration play a vital role in database performance and SQL Query optimization. Both have different parameters to make your database and the query accurate.
数据库设计和逻辑断⾔在数据库性能和SQL查询优化中起着⾄关重要的作⽤。 两者都有不同的参数以使您的数据库和查询准确。
数据库设计 (Database Design)
Database design plays an essential role in the database performance side. If the table structure is not in proper terms of data distribution and normalized way, then it could raise the significant challenge to change the structure as and when a problem comes in the picture. We will discuss a few parameters below to keep in mind when we design the database.
数据库设计在数据库性能⽅⾯起着⾄关重要的作⽤。 如果表结构没有按照正确的数据分布和规范化⽅式进⾏，那么当问题出现时，更改表结构可能会带来重⼤挑战。 在设计数据库时，我们将在下⾯讨论⼀些要记住的参数。
正常化 (Normalization)
Normalization is a technique to structure relational data in the form of schema and table to reduce the data redundancy and avoid the data anomaly. Data relation can be designed in 1NF, 2NF, 3NF as required with the nature of the information. Performance side, Normalization reduces IO operations with avoiding Insert/Update/Delete anomaly. Normalization of the database structure causes less amount of storage compared to unstructured data in the SQL Server database. You can understand it more over here,
规范化是⼀种以架构和表的形式构造关系数据以减少数据冗余并避免数据异常的技术。 可以根据信息的性质在1NF，2NF，3NF中设计数据关系。 在性能⽅⾯，规范化避免了Insert / Update / Delete异常，从⽽减少了IO操作。 与SQL Server数据库中的⾮结构化数据相⽐，数据库结构的规范化导致较少的存储量。 您可以在这⾥进⼀步了解它，
For example:
例如：
We have a table with Merchant_stock with details of Item, Merchant, Available Quantity, Price. We normalized it to end the level with the lookup table references in the last normalized form.
我们有⼀个带有Merchant_stock的表格，其中包含商品，商家，可⽤数量，价格的详细信息。 我们对其进⾏归⼀化，以最后⼀次归⼀化形式的查表引⽤结束该级别。
When a user wants details of Merchant, Merchant City, and Merchant status with the stock item, then the user can join multiple tables with the clause of Primary Key of the lookup tables.
当⽤户想要库存商品的商⼈，商⼈城市和商⼈状态的详细信息时，则⽤户可以将多个表与查表的主键⼦句连接起来。
SELECT * --(Required Columns)
FROM Item AS I
INNER JOIN Merchant AS M hant_id = M.merchant_id
INNER JOIN city AS C ON C.city_id = M.city_id
INNER JOIN merchant_status AS MS hant_status = MS.status_id
End level normalization reduces the redundancy of the table. For example: a user wants to change the city or status for the merchant, (n) numbers of rows will be affected in the update statement with the first case of the above example
table(merchant_stock). And in the second case, just one row will be affected in the Merchant table. Again status name change will update (n) rows in the second case. Therefore, in the third case, Integer reference is used for the merchant staus to avoid the update (n) rows in the table.
最终级别规范化减少了表的冗余。 例如：⽤户想要更改商家的城市或状态，在上述⽰例表的第⼀种情况下（ 商⼈ _股票），（n）⾏数将在更新语句中受到影响。 在第⼆种情况下， Merchant表中将只影
响⼀⾏。 在第⼆种情况下，状态名称更改将再次更新（n）⾏。 因此，在第三种情况下，Integer引⽤⽤于商户状态以避免表中的更新（n）⾏。
Static kinds of fields should be store in the lookup table always, and use the integer reference in the detail table is the best practice for the database design with the normalization.
静态类型的字段应始终存储在查表中，并且在明细表中使⽤整数引⽤是通过规范化进⾏数据库设计的最佳实践。
指标 (Indexes)
Indexes are a reference to a quick search on the information. An index is a powerful tool to directly locate the target data rather than scanning the entire table and find the required row/s from the table. The use of a Clustered or Non-Clustered index is to make quick data matching patterns. The index improves the overall query performance with the wise use of it. SQL Server allows us to create an index with the number of options as below, and each one has a significant role in the Query performance.
索引是对信息进⾏快速搜索的参考。 索引是⼀种功能强⼤的⼯具，可直接定位⽬标数据，⽽不是扫描
整个表并从表中查所需的⾏。 使⽤聚簇索引或⾮聚簇索引是为了建⽴快速的数据匹配模式。 索引的明智使⽤提⾼了整体查询性能。 SQL Server允许我们创建⼀个具有以下选项数量的索引，每个选项在查询性能中都起着重要作⽤。
Index Options:
索引选项：
ALLOW_PAGE_LOCKS
ALLOW_PAGE_LOCKS
ALLOW_ROW_LOCKS
ALLOW_ROW_LOCKS
DATA_COMPRESSION
DATA_COMPRESSION
DROP_EXISTING
DROP_EXISTING
FILLFACTOR
填充因⼦
IGNORE_DUP_KEY
IGNORE_DUP_KEY
MAX_DURATION
MAX_DURATION
MAXDOP (max_degree_of_parallelism)
MAXDOP（最⼤平⾏度）
ONLINE
线上
OPTIMIZE_FOR_SEQUENTIAL_KEY
OPTIMIZE_FOR_SEQUENTIAL_KEY
PAD_INDEX
PAD_INDEX
RESUMABLE
可恢复
SORT_IN_TEMPDB
SORT_IN_TEMPDB
STATISTICS_INCREMENTAL数据库优化sql语句
STATISTICS_INCREMENTAL
STATISTICS_NORECOMPUTE
STATISTICS_NORECOMPUTE
表分区 (Table Partition)
SQL Server Table Partition is a way of distributing the data across the file system with the set of user-defined rules. When we are working on a large-sized table, the row filter option is also available; however, it is comparatively slower compared to Table Partition option. When we have used the Table Partition on the table, the Query filter will target the required portion of the table and return the result-set relatively quickly. A query filter will decide to focus on a particular piece or partition of the table by the partition schema. Proper use of the Table Partition and table design make excellent query performance on the large table.
SQL Server表分区是⼀种使⽤⼀组⽤户定义的规则在⽂件系统中分发数据的⽅法。 当我们处理⼤型表时，⾏过滤器选项也可⽤； 但是，与“表分区”选项相⽐，它相对较慢。 当我们在表上使⽤了表分区后，查询过滤器将以表的所需部分为⽬标并相对快速地返回结果集。 查询过滤器将决定通过分区模式将重点放在表的特定部分或分区上。 正确使⽤表分区和表设计可以使⼤型表具有出⾊的查询性能。
Users can add the File Group and Files with the database to use partition with the table. For example:
⽤户可以将⽂件组和⽂件与数据库⼀起添加，以对表使⽤分区。 例如：
--Adding File Group for a Database
ALTER DATABASE [Database Name]
ADD FILEGROUP [File Group name]
GO
--Adding File to File Group
ALTER DATABASE [Database Name]
ADD FILE
(
NAME = [PartJan],
FILENAME = '\C:\\...\DATA\File.ndf',
SIZE = 2048 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [File Group name]
GO
⾏平衡 (Row balancing)
Keeping rows in a single table for all clients is not the only solution in a product-based database solution. For the different geographic client base, data needs to be stored in the database according to the client geography to meet the latency issues and better SQL Query optimization. An application can be deployed on multiple locations to perform different activities; however, the database insert location will be single. Therefore, we can have a database set up with the same tables on multiple sites, and according to the client, geography requests can be served to the target database.
在所有基于客户端的表中保留⾏并不是在基于产品的数据库解决⽅案中的唯⼀解决⽅案。 对于不同的
地理客户基础，需要根据客户地理位置将数据存储在数据库中，以满⾜延迟问题和更好SQL查询优化。 可以将应⽤程序部署在多个位置以执⾏不同的活动。 但是，数据库插⼊位置将是单个。 因此，我们可以在多个站点上建⽴⼀个具有相同表的数据库，并且根据客户端，可以将地理请求提供给⽬标数据库。
约束条件 (Constraints)
SQL Server Query Optimizer always checks for the foreign key and check constraints to prepare an efficient query execution plan, and each execution runs on the execution plan. SQL Server optimizer is too reliable and smart; if any constraint exists on the column, then the execution plan will skip the unnecessary part of data manipulation checks.
SQL Server Query Optimizer始终检查外键并检查约束以准备有效的查询执⾏计划，并且每次执⾏都在执⾏计划上运⾏。 SQL Server优化器过于可靠和聪明； 如果列上存在任何约束，则执⾏计划将跳过数据操作检查的不必要部分。
逻辑断⾔ (Logical Asseveration)
Logical Asseveration is a part of the query process and related performance parameters. Different qu
ery strategies with the most efficient way are the right solutions for SQL Query Optimization. Here we will discuss a few parameters below to keep in mind when we write a query, Procedure, Function, or Trigger.
逻辑断⾔是查询过程和相关性能参数的⼀部分。 以最有效的⽅式使⽤不同的查询策略是SQL查询优化的正确解决⽅案。 在这⾥，我们将讨论以下⼀些参数，以便在编写查询，过程，函数或触发器时牢记。
联接结构 (JOIN Structure)
structure with improper order of the table affects the query performance when several tables are present in joins with multiple conditions in the ‘Where’ clause. Query optimizer always refers to the table order in joins and changes it accordingly to prepare the execution plan. However, the optimizer will not follow all the possible orders with the query statement. Therefore, the developers have to try to order the table in the prepared query with one to one mapping. Improper order of tables in the join query consumes more resources in the server, and it affects other queries as well in the SQL Server.
当“ Where”⼦句中具有多个条件的联接中存在多个表时，表顺序不正确的 结构会影响查询性能。 查询优化器始终在联接中引⽤表顺序，并相应地对其进⾏更改以准备执⾏计划。 但是，优化器不会使⽤查
询语句来遵循所有可能的顺序。 因此，开发⼈员必须尝试使⽤⼀对⼀映射在准备好的查询中对表进⾏排序。 联接查询中表的顺序不正确会消耗服务器中的更多资源，并且还会影响SQL Server中的其他查询。
关系表达 (Relation Expression)
Relational Expression in the WHERE clause is significant for SQL Query optimization. IF EXISTS, IF NOT EXISTS, NOT IN, IN, CASE WHEN, and many more conditions can be used in the WHERE clause. Sightless use of conditions makes a surprising execution time and execution plan too. Even
NOT IN can be replaced by the LEFT JOIN with IS NULL condition as well, which is not known to many developers. What kind of Expression can be used, and when that depends on the nature and types of data? To make your query fast and optimized, it depends on the logic which is being written by the developer.
WHERE⼦句中的关系表达式对于SQL查询优化很重要。 如果存在，如果不存在，则不输⼊，输⼊，发⽣时以及在WHERE⼦句中可以使⽤更多条件。 盲⽬使⽤条件也使执⾏时间和执⾏计划变得令⼈惊讶。 甚⾄NOT IN也可以⽤具有IS NULL条件的LEFT JOIN替换，这对于许多开发⼈员⽽⾔并不为⼈所知。 可以使⽤哪种表达式，什么时候取决于数据的性质和类型？ 为了使查询快速和优化，取决于开发⼈员正在编写的逻辑。
For example:
例如：
SELECT *
FROM TableA
WHERE COL1 NOT IN (SELECT COL1 FROM TableB)
Or
要么
SELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B ON A.COL1 = B.COL1
WHERE B.COL1 IS NULL
3.数据排序 (3. Data Sorting)
Data Sorting is an expensive part of the query execution in SQL Server. Users can avoid using unnecessary sorting in the SQL Server program because it stores the query result set into the buffer to sort it in particular order reference. Even a SQL Server index option provides an option to store data with the sorting, yet again it is the job of the query optimizer to make perfect query execution plan.
数据排序是SQL Server中查询执⾏的昂贵部分。 ⽤户可以避免在SQL Server程序中使⽤不必要的排序，因为它将查询结果集存储到缓冲区中以按特定的顺序引⽤对其进⾏排序。 甚⾄SQL Server索引选项都提供了⼀种存储数据以进⾏排序的选项，再次，查询优化器的⼯作就是制定完美的查询执⾏计划。
For example: an Index is defined with the sorting column option, and rows returns from the table will be in order as well; however, SQL statement can have multiple fields in the ORDER BY clause. So, it could cause various issues to process the data to sort into the order of columns in the ORDER BY term. User needs to use required fields only to having data sorting in a particular result set only to avoid unnecessary data sorting in the procedure.
例如：使⽤排序列选项定义了索引，并且从表返回的⾏也将按顺序排列； 但是，SQL语句在ORDER BY⼦句中可以有多个字段。 因此，这可能会导致各种问题来处理数据以将它们排序为ORDER BY术语中的列顺序。 ⽤户只需要在特定结果集中进⾏数据排序就可以使⽤必填字段，以避免在过程中进⾏不必要的数据排序。
SET选项 (SET Options)
When we are working on large-sized database or having good experience in SQL Query Optimizatio
n then definitely we could face issues like: Multiple execution plan for the single procedure in the SQL Server, Query or procedure will turn the execution faster in SSMS but not with the application, errors can be returned by the application for the procedure (Nested Procedure), but it works fine using SSMS, and many more issues can be resolved with the help of declaration in the SQL Server.
当我们在⼤型数据库上⼯作或在SQL查询优化⽅⾯有丰富的经验时，我们肯定会遇到以下问题：SQL Server中单个过程的多执⾏计划，查询或过程将使SSMS中的执⾏速度更快，⽽在在应⽤程序中，该应⽤程序可以为该过程（嵌套过程）返回错误，但是使⽤SSMS可以正常运⾏，并且可以借助SQL Server中的声明解决更多问题。
When we execute a query using SSMS, it uses SSMS default configured SET Options. Whether application uses SET Options, which are defined in the procedure or defined in the SQL Connection, therefore, there could be different ways of execution for the same procedure by the SQL Server query optimizer and to make multiple execution plans with a set of SET Options.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。