![](https://img-blog.csdnimg.cn/fd842f38ab8740818ad706e088ebba40.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
SQL Server相关
SQL Server相关内容
wangyanglongcc
数仓工程师,多年数据处理、分析经验。擅长数仓ETL,数仓模型设计建设。
对微软云产品较为熟悉,如Azure Data Factory,Azure Databricks,SqlServer等。
对Python,Sql,Excel等较为熟悉。
展开
-
获取当前存储过程名和错误信息
SELECT OBJECT_NAME(@@PROCID) AS PROCEDURE_NAME,ERROR_MESSAGE() AS ERR原创 2021-01-26 17:57:51 · 196 阅读 · 0 评论 -
SQL Server中利用substring_split进行数据按指定分隔符展开
如下图salers和percents是一一对应的关系,现需要将salers和percents进行拆开。结果如下图2创建模拟数据表create table sales_test(order_id int,sku_id int,salers nvarchar(50),percents nvarchar(50))通过循环创建模拟数据DECLARE @i INT = 1;WHILE @i <= 100000BEGIN insert into sales_test values原创 2020-11-02 16:40:26 · 421 阅读 · 0 评论 -
SQL Server的3中备份方式
在工作中,通常是完整备份和差异备份相结合,如每周进行一次完整备份,每天进行差异备份。1. 完整备份可定期备份,如每周,每月一次BACKUP DATABASE [BI_ODS] TO DISK = @bak_fileWITH NOFORMAT, NOINIT, NAME = @bak_name, SKIP, REWIND, NOUNLOAD, STATS = 10DECLARE @time_stamp varchar(17) ;-- 文件路径DECLARE @bak_file var原创 2020-07-16 11:12:07 · 296 阅读 · 0 评论 -
部署SSIS
部署SSISUSE [SSISDB]GODECLARE @ProjectBinary AS VARBINARY(MAX)DECLARE @operation_id AS BIGINTSET @ProjectBinary =(SELECT * FROM OPENROWSET(BULK 'D:\SSIS\FirstDemo\FirstDemo\bin\Development\FirstDemo.ispac',-- SSIS部署包的路径SINGLE_BLOB) AS BinaryData原创 2020-07-15 18:56:33 · 305 阅读 · 0 评论 -
SQL 无法解决 EQUAL TO 运算中 ";CHINESE_PRC_CI_AS"; 和 ";CHINESE_PRC_90_CI_AI"; 之间的排序规则冲突
问题描述在使用SQL Server做多表关联时,遇到以下错误,由于字段排序规则不同,导致无法关联![image.png](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvNjcxOTcwLzE1OTQ3MTI3NTAwMzgtOTQzZjdkMTMtMjNlZS00ZWJhLTgwMmYtMmI0NWVhMmQzZDE3LnBuZw?x-oss-process=image/format,pn原创 2020-07-15 18:52:51 · 833 阅读 · 0 评论 -
SQL查询出每门课程成绩都大于85的学生信息
解题思路这道题目使用反向思维,我们第一步获取任意一门成绩小于等于85的学生,然后在过滤出不在该列表中的学生信息,即为所有科目成绩都大于85分。创建示例数据DROP TABLE IF EXISTS grade;CREATE TABLE IF NOT EXISTS grade(name varchar(20),course varchar(20),score int);INSERT INTO grade VALUES ('a','chinese',82),('a','math',75),('b','c原创 2020-07-12 23:52:15 · 9017 阅读 · 2 评论 -
SQL Server的3种恢复模型
恢复模型 优点 工作损失表现 能否恢复到即时点 简单恢复 允许高性能大容量复制操作 收回日志空间以使空间要求最小 必须重做自最新的数据库或差异备份后所发生的更改 可以恢复到任何备份的结尾,随后必须重做更改 完全恢复 数据文件丢失或损坏不会导致工作损失...原创 2020-07-12 23:39:13 · 510 阅读 · 0 评论 -
SQL Server常用日期计算
获取本周、本月、本季、本年的第一天和最后一天YTD|MTD|WTD|LMD|LWD计算DECLARE @today datetimeSET @today = GETDATE()-- TRAGET DATE-- [本年第一天,本年最后一天]SELECT 'YTD' AS CATEGORY_TIME ,@today AS CURRENT_DAY ,DATEADD(year, -1, @today) AS LAST_CURRENT_DAY -- 同期 ,DATEADD(year原创 2020-07-12 21:20:04 · 170 阅读 · 0 评论 -
SQL求总和的百分比
思路分析:先通过group by得到每个分类别的总和,再通过开窗函数计算整体的总和,两者相除就得到占比了。当over()后不加任何内容时,就是对所有的数据进行汇总。1.使用over (partition by)得到总计select sites_id,channel_id,volume, sum(volume) over (PARTITION by sites_id) as volume_sum from ( select sites_id,channel_id,sum(volume原创 2020-07-12 19:15:04 · 7666 阅读 · 0 评论 -
SQL获取上一条记录的信息
该方法可用于计算两个相邻日期间的间隔,如可用来计算用户活跃度,看出每个用户多次下单中的每两次之间的时间间隔,用以划分用户(如很活跃,活跃等,),还可以看出用户的大致购买时间间隔,对于超出时间间隔未再次下单的用户(沉睡用户),可想办法进行唤醒等。1.查看现有数据select * from emp;2.通过分析函数lag直接获取上一个记录的信息select deptno,hiredate, lag(hiredate) over (partition by deptno order by hi原创 2020-07-12 14:03:20 · 5647 阅读 · 0 评论 -
SQL数据库实现增量更新
假设现在有两个表,stg_table和ods_table,两者字段一模一样(建表语句如下)。我们现在需要将stg表中的数据增量插入到ods中去。create table stg_table(col1 varchar(5),col2 int,col3 int);create table ods_table like stg_table;insert into stg_table values ('a',1,1),('c',3,3);insert into ods_table values ('a',1原创 2020-07-12 13:02:30 · 8795 阅读 · 0 评论 -
SQL Server维度状态变化转换(纵表转横表)
原表及结果表如下图所示做法:通过表自关联转换行列。根据NAME,FROM_DATE,STATUS使用开窗函数取最小的TO_DATEwith T1 AS (SELECT 'A' AS NAME,'2020-01-01' AS [DATE],'active' AS STATUS UNION SELECT 'A' AS NAME,'2020-02-01' AS [DATE],'sleep' AS STATUS UNION SELECT 'A' AS NAME,'2020-02-26' AS原创 2020-07-09 18:27:54 · 754 阅读 · 0 评论 -
SQL Server角色
固定数据库角色名说明db_ownerdb_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动,还可以删除 SQL Server中的数据库。 (在 SQL 数据库 和 SQL 数据仓库中,某些维护活动需要服务器级别权限,并且不能由 db_owners执行。)db_securityadmindb_securityadmin 固定数据库角色的成员可以仅修改自定义角色的角色成员资格和管理权限。 此角色的成员可能会提升其权限,应监视其操作。db_accessadmi...转载 2020-07-07 23:57:31 · 504 阅读 · 0 评论 -
SQL Server物理文件类型
1. 主数据文件主要数据文件是数据库的起点,指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。其默认扩展名是 .mdf。2.辅助数据文件辅助数据文件包含除主要数据文件外的所有数据文件。一个数据库可以创建多个辅助数据文件。其默认扩展名是 .ndf。3.事务日志文件事务日志文件记录对数据库的所有操作,但不包含所操作的数据。其默认拓展名为.ldf。所有的数据文件或日志文件默认存放在C:\Program Files\Microsoft SQL Server\MSSQL.xxx\MSSQL\DA原创 2020-07-07 23:52:33 · 1137 阅读 · 0 评论 -
SQL Server中本地临时表和全局临时表的区别
在SQL Server中,临时表有两种类型,分别为本地临时表和全局临时表。临时表和永久表相似,但临时表存储在数据库tempdb中,当不再使用时回自动删除。区别作用域不同本地临时表只对创建这个表的用户的SESSION可见,对其他进程是不可见的。当创建它的进程消失时,这个临时表就会自动删除。全局临时表对整个SQL Server示例都可见,只有当所有访问它的SESSION都消失的时候,它才会自动删除。创建方式不同本地临时表的名称是以单#打头,而全局临时表的名称是以双#打头,如它们的创建语句如下:.原创 2020-07-07 23:39:47 · 1087 阅读 · 0 评论 -
SQL Server的存储结构
SQL Server的存储结构有两种,分别是页和区间。页用于数据存储的连续的磁盘空间块,SQL Server中数据存储的基本单位是页,磁盘I/O操作在页级执行,页的大小为8kb,每页的开头是96字节的页头,用于存储有关页的系统信息,包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID。区间区间是管理空间的基本单位,一个区是8个物理上连续的页(即64KB)的集合,所有页都存储在区中。SQL Server有两种类型的区:统一区和混合区。统一区:由单个对象所有,区中的所有8页只能由一个对象.原创 2020-07-07 23:30:23 · 1432 阅读 · 0 评论 -
SQL Server的4个默认数据库
1. mastermaster数据库时SQL Server的主数据库,其保存放在SQL Server实体上的所有数据库元数据的详细信息,它还是将引擎固定起来的粘合剂。由于如果不使用master数据库,那么SQL Server就不能启动,所以,必须要小心地管理好这个数据库。因此,对这个数据库进行常规备份是十分有必要的。这个数据库还包括了诸如系统登录、配置设置、已连接的Server信息、拓展存储过程等。2. model模型数据库时一个用来在实体上创建新用户数据库的模板数据库,可以把任何存储过程、视图、用户原创 2020-07-07 23:18:58 · 2826 阅读 · 0 评论 -
SQL Server获取系统时间及查看版本号
使用函数GETDATE获取系统时间。使用@@VERSION获取版本号。SELECT GETDATE() AS NOW,@@VERSION AS MS_VERSION原创 2020-07-07 23:04:10 · 357 阅读 · 0 评论 -
数据仓库星型模型VS雪花模型
在多维分析的商业智能解决方案中,根据事实表和维度表的关系,可将常见的模型分为星型模型和雪花模型。在设计逻辑型数据的模型的时候,就应考虑数据是按照星型模型还是雪花模型进行组织。星型模型当所有维表都直接连接到事实表上时,整个图解就像星星一样,故将该模型称为星型模型。星型架构是一种非正规化的结构,多维数据集的每一个维度都直接与事实表相连接,不存在渐变维度,是反范式的,所以数据有一定的冗余。其模型示例如下图所示雪花模型当有一个或多个维表没有直接连接到事实表上,而是通过其他维表连接到事实.原创 2020-07-06 21:20:33 · 468 阅读 · 0 评论 -
SQL注入
概念所谓SQL注入(SQL Injection),就是通过把SQL命令插入WEB表单提交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令的目的。例如,在代码中使用如下SQL语句:SELECT * FROM USER WHERE NAME = "'" + NAME + "'" AND PASSWORD = "'" + PASSWORD + "'"来验证用户名和密码是否正确,其中NAME和PASSWORD是用户输入的内容,当用户输入名为AA,密码为"BB OR 'A' = 'A’原创 2020-07-06 21:06:34 · 149 阅读 · 0 评论 -
SQL Server中聚集索引和非聚集索引的区别有哪些?
聚集索引与非聚集索引的根本区别是表记录的物理排序顺序和索引的排序顺序是否一致。其主要区别有以下几点: 聚集索引一个表只能有一个,非聚集索引一个表可以存在多个。 聚集索引存储记录是物理上连续存在,物理存储按照索引排序;而非聚集索引是逻辑上的连续,物理存储并不连续,物理存储不按照索引排序。 聚集索引查询数据比非聚集索引速度快,插入数据速度慢(时间花费在「物理存储的排序」上,也就是首先要找到位置然后插入);非聚集索引反之。 索引是通过B+Tree的结构来描述的,聚集索引的叶节点原创 2020-07-06 00:43:10 · 2420 阅读 · 1 评论 -
Server存储过程中 SET ANSI_NULLS ON 和 SET QUOTED_IDENTIFIER ON的作用解释
在通过sql server2005/2008创建存储过程的文件时,在文件的开头部分会默认出现下面这几行代码:USE [Test]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO作用和详解:USE:指明整个存储过程所调用/使用的数据库,其中Test是我本地建立的数据库名称,USE [Test]就是告诉程序,要调用/使用的是我本地的Test数据库的意思。必须要指明调用/使用的具体数据库。GO:该语句不是SQL的语句,表示一个事务结束的标识原创 2020-07-04 00:19:59 · 360 阅读 · 0 评论 -
SQL Server查询表行数
方式一:(表大时较慢)select count(*) as rowsfrom table_name方式二:(数据库里本身存的信息,速度较快)SELECT a.name, b.rows,b.indidFROM sysobjects AS aINNER JOIN sysindexes AS bON a.id = b.idWHERE b.indid in (0,1)and a.name = 'table_name'ORDER BY a.name,b.rows DESC如查询表DM_FAC原创 2020-07-04 00:03:34 · 10414 阅读 · 1 评论 -
SQL Server插入数据的4种方式
总览5种方式分别为:INSERT VALUES:直接把值插入INSERT SELECT:将一个查询结果插入到另一个表SELECT INTO:非标准的T-SQL语句BULK INSERT:从外部读入文件插入1.INSERT VALUES这种插入数据的方式是最基础、最常见的方式。数据插入之前需要确保表是存在的,不然数据无法插入。IF OBJECT_ID('A_TEST','U') IS NOT NULL DROP TABLE A_TEST; -- 删表CREATE TABLE A_TEST原创 2020-07-03 23:58:32 · 25374 阅读 · 0 评论 -
sqlserver用户权限创建
首先创建一个测试数据库CREATE DATABASE tmp_db1. 创建登陆用户 create loginCREATE login [’{KaTeX parse error: Expected 'EOF', got '}' at position 8: 要创建的用户名}̲'] WITH passwor…密码}’, default_database= ‘{$数据库名}’USE DATABASE [tmp_db]CREATE login [readonly_tmp_db] WITH pas原创 2020-06-29 23:11:21 · 812 阅读 · 0 评论 -
SQL Server解析Json格式数据
主要介绍5个函数:openJson:打开Json字符串IsJson:判断一个字符串是不是合法的Json格式。是返回1,否返回0,null返回null。Json_Value:从Json字符串中提取值。Json_Query:Json字符串中提取对象或数组。Json_Modify:更新Json字符串中的属性值,并返回更新的Json字符串。# 开始之前:创建示例数据declare @json_str varchar(max)set @json_str='[ {"key":3,"value原创 2020-06-12 10:47:57 · 5900 阅读 · 1 评论 -
SQL Server常用日期计算
获取本周、本月、本季、本年的第一天和最后一天YTD|MTD|WTD|LMD|LWD计算DECLARE @today datetimeSET @today = GETDATE()-- TRAGET DATE-- [本年第一天,本年最后一天]SELECT 'YTD' AS CATEGORY_TIME ,@today AS CURRENT_DAY ,DATEADD(year, -1, @today) AS LAST_CURRENT_DAY -- 同期 ,DATEADD(ye.原创 2020-06-12 10:46:21 · 1295 阅读 · 1 评论 -
把SQL查询结果转为Json
1. 普通查询select * from test_table2. 转为Json方法:添加 FOR JSON PATH语句select * from test_tablefor json path[{ "iid": 1, "value": 2}, { "iid": 2, "value": 0}, { "iid": 1, "value": 2}, { "...原创 2020-02-24 18:58:53 · 4277 阅读 · 1 评论 -
SQL Server解析Json格式数据
主要介绍5个函数:openJson:打开Json字符串IsJson:判断一个字符串是不是合法的Json格式。是返回1,否返回0,null返回null。Json_Value:从Json字符串中提取值。Json_Query:Json字符串中提取对象或数组。Json_Modify:更新Json字符串中的属性值,并返回更新的Json字符串。# 开始之前:创建示例数据declare @...原创 2020-02-24 19:01:39 · 7688 阅读 · 1 评论 -
Python往SQL Server中插入数据
往SQL Server数据库中插入数据/执行SQL语句,遵循以下步骤:建立与数据库的连接,配置信息同上一节查询数据。插入数据时记得将数据表头与数据库表头进行对应。插入数据时记得将数据类型转为tuple,否则无法插入。执行完sql语句后记得commit,如遇失败记得rollbackimport os,sysimport pymssqlimport timeimport panda...原创 2020-02-13 16:58:16 · 7883 阅读 · 0 评论 -
Python从SQL Server中查询数据
该项目开发主要使用SQL Server数据库,但有时候需要用到Python与数据库交互:查询数据并导出、往数据库里插入数据等操作。以下脚本为从数据库里通过查询获取数据,进而导出或存储等操作。操作流程如下:首先通过ip,用户名、密码、要连接到的数据库名建立连接。这里还有一个charset参数需要格外说一下,我之前建立连接的时候并没有指定该参数,查询的结果也是没问题的。但有一次查询到的结果为乱码,...原创 2020-02-13 16:57:18 · 3255 阅读 · 0 评论