How to generate and use CRUD stored procedures in SQL Server

Most database systems are based on four simplest data operations, which are called CRUD. The acronym stands for create, read, update, and delete. When developing and managing databases, CRUD stored procedures can be used to perform all data related tasks. The advantage of this stored procedure is that once it is written once, it can be reused ...

Posted on Wed, 11 Dec 2019 05:29:55 -0800 by bobthebullet990

SQLsever stored procedure paging query

Use stored procedures to implement paging query. The SQL statement is as follows: USE [DatebaseName] --Database name GO /****** Object: StoredProcedure [dbo].[Pagination] Script Date: 03/30/2019 10:36:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[Pagination] ( @SqlTable varchar(1000),--Table or vi ...

Posted on Sun, 08 Dec 2019 18:14:33 -0800 by delphi

SQL Server Query (sub query, full connection, equivalent connection, natural connection, left and right connection, intersection, union, difference set)

--Departmental table create table dept( deptno int primary key,--Department number dname nvarchar(30),--Department name loc nvarchar(30)--address ); --Employee list create table emp( empno int primary key,--Employee number ename nvarchar(30),--Employee name job nvarchar(30),--Employee work mrg int,--Employe ...

Posted on Fri, 06 Dec 2019 07:52:18 -0800 by kingsol

Generation and processing of SQL index fragment.

Reference in this article https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html https://www.jb51.net/softjc/126055.html https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 This article needs to“ Indexes ”And“ Stor ...

Posted on Thu, 28 Nov 2019 09:27:54 -0800 by gsv2com

UNPIVOT reverse perspective and dynamic reverse perspective stored procedure

I practiced PIVOT perspective the other day and realized the saving process of dynamic perspective< Dynamic PivotTable>https://www.cnblogs.com/insus/p/10888277.html Today, I will practice the function of MS SQL Server reverse perspective. First, prepare some data that can be inverted perspective:   IF OBJECT_ID('tempdb.dbo.#Part_ ...

Posted on Thu, 07 Nov 2019 08:13:37 -0800 by Petrushka

Reverse engineering of springboot + mybats has the process of generating domain from database

Environment: jdk1.8 (for springboot2.X and above) Maven (above 3.3. X)           spring boot 2.1.6           Idea 2019.1\              Fill it in here      Select the corresponding Jar, as shown below   The following is the most important operation. In pom.xml, import the jar required by mybats reverse engineering.   Co ...

Posted on Sun, 03 Nov 2019 01:45:46 -0700 by stone.cold.steve.austin

Timely Email Warning and Reminder for Blockage in Database (Part 2)

stay Timely Email Warning and Reminding of Blocking in Database (Part I) We build a new table to store blocked records, collect blocked stored procedures and configure sending mail to test ok, etc. Step 4: Create a storage process for sending mail. There are two modes that you can choose according to your needs Create a stored procedure for sen ...

Posted on Wed, 03 Jul 2019 15:53:38 -0700 by ksb24930

Who exactly is synchronized locking?

This article code repository: https://github.com/yu-linfeng/BlogRepositories/tree/master/repositories/sync Start with an enrollment-level concurrent programming pen test Title: Use 5 threads to execute concurrently, num digits count up to 10000, and print. 1 /** 2 * Description: 3 * Using five threads to execute concurrently, num numb ...

Posted on Thu, 13 Jun 2019 10:00:16 -0700 by popsiclesph

Descending Indexes for SQL Server

  Test environment: SQL Server 2012   The table structure is as follows USE [test] GO CREATE TABLE [dbo].[tt8]( [id] INT IDENTITY(1,1) NOT NULL, [win_num] [int] NOT NULL DEFAULT ((0)), [lost_num] [int] NOT NULL DEFAULT ((0)), [draw_num] [int] NOT NULL DEFAULT ((0)), [offline_num] [int] NOT NULL DEFAULT ...

Posted on Tue, 30 Apr 2019 15:30:37 -0700 by kaimason1

Summary of SQL Server Database Files and File Groups

File and File Group Concept   For documents and file groups, a brief summary is as follows. For details, please refer to the official documents.“ Database Files and Filegroups ” More relevant information:   Data file concept:   Each SQL Server database contains at least two system files: a data file and a log file. Data files contai ...

Posted on Wed, 24 Apr 2019 20:27:36 -0700 by driverdave