无论你的专业水平如何,从其他IT专家那里学习新的技巧与最佳实践常常都是有益的。本文包含了我遇到过的SQL Server开发的高级技巧。希望其中的一些技巧能够对您的数据库开发及管理工作有所帮助。
确保代码中的数据类型与数据库中的列类型保持一致确保您的应用程序各层数据类型保持一致是非常重要的。例如,如果一列的数据类型为NVARCHAR(50),那么,您应该在代码查询与存储过程中使用相同类型的局部变量。
同样,数据层中的ADO.NET代码也应该指定相同的数据类型与长度。为什么这很重要呢?因为如果数据类型与查询匹配,SQL Server需要先进行数据类型的隐式转换,以使它们能够匹配。
也有一些情况,即使为参照列设置了索引,SQL Server却不能使用此索引。因此,变量与列类型一致的情况下,您的查询可能会使用Index Scan而不是Index Seeking,这样需要执行的时间就更长了。
在中进行大规模更新
开发人员有时需要对一张表中的一列或多列中的全部或大部分列进行数据修改。通常,对小表而言这并不是一个什么问题。
然而,如果表很大的话,您的更新语句将锁定整张表,使它无法使用,甚至都不能读取。更有甚者,对一张频繁变化的表进行更新可能使整个应用程序或网站瘫痪。有时,在极端情况下,一个大的、单个事务将导致事务日志急剧增长,并最终耗尽数据库服务器磁盘空间。
因此,好的策略是进行分批大规模更新,并结合频繁的事务日志备份。以我的经验看,最好一批10,000至50,000工作量。当您开始考虑应用批量处理时,确定阈值很困难,因为这取决于诸多因素比方说如何使I/O更快,如何使表高效利用等等。
您可以考虑一个准则。在ADO.NET中,典型的命令超时时间是30秒左右。当开始更新时,其他进程一直处于等待状态直到更新结束。因此如果期望更新时间超过20-25秒,您最好进行一个更新。否则,将以应用程序超时而结束。
下面这段简单的代码展示了如何更新表中的一列,应用的批量大小为10,000:
WHILE ( 0 = 0 )
BEGIN
UPDATE TOP ( 10000 )
Person
SET Status = 2
WHERE Status = 1
IF @@ROWCOUNT = 0
BREAK
END
应用FOR-EACH存储过程
有些时候您可能需要对某一特定类型的所有对象执行相同的操作。例如,您可能需要对数据库中的所有表分配特定的权限。开发人员经常通过指针设置这样的任务,但是SQL Server中两个简单的存储过程可以更容易实现:sp_msForEachTable 与 sp_msForEachDB。
每个存储过程作为一个参数执行命令。在命令中,您把表名或数据库名作为一个问号标志占位符嵌入到参数中。命令运行时,SQL Server把问号标志替换为表名或数据库名,并执行。
例如,下面的代码在Server上除TempDB外,对每个数据库进行全备份:
EXEC sp_msforeachdb 'IF ''?'' ''tempdb'' BACKUP DATABASE ?
TO DISK=''c:\backups\?.bak'' WITH INIT'
这是另外一个如何应用这些存储过程的例子。下面的代码在禁用外键后,删除数据库所有表中的数据。当然了,当使用这些代码时,您需要谨慎的练习。
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
建立数据库版本
对开发人员而言,如同对您的应用程序版本化一样,对数据库执行数字版本化是一个很好的方法。
执行版本化并不需要很大的工作量,您只需创建一个包含版本号列及时间戳列的版本表即可。当部署那些脚本时,您将更好的分配每个脚本集合的版本号,并对版本表进行更新,检查错误与数据库对比将变得更加容易。您甚至可以对脚本进行编号,这样一来如果数据库中建立的编号不比脚本中建立的编号高的话,脚本就不执行。样例数据库AdventureWorks中的AWBuildVersion就是一个很好的例子,可以看看。
尽量减少网络会话
这个技巧主要针对从数据库取数据的网络应用程序。缺乏经验的开发人员常常意识不到数据库调用是代价很高的操作。对于小应用程序而言,这不是什么大问题。但是,由于很多网站变得非常火爆导致数以千计的用户同时在线,那么您就有必要提前考虑它的可扩展性与网页加载时间的优化问题了。
我曾经看到过的网页有多达15个数据库调用,而大多数正在执行的存储过程就是为了返回单独的一行或一个值。需要牢记的是在SQL Server中一个单独的存储过程能够返回多个结果集。在一个存储过程中,您可以使用ADO.NET中的DataSet对象以及把DataTable对象组成一个集合。

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Dreamweaver CS6
Visual web development tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 English version
Recommended: Win version, supports code prompts!

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
