SQL Server (sub-query, stored procedure, perspective, Index)

Stored procedures:

A view is an encapsulation of a query statement, while a stored procedure: encapsulates a piece of T-SQL script and encapsulates a logical operation.

To create a stored procedure:
CREATE proc name parameter table as begin end

Case: Go to left and right spaces

creat proc Trim
@str1 varchar (10)
As
Begin

Select LTRIM (RTRIM (@str1))

End

In < programmability > There is a < stored procedure > There is a trim in the name of

When used:

EXEC trim ' parameters '

Case:
Write stored procedures, query the total number of tables in the table, and the data for the current page
Code for pagination

SELECT * FROM (SELECT *, ROWNUMBER () over (order BA Sid desc) as Rowidnex
From Studentinfo where isdelete=0) as T1 where rowindex between A and B

--Write the stored procedure, query the total data in the table, and the data of the current page.
EXEC proc Getpagelist
@pageIndex int,
@pageSize int,
@rowsCount int output//is equivalent to the function out keyword in C #. You can use multiple
As
Begin
SET NOCOUNT on; The stored procedure is executed without prompting a few lines to be impressed.

Select @rowsCount Count (*) from Studentinfo where isdelete=0

SELECT * FROM (SELECT *, ROWNUMBER () over (order BA Sid desc) as Rowidnex
From Studentinfo where isdelete=0) as T1 where rowindex between (@pageIdnex-1) * @pageSize +1 and @pageIndex * @pageSize

End

When called:
DECLARE @temp int

EXEC getpagelist @temp output

Print @temp

On the line.

Remember that the key to the stored procedure is the right word.

How do I call a stored procedure in. NET? Start calling just pagelist.

private int pageIndex;
Pageindex=1;
private void Loadlist ()
{
String sql= "getpagelist";//the name of the stored procedure is called directly
using (Sqlconnection conn=new Sqlconnection ("server=.; Database=dbtest;uid=sa;pwd=123 ")) {
SqlCommand cmd=new SqlCommand (sql,conn);
Specify command type as stored procedure
Cmd.commandtype=commandtype.storedprocedure;
As a stored procedure. To construct the parameters
SqlParameter pindex=new SqlParameter ("@pageIndex", PageIndex);
SqlParameter psize=new SqlParameter ("@pageSize", 3);
SqlParameter pcount=new SqlParameter ("@rowsCount", SqlDbType.Int); This is the parameter to be output.
Pcount.direction=parameterdirection.output; Sets the properties of this parameter.

Cmd. Parameters.add{pindex};
Cmd. Parameters.add{psize};cmd. Parameters.add{pcount};
Conn.Open ();
SqlDataReader Reader=cmd. ExecuteReader ();
Then you can format the value in reader.
The value of the parameter to be output is now in the parameter.
}
}

Index:

Classification:
Clustered index: As with the actual storage location, there can be only one clustered index in a table.
Nonclustered indexes: The index order is inconsistent with the storage order.
There can only be one clustered index (done on the primary key)
How to do index::
Interface actions: Right-click on the table-"design, column right-click Index, add-" select column. The prefix of the index name is Ix_ is the index of the column.
Code action: Create [unique]nonclustered index ix_ indexed name on table name (column name)
Indexing is done when there is more data. Index the column following the where.

Post-Union sqlserver-> Tool-"SQL Serverprofiler this thing runs after one weeks, there will be a lot of post-run statements, collect SELECT statements to see where the columns behind, and then can be indexed."

Subquery: Nesting a query in another query statement
Common operators: = Only one value can be matched in this range of exists and in, but efficiency in is to take the outside with the inside ratio, exists distinct to repeat, it is important.
Example: Check the information of the students taking the exam
SELECT * FROM Student
SELECT * FROM Scoreinfo
_____------

SELECT * from Studentinfo where stuid on (select distinct stuid from Scoreinfo to get all the IDs of the classmates who took the exam)

------
Sub-queries are often used in pagination

