SQL Server

The three paradigms of SQL Server database Analysis: The database design should follow three main paradigms, respectively: First paradigm: Ensure the atomicity of each column in the table (non-detachable); Second paradigm: Make sure that each column

Stored Procedure format:CREATE PROCEDUREyourprocedure asBEGIN SETNOCOUNT on; BEGINTRY---------------------Start catching exceptionsBEINTRAN------------------Start a transaction UPDATEASETA.names=B.names fromT1 asAINNER JOINT2 asB

New data table:CREATE TABLE [dbo].[szsj_message_list]( [ID] [int] IDENTITY(1,1) not NULL, [name] [nvarchar]( +)NULL, [message] [nvarchar]( the) not NULL, [create_by] [nvarchar]( +) not NULL, [create_date] [datetime] not NULL,

Get the table for the databaseSELECTobj.name tablename,schem.name Schemname,CAST( Case when(SELECT COUNT(1) fromSys.indexesWHERE object_id=Obj.object_id andIs_primary_key=1)>=1 Then 1 ELSE 0 END as BIT) HasPrimaryKey

First, let's do the test data.1. Create test data--Create a People tableCreate TablePerson1 (Uidint Identity(1,1)Primary Key, Namevarchar( -) not NULL)--Create a People table 2--Create TablePerson2 (Uidint Identity(1,1)Primary Key, Namevarchar( -)

Use PUBDBGoIF object_id (N ' dbo. Regexreplace ') is not NULLDROP FUNCTION dbo. RegexreplaceGO--Start creating a regular replacement functionCREATE FUNCTION DBO. Regexreplace(@string VARCHAR (MAX),--the string to be replaced@pattern VARCHAR

Install the database to set the domain administrator as the database owner. Click SQL Server Configuration Manager to enable TCP/IP and named pipes in the SQL Native clinet 10.0 configuration (32-bit) \ Client protocol. The TCP/IP and named

Scalar functionsThe RETURNS clause specifies a scalar data type, the function is a scalar-valued function.Grammar| Schemabinding}] [as]beginsql statement (must have return variable or value) EndInterpretation: The WITH clause indicates the

SELECT db_name (er.[ DATABASE_ID]) [DatabaseName],Er. [Command] As [CommandType],Er. [Percent_complete], Er.start_time,CONVERT (DECIMAL (5, 2), er.[ Percent_complete]) as [complete_percent], CONVERT (DECIMAL (2), er.[ Total_elapsed_time]/60000.00)

--Create userCREATE LOGIN user name with password=n ' password ', default_database= database name, Check_expiration=off, Check_policy=offEXEC sp_grantdbaccess N ' user name '--AuthorizationGRANT SELECT on table name to user name--add "check"

SQL classification:DDL data Definition Language CREATE ALTER DROP DECLAREDML Data Manipulation Language SELECT DELETE UPDATE INSERTDCL Data Control Language GRANT REVOKE COMMIT ROLLBACKIndex creation and deletion note: The index is immutable and you

Select the table right-click on "Edit Top 200 rows" and select the upper-left corner of theSQL icon, then on the right side of the SQL statement to remove the top 200 and then execute the query to edit all the rows, you can choose to write SQL, and

A 233 error occurred with a colleague using SQL Server. 233 error occurs after you connect 1 databases using Microsoft SQL Server Management Studio and disconnect againViewing Windows log discovery is caused by a limit of maximum connections to ' 3 '

1. Sort all stored procedures by recent changesSELECT [name], [create_date], [modify_date] FROM [sys].[objects]WHERE [type] = ‘P‘ -- 这里也可以替换为：‘U‘,‘IF‘,‘TR‘分别代表用户表，自定义函数，触发器ORDER BY [modify_date] DESC2. Query the creation and modification time of the

Dry goods are as follows:exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' crs.***.com,6598 ' exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' Password '--exec sp_droplinkedsrvlogin ' ITSV ', null --Delete mappings

T-SQL language provides us with a more flexible way to manipulate data, that is, functions, functions are divided into three main categories: scalar function: (pass in a parameter, and then a parameter) aggregate function (pass in multiple

The stored procedure of the database is understood as, the subroutine that processes the data, writes like function, uses up like function, in SQL Server the stored procedure divides into two big classes, the system and the custom, the system all

1. Case statement Used to select statements　　　　 SELECTProductNumber, Category= CaseProductLine when 'R' Then 'Road' when 'M' Then 'Mountain' when 'T' Then 'Touring' when 'S' Then 'Other sale Items'

First, identify all the table names of a libraryuse database_namegoselect tab.name        as  Tablenamefrom   sys.columns col       inner join  sys.tables tab                on col.object_id = tab.object_id       left join

First, identify all the table names of a libraryuse database_namegoselect tab.name        as  Tablenamefrom   sys.columns col       inner join  sys.tables tab                on col.object_id = tab.object_id       left join