Features of SQL Server 2012

1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

2. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

3. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

7. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

8. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

9. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

10. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

11. SQL Azure Enhancements -- These don't really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

12. Big Data Support -- I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

Shortcuts in SQL Query Analyzer

A list of Keyboard Shortcuts in SQL Query Analyzer:

Key	Description	Key	Description
CTRL-SHIFT-F2	Clear all bookmarks.	CTRL+F2	Insert or remove a bookmark (toggle).
F2	Move to next bookmark.	SHIFT+F2	Move to previous bookmark.
ALT+BREAK	Cancel a query.	CTRL+O	Connect.
CTRL+F4	Disconnect.	CTRL+F4	Disconnect and close child window.
ALT+F1	Database object information.	CTRL+SHIFT+DEL	Clear the active Editor pane.
CTRL+SHIFT+C	Comment out code.	CTRL+C or Ctrl+Insert	Copy
CTRL+X or Shift+Del	Cut	SHIFT+TAB	Decrease indent.
CTRL+DEL	Delete through the end of a line in the Editor pane.	CTRL+F	Find.
CTRL+G	Go to a line number.	TAB	Increase indent.
CTRL+SHIFT+L	Make selection lowercase.	CTRL+SHIFT+U	Make selection uppercase.
CTRL+V or Shift+Insert	Paste.	CTRL+SHIFT+R	Remove comments.
F3	Repeat last search or find next.	CTRL+H	Replace.
CTRL+A	Select all.	CTRL+Z	Undo.
F5 or Ctrl + E	Execute a query.	F1	Help for Query Analyzer.
SHIFT+F1	Help for the selected Transact-SQL statement.	F6	Switch between query and result panes.
Shift+F6	Switch panes.	CTRL+W	Window Selector.
CTRL+N	New Query window.	F8	Object Browser (show/hide).
F4	Object Search.	CTRL+F5	Parse the query and check syntax.
CTRL+P	Print	CTRL+D	Display results in grid format.
CTRL+T	Display results in text format.	CTRL+B	Move the splitter.
CTRL+SHIFT+F	Save results to file.	CTRL+R	Show Results pane (toggle).
CTRL+SHIFT+M	Replace template parameters.	CTRL+L	Display estimated execution plan.
CTRL+K	Display execution plan (toggle ON/OFF).	CTRL+I	Index Tuning Wizard.
CTRL+SHIFT+S	Show client statistics	CTRL+SHIFT+T	Show server trace.
CTRL+U	Usedatabase

Date Format In Sql Server

The list of available styles are given below:

Style	Format	Example
1	MM/dd/yy	04/25/09
2	yy.MM.dd	09.04.25
3	dd/MM/yy	25/04/09
4	dd.MM.yy	25.04.09
5	dd-MM-yy	25-04-09
6	dd MMM yy	25 Apr 09
7	MMM dd, yy	Apr 25, 09
10	MM-dd-yy	04-25-09
11	yy/MM/dd	09/04/25
12	yyMMdd	090425
100 or 0	MMM dd yyyy hh:miAM (or PM)	Apr 25 2009 1:10PM
101	MM/dd/yyyy	04/25/2009
102	yyyy.MM.dd	2009.04.25
103	dd/MM/yyyy	25/04/2009
104	dd.MM.yyyy	25.04.2009
105	dd-MM-yyyy	25-04-2009
106	dd MMM yyyy	25 Apr 2009
107	MMM dd, yyyy	Apr 25, 2009
108	hh:mm:ss	13:12:22
109 or 9	MMM dd yyyy hh:mi:ss:mmmAM (or PM)	Apr 25 2009 1:12:40:263PM
110	MM-dd-yyyy	04-25-2009
111	yyyy/MM/dd	2009/04/25
112	yyyyMMdd	20090425
113 or 13	dd MMM yyyy hh:mm:ss:mmm(24h)	25 Apr 2009 13:13:30:983
114	hh:mi:ss:mmm(24h)	13:13:42:200

O

SQL String Functions

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :


Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53


Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial')
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20)
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] )
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT('TravelYourself', 6)
-- Value = Travel
SELECT LEFT('BeautyCentury',6)
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself
SELECT RIGHT('BeautyCentury',6)
-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE('Sql', 2)
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3)
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer')
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE('SqlServer')
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer')
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER('sqlserver')
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

