SQL Server solution -- a summary of the uniqueness of object naming

About the uniqueness of object naming in SQL Server database. For example, database objects such as tables, indexes, constraints, etc. sometimes, DBA s often need to create or rename objects when doing database maintenance. At this time, they will encounter a problem, the uniqueness of object naming. Although it's a small question, it's not cle ...

Posted by EverToDesign on Thu, 20 Feb 2020 21:10:20 -0800

SqlServer uses cursors to update data in batches

Intro Cursors can be very useful sometimes. When updating a small part of data, they can easily update the data. There is no need to write another small tool to do this, just write SQL directly Sample Here is a practical example: -- Declare field variables DECLARE @RegionCode INT; DECLARE @RegionName NVARCHAR(64); DECLARE @ProvinceId INT; -- d ...

Posted by shock on Tue, 04 Feb 2020 10:02:03 -0800

SQL Server batch modify database table PK name to PK table name

1. When we create the primary key of the data table of SQL server, sometimes a string of random strings will be added, as shown in the figure:   2. If you have obsessive-compulsive disorder, you can use the following sql script to modify the name of the primary key to the PK UU table name --Unify the primary key name of the table as PK_T ...

Posted by ollie007 on Wed, 08 Jan 2020 07:55:49 -0800

Rollback in SQL Server

USE [TestDB] GO /****** Object: Table [dbo].[Person] script date: 11 / 23 / 2008 13:37:48******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [PersonId] [nchar](18) NOT NULL, [PersonName] [nchar](20) NOT NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [PersonId] ASC )WITH (PAD_INDEX = OFF, S ...

Posted by Lodar on Sun, 05 Jan 2020 05:05:58 -0800

Summary of SQL Server Setting max server memory incorrectly

Yesterday, a netizen on the Internet said that after his colleague mistakenly set "max server memory" to 10M, the SQL Server database could not be logged in. At that time, I simply tested it. Today, I will sort out the whole process and record it here.   Set "max server memory" in the UI of SSMS. Even if you set it to 10M, i ...

Posted by mwd2005 on Mon, 23 Dec 2019 19:43:03 -0800

Precise code formatting, fast style switching?All you need is SQL Prompt!

SQL Prompt not only formats the code exactly as you want, but also helps you quickly switch to other styles or apply exceptions to parts of SQL scripts that don't require specific styles. SQL Prompt automatically retrieves object names, syntax, and code snippets from the database to provide users with appropriate code choices.Automatic script ...

Posted by Confessions on Sat, 21 Dec 2019 11:17:52 -0800

Words randomly connect three phrases

In the technical forum, I saw a question like this: Now I have a table. There are 100 different words in it. Each word corresponds to about 20 phrases. I want to get 3 phrases randomly for each word through sql. How can I write it? I feel that the subject matter is very novel and the angle is very tricky. I haven't met similar needs in the in ...

Posted by johnsiilver on Mon, 09 Dec 2019 05:56:19 -0800

Date SQL Server obtains continuous interval

There are three ways of personal understanding Obtained from the system table Master.. SPT ﹣ values Get with WHILE loop Cursor acquisition   Method 1: obtain from the system table master..spt_values 1. Obtain consecutive days -- Get the date of continuous interval DECLARE @StartTime DATE = '2019-03-08', -- start time @EndT ...

Posted by techbinge on Mon, 02 Dec 2019 05:21:09 -0800

Get the list of days in the current month

To realize this function, please refer to the following articles< T-SQL get February days>https://www.cnblogs.com/insus/archive/2011/04/22/2025019.html <How to get the days of a month>https://www.cnblogs.com/insus/archive/2011/09/10/2173028.html <Gets the first day of the month in which the specified date is located>https:// ...

Posted by Tomatron on Thu, 14 Nov 2019 10:19:41 -0800

Record the troubleshooting process of SQL Server database subscription and publishing

Record the error reporting and solution process in the process of SQL server subscription and publishing Report errors: 20598 error reporting20598 error reporting is mainly caused by the data inconsistency between the databases at both ends. The database generates an error reporting prompt when applying the stored procedure The row was not f ...

Posted by mike_revolution on Tue, 12 Nov 2019 13:02:05 -0800