随笔分类 -  SQL

查看SQL连接池当前已使用连接数
摘要：SELECT des.program_name , des.login_name , des.host_name , COUNT(des.session_id) [Connections]FROM sys.dm_exec_sessions desINNER JOIN sys.dm_exec_conn 阅读全文
posted @ 2021-06-03 17:25 Researcher 阅读(845) 评论(0) 推荐(0)
SQL Join 理解
摘要：对各种连接的理解，可以参照文章。 下面是对连接结果表条数统计的思考：假设有主表Ta有5条记录，从表Tb有4条记录 Ta corss join Tb, 结果为2表做笛卡尔积，5*4=20条 /*下面其它连接返回的结果都是cross join的子集*/ Ta inner join Tb, 结果分为有重复 阅读全文
posted @ 2018-05-24 11:21 Researcher 阅读(412) 评论(0) 推荐(0)
SQL性能优化思路
摘要：1. 尽可能把数据的存储和计算放入Memory而不是Disk，且减少IO操作，比如运用Redis等缓存技术 2. 对数据表进行精心设计，特别是大数据表，对常用数据字段进行适当的冗余，尽可能避免分表导致的Join查询，即使非Join不可也是大表和小表Join，而不是一堆大表之间Join 3. 对常用查 阅读全文
posted @ 2018-05-23 20:08 Researcher 阅读(362) 评论(0) 推荐(0)
Disable trigger to avoid the ID is auto-updated
摘要：CREATE TABLE COBRA.COBRA_PRODUCT_INFO_BAK AS SELECT * FROM COBRA.COBRA_PRODUCT_INFO; TRUNCATE TABLE COBRA.COBRA_PRODUCT_INFO; ALTER TABLE COBRA.COBRA_ 阅读全文
posted @ 2017-10-10 16:20 Researcher 阅读(133) 评论(0) 推荐(0)
数据库中锁的问题
摘要：为了防止数据被错误修改例如商品库存被改为-1如何加锁，应该加什么锁，相应的机制是什么 阅读全文
posted @ 2017-02-21 15:46 Researcher 阅读(199) 评论(0) 推荐(0)
SP和Fuction的关系
摘要：它们之间是什么关系，谁调用谁 阅读全文
posted @ 2017-02-21 15:42 Researcher 阅读(138) 评论(0) 推荐(0)
How to resolve the truncate/drop/delete/join hang issue in ADW
摘要：In some case, we found that when we execute the sql commands like truncate table, drop table, delete all records in table, join 2 tables, it will take 阅读全文
posted @ 2016-12-10 00:10 Researcher 阅读(146) 评论(0) 推荐(0)
How to get the mapping relationship between two columns in a table
摘要：If a table have column A and B Count(distinct A) as Da Count(distinct B) as Db Count(distinct A, B) as Dab The Da/Db<=Dab, this will be always true; 1 阅读全文
posted @ 2016-12-08 18:43 Researcher 阅读(127) 评论(0) 推荐(0)
Use Select To Generate Any Insert/Delete/Update Statement
摘要：If you don't have the permission to generate script according to an existing db, but you have the read permission for that db, if so you can use selec 阅读全文
posted @ 2016-12-01 16:02 Researcher 阅读(113) 评论(0) 推荐(0)
用Redis作为缓存服务器，加快数据库操作速度
摘要：https://zh.wikipedia.org/wiki/Redis http://www.jianshu.com/p/01b37cdb3f33 阅读全文
posted @ 2016-10-21 10:47 Researcher 阅读(801) 评论(0) 推荐(0)
Run Performance Testing Which Was Distributed To Multiple Test Agents
摘要：How to solve the VS installed machine cannot run performance testing by .testsettings file, which will distribute the tests running on multiple test a 阅读全文
posted @ 2016-08-14 09:05 Researcher 阅读(120) 评论(0) 推荐(0)
What is the difference for delete/truncate/drop
摘要：Same:delete/truncate/drop, all of them can support rollback/commit, the sample is as below:begin tran T1truncate table TestTablerollback/commitDiffere... 阅读全文
posted @ 2015-11-25 13:52 Researcher 阅读(143) 评论(0) 推荐(0)