SQL Server DataType

SQL Server DataType

All Sql DataType

bigint
Integer data from -2^63 through 2^63-1

binary
Fixed-length binary data with a maximum length of 8,000 bytes

bit
Integer data with either a 1 or 0 value

char
Fixed-length character data with a maximum length of 8,000 characters

datetime
Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds

decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

float
Floating precision number data from -1.79E + 308 through 1.79E + 308

image
Variable-length binary data with a maximum length of 2^31 - 1 bytes

int
Integer data from -2^31 through 2^31 - 1

money
Monetary data values from -2^63 through 2^63 - 1

nchar
Fixed-length Unicode data with a maximum length of 4,000 characters

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 characters

nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters

numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

real
Floating precision number data from -3.40E + 38 through 3.40E + 38

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute

smallint
Integer data from -2^15 through 2^15 - 1

smallmoney
Monetary data values from -214,748.3648 through +214,748.3647

sysname

text
Variable-length data with a maximum length of 2^31 - 1 characters

timestamp

tinyint
Integer data from 0 through 255

varbinary
Variable-length binary data with a maximum length of 8,000 bytes

varchar
Variable-length data with a maximum of 8,000 characters

uniqueidentifier
A globally unique identifier

SQL Server Alternate of Count()

SQL Alternative Count

Both SQL Query below returns same result but the speed of retrieving data are different.
Copy Below Both Sql Query to Query analyzer to test it. Customers is a Table Name.

1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Customers') AND indid <>
You can use sysindexes system table to get number of ROWS. This column contains the total row count for each table in your database. So, you can use the following select statement instead of Below one:


2. SELECT Count(*) FROM Customers
This SQL query performs full table scan to get the row count.


So, you can improve the speed of the SQL query using the Above First SQL instead of Second SQL Query

What's New in SQL Server 2008?

What's New in SQL Server 2008?

Tuesday December 4, 2007

In a recent interview, SQL Server MVP Brad McGehee offered some observations about Microsoft's pending release of SQL Server 2008. His thoughts, in a nutshell, were:

• SQL Server continues to get more complex and many DBAs are behind the curve because they haven't yet upgraded to SQL Server 2005
• PowerShell, included in SQL Server 2008, is unlikely to see much adoption among DBAs
• The rewritten Reporting Services and Analysis Services are scalable and don't require IIS
• The Declarative Management Framework (DMF) is the big selling point for 2008, offering consistent enterprise management

Simple Trigger

Simple Trigger

Simple trigger that Delete records from particular table.

Situation : There is one field type is single/joint if user select joint one field enter joint account name and if single then that fields disapears so now if user change joint type to single then joint account name is deleted form database using this trigger.

Answer :

CREATE TRIGGER Tr_Name_UpdateRecordTable
On Table_name
FOR UPDATE,insert AS
declare @Id int
declare @Type int

select @Id=Id from inserted
select @Type=Type from inserted

// @Type=1 mean single else joint

IF @Type=1

BEGIN

Update Table_name set JointAccountFirstName='' ,JointAccountMiddleName='' ,
JointAccountLastName='' where Id=@Id

END

Useful Sql Querys

Useful Sql Querys

Print Row number in Sqlserver 2005

SELECT (ROW_NUMBER() OVER (ORDER BY field_name) )as RowNumber,
field1, field2,fieldn FROM tablename

Get random record from database

SELECT TOP 1 field1, field2,fieldn FROM tablename ORDER BY NEWID()

Find Recenty executed Query in SQLSERVER 2005

SELECT

DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC


Taking Multiple backups in SQL SERVER 2005

BACKUP DATABASE Northwind
TO DISK = 'D:\DataabseBkps1\Northwind.bak'
MIRROR
TO DISK = 'E:\DataabseBkps2\Northwind.bak'
WITH FORMAT;

• consistent enterprise management

SQL Server Combine 2 Different Table

SQL Server : Combine 2 Different Table