Over () the window function is generally used in two aspects, one is the aggregation function added to the column, one is a sort of time

Select *,row_number () over (order by Sid Desc) as RowIndex from Studentinfo
where RowIndex between 5 and 6//This will find that RowIndex is not available, because it will first execute the where sentence, so here is the first thing to make a result set of the Select, the correct wording as follows

Paging rule:
Start: (pageIndex-1) *pagesize+1
End: PageIndex * pageSize

SELECT * FROM
(select *,row_number () over (order by Sid Desc) as RowIndex from Studentinfo) as T1 where RowIndex between 5 and 8

Consolidation of existing data: (pivot)

Row variable column.

Select SName Name,
Max (case stitle when ' language ' then Scorevalue end) as language,
Min (case stitle when ' math ' then Scorevalue end) as mathematics,
SUM (case stitle when ' English ' then Scorevalue end) as English
From table name

The aggregate function here is repeated if not added, NULL, three numbers in the aggregation because the three numbers are not participating in the operation. Inside parentheses: Outputs the value of Scorevalue when the value of Stitle is ' language '.

Output each class of boys and girls:
Select Ctitle,
Mix (case Sgender if ' 1 ' then Count1 end else 0) boy,
MIN (case sgender if ' 0 ' then Count1 end else 0) schoolgirl
From table name GROUP by Ctitle
For example, knowing that every day's sales show the monthly sales form.

T-SQL programming.
SQL Server-specific encoding: keywords and rules are completely different from others.

Declaring variables: Declare variable name type-----variable name begins with @, and is parameterized in C #, and is required to start with @ is the variable in SQL Server here?
Setting: Set/select Variable name = value
Output: Print/select variable name
Global variables: Using the @@ 符号, there are two variables of the @@ 符号, usually the system built-in variables, for example, commonly used
INSERT into ClassInfo values ("New data") SELECT @ @INENTITY returns the identity ID of the data that was just inserted. @ @INENTITY called after inserting, returning the most-Pro identity value.
@ @error error, return the wrong number, no words return 0, single step execution
The print @ @rowcount returns the value that was affected by the last few rows.

Example:
declare @name nvarchar (10)
Set @name = ' This text '
Print @name

Begin try
End Try
Begin Catch
End Catch

SELECT statement:
Above, we have the choice of the case when querying the column. If you change the order of your entire code execution.

DECLARE @id int
Set @id =5
If @id >5
Begin
--code that satisfies the conditional execution
End
Else
Begin
--code that does not meet conditional execution
End

Cycle:

DECLARE @id int
Set @id =1
While @id <10
Begin

Print @id
Set @[email protected]+1
End

Output an even number from one to 10

DECLARE @num int
Set @num = 1;
While
Begin
If @num%2=0
Begin
Print @num
End
Set @num [Email protected]+1

End

Things: The ability to guarantee a large number of operations in a batch, if there is a failure, you can return to the original state. Ensure that the operation is correct.
Only if the data changes, it will trigger things, that is, to increase the deletion.
Classification:
Explicit transactions (manual write required)
Implicit transaction (MSSQL Default, no manual required)

Begin try
BEGIN Tran-Set the point of regret//This table is locked. The query is not found in the time.
SQL statements
Commit Tran commit does not return
End Tyr
Begin Catch
Rollback Tran back to the point of regret
End Catch
Back to the return point after the data are back to the return point, the equivalent of nothing has been deleted, nothing has changed.

Lock: Only one person can carry out this operation after locking, prevent the disorderly operation of many people.

There are foreign key table additions and deletions and no foreign key table is the same, but in order to ensure that the validity, it is generally referred to the table he refers to the column as a drop-down list format, let the user to choose.
Now for example, if you want to delete a piece of data, you must first remove the data from the table that references him. (usually prompted, such as you want to delete a class, tell you that there are students in the class, you have to delete the students first.) There is also a soft delete, that is, the change of a marker is 0)

(Pending update ...) ）

SQL Server (sub-query, stored procedure, perspective, Index)