SQL Server XML Data

SQL Server > XML Data

XML support is integrated into all the components in SQL Server.

• FOR XML clause

SOUNDEX SQL Server Example

SQL Server > Built-in Functions > SOUNDEX

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken. 

Example

SELECT SOUNDEX ('Jan'), SOUNDEX ('Jane');

Result:

(No column name) (No column name)

J500                 J500

TRY_CAST SQL Server Example

SQL Server > Built-in Functions > TRY_CAST

Try to cast a value to the specified data type. 
If the cast not succeeds returns null.

Example

SELECT

    CASE WHEN TRY_CAST('1' AS float) IS NULL

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

Result

Cast succeeded

SELECT

    CASE WHEN TRY_CAST('xx' AS float) IS NULL

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

Result

Cast failed

INSERT SQL Server Example

SQL Server > DML > INSERT

INSERT adds one or more rows to a table or a view.

• When insert data into a view, the view must reference exactly one base table in the FROM clause of the view. 
• When insert into a multi-table view you must use a column list that references only columns from one base table. 

Example

INSERT INTO Sales (ProductName, Price, [Date])
VALUES ('Product1', 200, GETDATE());

System tables SQL Server

SQL Server > System tables

• sysobjects

Subtract SQL Server example

SQL Server > Operators > Subtract

- Operator (minus) subtracts two numbers or a number (in days) from date.





Example

1. Subtracts values from 2 numeric columns

create table #test(value int, fee int)
insert into #test(value, fee) values (500,50),(100,10)
  select 
   value - fee as 'diff'
  from 
   #test
drop table #test

Result
diff
450
90

2. Subtracts from date

SELECT getdate() - 1 AS 'Subtract Date';

Calculate SQL Server row size for a table

SQL Server > Scripts > Row Size

create table ##RowSize (tableName nvarchar(50), rowSize int)

exec sp_msforeachtable 'INSERT INTO ##RowSize Select ''?'' As tableName, SUM(c.Length) from dbo.SysColumns c where c.id = object_id(''?'') '

select * from ##RowSize order by rowSize  desc

drop table ##RowSize

Remove database SQL Server with sp_dbremove

SQL Server > System Stored Procedures > sp_dbremove

Removes a database.

Example

EXEC sp_dbremove HR;

STR SQL Server Example

SQL Server > Built-in Functions > STR

Converts number to varchar.

STR ( expression , length , decimal )

Example:

SELECT STR(123.45, 6, 1);

GO

result
 123.5

Transact-SQL SQL Server

SQL Server > Transact-SQL

Transact-SQL is Microsoft's proprietary extension to SQL (Structured Query Language).
All applications that communicate with SQL Server use Transact-SQL to send statements to the server, regardless of the user interface of the application.

SET Statements

• NOCOUNT
• XACT_ABORT

Mnagements Commands

• Kill

PARSE SQL Server Example

SQL Server > Built-in Functions > PARSE

Translates an expression to the requested data type.

Example

SELECT PARSE('3-11-2014' AS datetime USING 'en-US') AS Result_En

Result_En

2014-03-11 00:00:00.000

SELECT PARSE('3-11-2014' AS datetime USING 'de-DE') AS Result_De

Result_De

2014-11-03 00:00:00.000

CONVERT SQL Server Example

SQL Server > Built-in Functions > CONVERT

CONVERT converts an expression of one data type to another.

Example

select CONVERT(nvarchar(30), GETDATE(), 100)

result

Oct 15 2014  9:54AM

Other examples:

How to format date and time

APP_NAME SQL Server Example

SQL Server > Built-in Functions > APP_NAME

APP_NAME returns the application name for the current session.

Example:

select APP_NAME();

Result:

Microsoft SQL Server Data Tools, T-SQL Editor

SqlBulkCopy C# SQL Server import data example

C# > System.Data  > SqlClient > SqlBulkCopy

SqlBulkCopy efficiently bulk load a SQL Server table with data from another source.
The data source is not limited to SQL Server, any data source can be used.
SqlBulkCopy offers a significant performance.






Example:

string sourceConnectionString = "YourSourceConnectionString";

string destinationConnectionString = "YourDestinationConnectionString";

using (SqlConnection sourceConnection =

       new SqlConnection(sourceConnectionString))

            {

                sourceConnection.Open();

                SqlCommand commandSourceData = new SqlCommand(

                    "SELECT * FROM source_table;", sourceConnection);

                SqlDataReader reader = commandSourceData.ExecuteReader();

                using (SqlConnection destinationConnection =

                           new SqlConnection(destinationConnectionString))

                {

                    destinationConnection.Open();

                    using (SqlBulkCopy bulkCopy =

                               new SqlBulkCopy(destinationConnection))

                    {

                        bulkCopy.DestinationTableName = "dbo.destination_table";

                        try

                        {

                            bulkCopy.WriteToServer(reader);

                        }

                        catch (Exception ex)

                        {

                            MessageBox.Show(ex.Message);

                        }

                        finally

                        {

                            reader.Close();

                        }

                    }

                }

            }

CAST SQL Server Example

SQL Server > Built-in Functions > CAST

CAST converts an expression of one data type to another.

CAST ( expression AS data_type )

Examples

• String to number
• Number to date
• Date only - no time

Format SQL Server Example

SQL Server > Built-in Functions > FORMAT

Formats a values to specific format.






Example:

A.

DECLARE @d DATETIME = '10/17/2014';

SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'

      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'

      ,FORMAT ( @d, 'd', 'ro-ro' ) AS 'Romanian Result';

Result

US English Result German Result Romanian Result

10/17/2014         17.10.2014 17.10.2014

B.

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd/MM/yyyy')

Result

(No column name)

13/06/2014

C.

SELECT FORMAT(100, 'N', 'en-us') AS 'Number Format',FORMAT(200, 'C', 'en-us') AS 'Currency Format'

Result

Number Format Currency Format

100.00         $200.00

Lower SQL Server Example

SQL Server > Built-in Functions > LOWER

Converts uppercase character data to lowercase.

Example

SELECT lower('SQL SERVER')

Result:

sql server

SQL Server Drop

SQL Server > DDL > Drop

DROP statements removes existing entities.

• DROP TABLE
• DROP TRIGGER

sys.schemas SQL Server example

SQL Server > Catalog Views > sys.schemas

Gets information about database schema.

Example:

select * from sys.schemas

Result

sys.data_spaces SQL Server

SQL Server > Catalog Views > sys.data_spaces

Return info for each data space.

Example

select * from sys.data_spaces