sql server cheat sheet

Delete data columnsThe data columns that are built, wrong, or duplicated in the development or production process need to be deleted.Use the SSMS database management tool to delete data column mode one1. Open database--Select data Table-"Expand Data Sheet-" Expand Data Column-"Select the data column to delete-" Right click-"SELECT Delete-" in the Pop-up box click OK.Way Two1. Open Database-"Open data Sheet-

, [whether Night shift]nchar (1) not NULL, [date]date not NULL, [Day of work]float (1) NOT null ) / * Above is to tell the computer you want a basic information column/ as BEGIN INSERT INTO @TEMP_TABLE/* This means inserting the following query results into the @temp_table variable. SELECT y. Name, Y. Work, Y. Position, Y. department, K. Night Shift, K. Date, K. Working hours of the day From[t_ Employee Info] as Y CROSS JOIN [t_ attendance] as K/ * Here I am using a cross connection/ where Y.

Tags: ar sp data problem BS database SQL nbsp userFirst build a test sheet: CREATE table teststring (name nvarchar (100));INSERT INTO teststringValues(' Zhang San, John Doe, Harry, Caifan, Xiao Rambler, Royal Palace '); 1. Determine the number of occurrences of a character (string) in a string, the position where the first occurrence occurs last: View ', ' The number of occurrences Select LEN (name)-len (RE

: increments of several numbers at a time wildcard characters:_: There is only one character% any of any charactersLike ' (used to modify)Having said so much, we should pay attention to some common problems;01. Foreign Key control is not good02. In SQL Server, string and date type data are enclosed in single quotes, and numeric types are not03. Each table must have a primary key, the table without the prima

In SQL Server, many people concatenate SQL statements by repeating the Excel data read by oledb. This is not only error-prone but also inefficient. The best way is to use BCP, that is, system. data. sqlclient. sqlbulkcopy class. Not only fast, but alsoCodeSimple: The following test code imports a sheet with more than 6

! (just forget, will not go back to change) Note here that the first 8 columns get the initial byte substring (@bitmap, 1, 1), each column corresponds to a bit, 5th column 00010000, 16. Notice to change here. (More references in-depth understanding of columns_updated functions in SQL Server 2005) Publish Object Changes complete! ~ Now to test, in the publishing library and the subscription library to pe

where UserName like ' Zhang [0-9] sister '/' Zhang [A-z] sister '/' Zhang [0-9a-z] sister '; [A-z] The default collation is case-insensitive, so the case can be written out ^ (non): select * from Users where UserName like ' Zhang [^0-9] sister ' (middle not number)/not like ' Zhang [0-9] sister ' (do not take ' Zhang [0-9] sister ' data); Note: 14.null Value Processing ①sql null cannot be calculated with =/!=; ②is null/is NOT NULL; ③ any value with

Server|xml [Author Introduction:]wayne, a new generation of programmers, 12-Year-old began to learn programming, 13-year-old with his own computer, has learned basic, Pascal, C, FOXPRO, VB, DELPHI, + +, SQL, learning the Java language, then become a crazy fan of Java , he is now studying at China University of Science and Technology. IntroductionContent navigation　　* Introduction* Configure IIS virtual dire

based on the database that was created in the previous article, we continue to create data tables. T-the syntax for SQL database creation is as follows:CREATE TABLETable name (field name 1 data type constraint, field name 1 data type constraint,...) The first is to create the Learner information table Stuinfo, here are some small details:IDENTITY(Start value, increment) is listed as the identity column, NUMERIC ( -,0) represents a 18-digit number, wi

First, write it in front.A lot of days did not record SQL learning notes, to persist, firmly believe that every point of progress is to accumulate strength. See a picture today and share it.Through this picture, I see each person standing in their own perspective, feeling is not the same, like learning knowledge, always feel that their understanding is the most unique, sometimes appropriate to share things outTo listen to other people's opinions may m

the day of the year,DW represents the day of the week and returns an integer default of 1 for SundayGETDATE No parametersSelect GETDATE ()Back to 2009-04-28 18:57:24.153Returns the current system date and time.DayDateSelect Day (' 2007-12-11 ') returns 11Returns an Integer that represents the part of the day of the specified date.Equivalent toDATEPART (dd, date)MonthDateSelect month (' 2007-12-11 ') returns 12Returns an Integer that represents the part of the month of the specified date.Equival

attribute edit button of the expression to drag the variable User: xlspath in the list to the following expression box. 19. SSIS will report an error and give a warning. 20. On the Control Flow page, right-click a Data Flow task and choose properties. In the Control Flow Properties window, set delayvalidation to true. Run:After the above 20 steps of configuration, the entire process is over. You can run the package to check the effect. All the Excel files in the folder will be imported into th

Tags: data sheet test sharing doc Source log Success Database toolThis is a simple database document generation tool, the main implementation of SQL Server generation instructions document of the gadget, is not perfect, the main can be the database table and table detailed field information, export toIn Word, you can make it easier for developers to learn about d

Tags: var ref pre IMA Define PRIMARY KEY constraint server varchar span1. Create a customer sheet 1 IF object_id('dbo. Employees','U') is not NULL 2 DROP TABLEdbo. Employees; 3 4 CREATE TABLEdbo. Employees5 ( 6EmpidINT not NULL, 7FirstNameVARCHAR( -) not NULL, 8LastNameVARCHAR( -) not NULL, 9HireDate DATE not NULL, TenMgridINT NULL, OneSsnVARCHAR( -) not NULL, ASalary Money not

table, so it is certainly not possibleDECLAREDECLATR @ variable name data typeThe default value of the variable is NULLAssign valueSET @ variable name = valueSELECT @ variable name = valueTake valuePRINT @ Variable nameGlobal variables@ @ERROR Record the last error number that occurred@ @IDENTITI The last marked value inserted@ @MAX_CONNECTIONS The number of connection that can be created at the same time per server is limited@ @ROWCOUNT The number o

that row_number paging is more efficient, and that this is top top a lot more elegant than pagination.The "cheat" query engine lets queries query as you wishBut why is it 5 seconds to query 20 records, especially if the table is a time index--refer to the index mentioned in "How the program ape solves SQL Server's cpu100%". I tried to get rid AND b.AddrId in (‘02109000‘,……,‘02109002‘) of this sentence, the

Below to share the next SQL Server Building, building tables, building constraints skills, the following text has code. --Before creating a school database: First, determine if the database exists, delete it if it exists, and create it if it does not exist---exists keyword: The parentheses inside can query to the data to return ' true ' otherwise return ' false ' if exists (SELECT * from sysdatabas

number of rows affected is 5 rows)Result Description:The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).The low-record of table B is null. -----------Cycle Table DECLARE @id intDECLARE @maxid intSet @id =3Select @maxid =max (ID) from tabl

In the design of the database, often do not take into account the problem of table partitioning, often in the burden of the data table load is getting heavier, will take into account the partitioning method, at this time, it is related to how to convert the ordinary table to the problem of the Component table.So, how do you convert a normal table into a partitioned table? In the final analysis, simply create a clustered index on the table and use the partitioning scheme on the clustered index.Ho

I recently conducted a small integration application test, which is suitable for users who do not want to view the relevant reports on the website but want to view the reports by email at regular intervals.Relatively static reports(No user interaction required), You can use an integrated operation method described in this article. The main implementation ideas are as follows: 1. First, you can use the Excel Service Web Service of Sharepoint to automatically obtain and download the charts of th