There is also a difference in getting the current date and time. In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x). Most relational database systems deliver many functions to operate on dates and times. The SQL standard doesn't allows for the use of DISTINCT in these functions.īut in T-SQL we don't find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard. T-SQL allows the use of DISTINCT before these argument values, so that rows are counted only if the values are different from other rows. The functions COUNT, SUM, and AVG all take an argument related to a count. We find another syntax difference with the aggregate functions. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL you use the GREATEST(list) and LEAST(list) functions. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. One of these math functions is CEIL(x), which we don't find in T-SQL. Several common mathematical functions are part of the SQL standard. In T-SQL we can also automatically generate values, but in this way: The syntax to do this is shown below:ĬREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY) The SQL standard enables you to create columns with automatically generated values. Another ways to select rows, but without ORDER BY is by using the TOP clause in T-SQL:
SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY Īs you notice, this uses an ORDER BY clause. The example below shows the MS SQL Server syntax: T-SQL implements this syntax in a different way. SELECT * FROM tab FETCH FIRST 10 ROWS ONLY In the SQL standard you can limit the number of records in the results by using the syntax illustrated below: In this expression we don't need a table to evaluate 12 divided by 6, therefore the FROM statement and the name of the table can be omitted. In T-SQL it looks like the example below: How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. What is different in a SELECT statement? #2 Returning values Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard. Look at these examples for the name of a table in T-SQL:ĬREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100)) ĬREATE TABLE dbo.test. In standard SQL you can place this kind of name in quotation marks (""), but in T-SQL you can also place it in brackets (). In relational database systems we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In this article you will find examples of some of the differences in syntax between standard SQL and Transact-SQL. However, in SQL Server you will find almost all the features of the SQL standard. If you think T-SQL is an extension implementing all the features from standard SQL, you aren't right. Now I'd like to focus on the syntax differences and illustrate these differences with examples. In my last article, I roughly described how standard SQL differs from T-SQL and who should learn which.
0 kommentar(er)