This SQL is use to combine 2 different table together for other purpose.
SELECT ISNULL(a.Col1,b.Cola) , ISNULL(a.Col2,b.Colb)
FROM Table1 a
FULL JOIN Table2 b
ON a.Col1 = b.Cola

Here is 2 Different Table,
Table Name : Salary

Name	Salary
emil	1000
rayden	2000

Table Name: Flight

FligtTicket	Price
Kuala Lumpur	265
Bangkok	878

Below Sql is use to combine above 2 Tables Become below 1 Table
SELECT ISNULL(a.Name,b.FlightTicket) AS Col1, ISNULL(a.Salary,b.Price) AS Col2 FROM Salary a
FULL JOIN Flight b ON a.Name = b.FlightTicket

Col1	Col2
emil	1000
rayden	2000
Kuala Lumpur	265
Bangkok	878

SQL DATE Function

SQL DATENAME Function

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond.

SQL DATENAME Syntax
DATENAME ( datepart , date )

Example

SELECT GETDATE() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE())
SELECT DATENAME(yy, GETDATE())
SELECT DATENAME(yy, GETDATE())
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE())
SELECT DATENAME(qq, GETDATE())
SELECT DATENAME(q, GETDATE())
-It will return value = 2 (because 1 quarter equal to 3 month,Detail see below table)

Month	Quarter Value
January - March	1
April - June	2
July - September	3
October - December	4

SELECT DATENAME(month, GETDATE())
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE())
- It will return value = May

SELECT DATENAME(dayofyear, GETDATE())
SELECT DATENAME(dy, GETDATE())
SELECT DATENAME(y, GETDATE())
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE())
SELECT DATENAME(dd, GETDATE())
SELECT DATENAME(d, GETDATE())
- It will return value = 30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)

SQL Optimization Tips

SQL Optimization Tips

• Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to
server only stored procedure or view name (perhaps with some
parameters) instead of large heavy-duty queries text. This can be used
to facilitate permission management also, because you can restrict
user access to table columns they should not see.

• Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost
performance. So, you should use constraints instead of triggers,
whenever possible.

• Use table variables instead of temporary tables.
Table variables require less locking and logging resources than
temporary tables, so table variables should be used whenever possible.
The table variables are available in SQL Server 2000 only.

• Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL
statement does not look for duplicate rows, and UNION statement does
look for duplicate rows, whether or not they exist.

• Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance
degradation, you should use this clause only when it is necessary.

• Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in
comparison with select statements. Try to use correlated sub-query or
derived tables, if you need to perform row-by-row operations.

• Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the
GROUP BY clause. When you use GROUP BY with the HAVING clause, the
GROUP BY clause divides the rows into sets of grouped rows and
aggregates their values, and then the HAVING clause eliminates
undesired aggregated groups. In many cases, you can write your select
statement so, that it will contain only WHERE and GROUP BY clauses
without HAVING clause. This can improve the performance of your query.

• If you need to return the total table's row count, you can use
alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the
total table's row count, it can take very many time for the large
table. There is another way to determine the total row count in a
table. You can use sysindexes system table, in this case. There is
ROWS column in the sysindexes table. This column contains the total
row count for each table in your database. So, you can use the
following select statement instead of SELECT COUNT(*): SELECT rows
FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,
you can improve the speed of such queries in several times.

• Include SET NOCOUNT ON statement into your stored procedures to stop
the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive
the message indicating the number of rows affected by a T-SQL statement.

• Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will
return to client only particular rows, not all rows from the table(s).
This can reduce network traffic and boost the overall performance of
the query.

• Use the select statements with TOP keyword or the SET ROWCOUNT
statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller
result set will be returned. This can also reduce the traffic between
the server and the clients.

• Try to restrict the queries result set by returning only the
particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will
return to client only particular columns, not all table's columns.
This can reduce network traffic and boost the overall performance of
the query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization


Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES
and DELETES, so the number of indexes should not be very much. Try to
use maximum 4-5 indexes on one table, not more. If you have read-only
table, then the number of indexes may be increased.

• Keep your indexes as narrow as possible. This reduces the size of
the index and reduces the number of reads required to read the index.

• Try to create indexes on columns that have integer values rather
than character values.

