Two ways to automatically generate ordinal numbers with "SQL" statements

1. First of all, let's introduce the first approach : The SQL statement for the query is as follows: Select Row_number () over (order by name) as rowID, sysobjects. [Name] from sysobjects Results of the operation: rowid   name 1            all_columns 2            all_objects 3           all_parameters 4            all_sql_modules 5            all_views 2. Finally, let's introduce the second approach : When we generate automatic ordinals in this way, the test_table must not exist in the database because tables are created automatically when these SQL statements are executed. Select Id=identity (int,1,1), sysobjects. [name] as name into dbo. Test_table from sysobjects

Learn more about getting the number of days in the month

Useful tips for getting the number of days in the month: The following is a reference fragment: --Get days of the month analyzed as follows : select getdate ()  --Current date &NBSP; select Day (GETDATE ())--Current days   select getdate ()-day ( GETDATE ())   -Last day of last month select DateAdd (mm,1, GETDATE ())-day (GETDATE ())  --plus one months &NBSP; select Day (DateAdd (Mm,1,getdate ())-day ( GETDATE ())--Gets the number of days in the month 　　 The following is a reference fragment: <script language= "VBScript"; dim dt1, DT2 DT1 = Date DT1 = CDate ( Year (DT1) & "-" & Month (DT1) & "1") ' Get the first day of the month < Span style= "font-family: the song Body;" >DT2 = DateAdd ("M", 1, DT1) ' Get the first day of the month The following is a reference fragment: <script language= "JScript" > var dt = new Date (); Get current time DT = new Date (Dt.getfullyear (), Dt.getmonth () + 1, 0); Get the last day of the month Alert (Dt.getdate ()); The last day of the month is the number of days this month </script>

take a table before N article Records different SQL notation for each database

seen from Elsewhere, I am using the DB2, unexpectedly are not the same ... It seems to be impossible to say "SQL, all databases are used the same".

1. ORACLE
SELECT * from TABLE1 WHERE rownum<=n
2. INFORMIX
SELECT First N * from TABLE1
3. DB2

5
SELECT * Row_number () over (ORDER by COL1 DESC) as ROWNUM WHERE rownum<=N
DB2
SELECT COLUMN from TABLE FETCH first N ROWS only
4. SQL SERVER
SELECT TOP N * from TABLE1
5. SYBASE
SELECT TOP N * from TABLE1
6. mysql:
SELECT * FROM table_name limit N

Why SQL is not allowed to define an ORDER BY clause in a view

release time:2007.08.03 05:01     Source: Sadie Web Luoyingshu

Q: Why does SQL Server disallow the use of an ORDER BY clause in a view definition? Answer: SQL Server does not allow an ORDER BY clause to be used in a view definition in order to comply with the ANSI SQL-92 standard. Since the principle analysis of this standard requires a discussion of the underlying structure of the Structured Query Language (SQL) and the mathematical theory on which it is based, we cannot fully explain it here. However, if you need to specify an ORDER BY clause in the view, consider using the following methods: use pubs go   create VIEW Authorsbyname as from authors go The top structure introduced by Microsoft in SQL Server 7.0 is useful when used in conjunction with an ORDER BY clause. SQL Server supports the use of the ORDER BY clause in views only when used in conjunction with the top keyword. Note: The top keyword is an extension of SQL Server to the ANSI SQL-92 standard.

Why a SQL statement becomes so slow and how to solve it

release time:2008.01.30 04:58     Source: Sadie Web Zhao

phenomenon: A SQL suddenly runs very slowly. select Uidtable.column_ Value, first_name| | ' ' | | Last_Name, company, Job_title, Upper (Member_level), < Span style= "font-family: the song Body;" >upper (service_value) From (SELECT * FROM table (select cast (multiset (select B from BBB) as TAAA)) Uidtable,member < Span style= "font-family: the song Body;" >where Uidtable.column_value = member.login_id (+) and member.site= ' Alibaba ' and member.site= ' test '; Error Reason: The user added a condition member.site=test, resulting in a change in the order of the connection, the original driver table is uidtable (up to 1024 records), now becomes the member table to do the driver (600W). So the sentence becomes a huge slow one. But since it's an outer join, why does the order of connections change? Because the connection order of an outer join is not determined by cost, it is determined by the condition of the connection. Find the execution plan as follows: ------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------- | 0 | SELECT STATEMENT | | 1018 | 72278 | 8155 | | 1 | NESTED LOOPS | | 1018 | 72278 | 8155 | | 2 | VIEW | | 4072 | 69224 | 11 | | 3 | COLLECTION ITERATOR subquery fetch| | | | | | 4 | TABLE ACCESS Full | DUAL | 4072 | | 11 | | 5 | TABLE ACCESS Full | BBB | 41 | 287 | 2 | | 6 | TABLE ACCESS by INDEX ROWID | MEMBER | 1 | 54 | 2 | |* 7 | INDEX UNIQUE SCAN | MEMBER_SITE_LID_PK | 4 | | 1 | ------------------------------------------------- Why is there no external connection at all? The problem is in the member.site= ' test ' condition, because the external connection table adds the condition, causing the outer connection to fail. After changing to member.site (+) = ' Test ', the problem is resolved completely. --------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------- | 0 | SELECT STATEMENT | | 1018 | 72278 | 8155 | | 1 | NESTED LOOPS | | 1018 | 72278 | 8155 | | 2 | VIEW | | 4072 | 69224 | 11 | | 3 | COLLECTION ITERATOR subquery fetch| | | | | | 4 | TABLE ACCESS Full | DUAL | 4072 | | 11 | | 5 | TABLE ACCESS Full | BBB | 41 | 287 | 2 | | 6 | TABLE ACCESS by INDEX ROWID | MEMBER | 1 | 54 | 2 | |* 7 | INDEX UNIQUE SCAN | MEMBER_SITE_LID_PK | 4 | | 1 | -----------------------------------------------------------