sql server cheat sheet

Server Management Studio Manager 35 4.1 SQL Server Management Studio Introduction 35 4.1.1 Launch SQL Server Management Studio Tool 35 4.1.2 Connect Registered Servers and Object Explorer 36 4.1.3 Change Environment Layout 39 4.1.4 Set startup option 40 4.1.5 Query/Code Edi

Analyze, then select the appropriate databases and login options, such as: 　　 　　 4. Click Connect, then select the database to be analyzed, here we choose ADVENTUREWORKS2016CTP3 　　 　　5. Once the database is selected, we can run it for analysis. Such as: 　　 　　 6. After the operation is complete, the advisor will tell us the results of the analysis data sheet. Such as: 　　 , the red exclamation mark indicates that the table could not be migrated to Azu

PowerShell Excel imports SQL Server to update multiple table imports, which was slightly altered before.CLS$SqlConnection= New-object System.Data.SqlClient.SqlConnection#To SqlConnection a pair of elephants$SqlConnection. ConnectionString ="server=192.168.1.1;database=sa;user=sa;pwd=123" #The connection string indicating the SqlConnection image$SQLCMD= New-obj

columns of XML data types is more appropriate for data modeling. Some of the values in the XML data can be stored in a closed series, while the remaining values or all of the XML values are stored in an XML column. This can result in better performance and locking characteristics. For singleton values (that is, a single value property), the values in the XML data can be promoted to computed columns in the same table. A multivalued attribute requires a separate property

)), average of the from TB Group by name) nwhere M. Name = N. Name 'EXEC (@sql)--or use ISNULL ()[Email protected] (8000)[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course[Email protected]= ' Select m.*, N. Total score, N. Average from(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' +@sql + ')) b) m,(select name, sum (s

of the from TB Group by name) nwhere M. Name = N. Name 'EXEC (@sql)--or use ISNULL ()[Email protected] (8000)[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course[Email protected]= ' Select m.*, N. Total score, N. Average from(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' +@sql + ')) b) m,(select name, sum (score) Total

= N. Name 'EXEC (@sql)--or use ISNULL ()[Email protected] (8000)[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course[Email protected]= ' Select m.*, N. Total score, N. Average from(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' +@sql + ')) b) m,(select name, sum (score) Total, cast (avg (fractional *1.0) as decimal (18,

notTRUNCATE TABLE cannot be used for tables that have a FOREIGN key constraint reference.Change-UPDATE statement:Update table name set column name = update value [WHERE condition]WHERE clause does not update all dataFor example, add two points to Zhang San's score in the score table:Update score Table Set score = Score +2 where name = ' Zhang San 'Check select-query statement:1. Select Column name from table name where condition2. SELECT statement Parameters:into: Save query results to a new ta

1 002 percussion drill li Gang Hownie 8 2 003 forklifts Kwok one ai beautiful 2 cars 1 Customer Information Form Customer number Customer Name Affiliated Companies Contact information 1 Li Cong 51 Construction 13253661015 2 Liu Xinmin Self-employed 13285746958 In

= N. Name 'EXEC (@sql)--or use ISNULL ()[Email protected] (8000)[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course[Email protected]= ' Select m.*, N. Total score, N. Average from(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' +@sql + ')) b) m,(select name, sum (score) Total, cast (avg (fractional *1.0) as decimal (18,

1. Export SQL Server as Excel: To use T-SQL statements to export directly to the Excel worksheet, you have to borrow an extended stored procedure from the SQL Server Manager: xp_cmdshell, this process is used to execute a given command string in the form of an operating sys

execution time is increased from 14 seconds to 5 seconds, which indicates 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

SQL Server database and excel files Import DataSELECT * INTO excel from opendatasource ('Microsoft. JET. OLEDB.4.0 ','Excel 5.0; DATABASE = c: "book1.xls ') [Sheet1 $]Export dataInsert into opendatasource ('Microsoft. JET. OLEDB.4.0 ','Excel 5.0; DATABASE = c: "book1.xls ') [Sheet1 $] SELECT * FROM ExcelThe sheet 1 in book1.xls must have fields corresponding to

transformation of the workflow is: Customer calls to repair, call center records the user's basic information and customer description of the fault information, the production of maintenance orders, maintenance orders directly through the wireless network to repair service personnel on the handheld equipment, repair service personnel door-to-door for customer service, In the mobile device to fill out the maintenance record sheet, the customer signed

been changed to 10000--Below we re-insert a piece of data, the data information is numbered 8, the employee's name is Xiao Chen, gender is male, age is 25, salary is 7000, home address is Beijing Changping DistrictInsert into company staff information sheet (employee number, employee name, gender, employee age, employee salary, home address)values(8,' Xiao Chen ',' male ',7000,' Beijing Changping District ' )Post-insert results such as:--Let's delete

Source: http://blog.csdn.net/qygaojiansheng/archive/2009/04/26/4126364.aspx If the table already exists, the SQL statement is: Insert into AA select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = D: \ outdata.xls; extended properties = Excel 8.0 ')... [sheet1 $] Here, AA is the table name, and D: \ outdata.xls is the full path of Sheet 1 in Excel, which must be added with

foreign key. III. Management Data sheet of Enterprise Manager CREATE TABLE: Database, table, right-click New table, input column name, select data Type---save input table name. Modify Table: Database---table--right-click Design. Rename: Database, table->F2. Delete Table: Database---table--right-click Delete. Allow null---yes/No for column properties, NOT NULL constraints, and columns. The primary key--------right-------is completed with the main key.

Excel|server Basic ways to import/export Excel from SQL Server /*=================== Import/Export Excel basic methods ===================*/ From the Excel file, import the data into the SQL database, very simply, using the following statement directly: /*============================================================

nameEXEC sp_rename old view name, new view nameCases:Use Commodity Management databaseGoEXEC sp_rename V_ Goods _ Purchase success, V_ product Information _ Purchase Success2) Modify the contents of the ViewALTER VIEW name [WITH encryption] AS SELECT query Statement --alter View represents the modification of views, "view name" must be a view name that already exists. The other commands are the same as the CREATE view.Example: (Change the contents of the "Product Management database" named "V_

Label:Sometimes, in order for the application to run faster, all that is done is to make minor adjustments here or there. But the key is to determine how to adjust! Sooner or later you'll encounter this scenario: the SQL query in your application doesn't respond the way you want. It either doesn't return data, or it takes a surprisingly long time. If it lowers the speed of the enterprise application, the user must wait a long time. Users expect applic