• If you create a composite (multi-column) index, the order of the
columns in the key are very important. Try to order the columns in the
key as to enhance selectivity, with the most selective columns to the
leftmost of the key.

• If you want to join several tables, try to create surrogate integer
keys for this purpose and create indexes on their columns.

• Create surrogate integer primary key (identity for example) if your
table will not have many insert operations.

• Clustered indexes are more preferable than nonclustered, if you need
to select by a range of values or you need to sort results set with
GROUP BY or ORDER BY.

• If your application will be performing the same query over and over
on the same table, consider creating a covering index on the table.

• You can use the SQL Server Profiler Create Trace Wizard with
"Identify Scans of Large Tables" trace to determine which tables in
your database may need indexes. This trace will show which tables are
being scanned by queries instead of using an index.

• You can use sp_MSforeachtable undocumented stored procedure to
rebuild all indexes in your database. Try to schedule it to execute
during CPU idle time and slow production periods.
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Functions used in sql server

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Numeric Functions

Numeric functions accept numeric input and return numeric values. Most numeric functions that return NUMBER values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The numeric functions are:

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

Character Functions Returning Character Values

Character functions that return character values return values of the same datatype as the input argument. The length of the value returned by the function is limited by the maximum length of the datatype returned.

• For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.

• For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.

The character functions that return character values are:

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

Character Functions Returning Number Values

Character functions that return number values can take as their argument any character datatype.

The character functions that return number values are:

ASCII
INSTR
LENGTH
REGEXP_INSTR

Datetime Functions

Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.

The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.

The datetime functions are:

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

Collection Functions

The collection functions operate on nested tables and varrays. The SQL collection functions are:

CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET

Miscellaneous Single-Row Functions

The following single-row functions do not fall into any of the other single-row function categories:

BFILENAME
COALESCE
CV
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
GREATEST
LEAST
LNNVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
ORA_HASH
PATH
PRESENTNNV
PRESENTV
PREVIOUS
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

See Also: "Using the GROUP BY Clause: Examples" and the "HAVING Clause " for more information on the GROUP BY clause and HAVING clauses in queries and subqueries

Many (but not all) aggregate functions that take a single argument accept these clauses:

• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.

• ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr:

SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

AVG(MAX(SALARY))
----------------
          10925

This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause (department_id), and aggregates the results again.

The aggregate functions are:

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

HOW TO CHANGE DATE FORMAT OF CRYSTAL REPORT FIELD FROM ASP.NET

SELECT Lower(CONVERT(VARCHAR,GETDATE(),100)) -- May 23 2007 6:00PM

SELECT CONVERT(VARCHAR,GETDATE(),101) -- 05/23/2007

SELECT CONVERT(VARCHAR,GETDATE(),102) -- 2007.05.23

SELECT CONVERT(VARCHAR,GETDATE(),103) -- 23/05/2007

SELECT CONVERT(VARCHAR,GETDATE(),104) -- 23.05.2007

SELECT CONVERT(VARCHAR,GETDATE(),105) -- 23-05-2007

SELECT CONVERT(VARCHAR,GETDATE(),106) -- 23 May 2007

SELECT CONVERT(VARCHAR,GETDATE(),107) -- May 23, 2007

SELECT CONVERT(VARCHAR,GETDATE(),108) -- 18:02:03

SELECT CONVERT(VARCHAR,GETDATE(),109) -- May 23 2007 6:02:28:177PM

SELECT CONVERT(VARCHAR,GETDATE(),110) -- 05-23-2007

SELECT CONVERT(VARCHAR,GETDATE(),111) -- 2007/05/23

SELECT CONVERT(VARCHAR,GETDATE(),112) -- 20070523

SELECT CONVERT(VARCHAR,GETDATE(),113) -- 23 May 2007 18:02:51:850

SELECT CONVERT(VARCHAR,GETDATE(),114) -- 18:02:57:083

SELECT CONVERT(VARCHAR,GETutcDATE()),114) -- 18:02:57:083

SELECT CONVERT(VARCHAR,GETDATE()),114) -- 18:02:57:083

SELECT DATEPART(HOUR,CONVERT(VARCHAR,HOUR(GETDATE()),109)) -- 18:02:57:083