SQL Server Data Types

SQL Server > Data Types

A data type is an attribute that specifies the type of data that the object can hold.

Exact Numerics

• numeric, decimal
• int, bigint, smallint, tinyint
• bit
• money, smallmoney
  Approximate Numerics
  float

  real

  Date and Time

  date	datetimeoffset
  datetime2	smalldatetime
  datetime	time

  Character Strings

  char	varchar
  text

  Unicode Character Strings

  nchar	nvarchar
  ntext

  Binary Strings

  binary	varbinary
  image

  Other Data Types

  cursor	timestamp
  hierarchyid	uniqueidentifier
  sql_variant	xml
  table	Spatial Types

  rowversion

XML exist XQuery SQL Server example

SQL Server > XML > Methods > exist()

Syntax

          exist (XQuery)

Returns

• 1 : XQuery expression in a query returns a nonempty result
• 0 : XQuery expression in a query returns an empty result.
• NULL : if target NULL.

Example: Find specific node in XML

DECLARE @data  XML

.... you xml 

SELECT @data.exist('(//item1)') 'Item1 exists', @data.exist('(//item3)') 'Item3 exists'

Results:

Add computed column SQL Server

SQL Server > DDL > Alter > Add computed column

You can add computed column with AS keyword followed by your computed formula.

Note: the computed column cannot be updated!

CREATE TABLE #tmp

(

     [hours] float,

     [rate]  float

)

ALTER TABLE #tmp ADD salary AS ([hours] * [rate])

INSERT INTO #tmp([hours], [rate])

SELECT 160, 10

UNION

SELECT 180, 12

SELECT * FROM #tmp

--The column "salary" cannot be modified because it is either a computed column or is the result of a UNION operator.

--update  #tmp

--set salary = salary *1.14

drop table #tmp

SQL Server Catalog Views

SQL Server > Catalog Views

Catalog views return information that is used by the SQL Server Database Engine.

• It is recommend that you query the catalog views instead of system tables
• It is recommend using SELECT * FROM sys.catalog_view_name because the number of columns returned might change

List of catalog views:

• Database mail
• sys.dataspaces
• sys.schemas
• sys.columns

Check the Status of E-Mail Messages SQL Server

SQL Server > Catalog Views > Database mail > sysmail_faileditems

In this SQL example will find information about any emails could not send successfully.


SELECT fi.subject, fi.last_mod_date,lg.description
FROM
   dbo.sysmail_faileditems as fi
INNER JOIN dbo.sysmail_event_log AS lg
ON fi.mailitem_id = lg.mailitem_id

Duration between two dates in SQL Server example

SQL Server > Built-In Functions > DATEDIFF

Returns the number of date and time boundaries crossed between two specified dates.

Example:

Duration between two dates in SQL Server example

DECLARE 

  @date1 DATETIME
,@date2   DATETIME

SELECT
  @date1 = getdate(),
   @date2  = dateadd(day,1,getdate()),
  @date2  = dateadd(minute,5,@date2)

SELECT
  @date1,
  @date2,
  DATEDIFF(hh, @date1, @date2) as Hours,  
  DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @date1, @date2),@date1),@date2) as Minutes

Result:

AppendChild C# XML Example

C# > XML > AppendChild

AppendChild adds node to the end of the list of child nodes.

Example

XmlDocument doc = new XmlDocument();
doc.LoadXml(yourxml);
XmlNode root = doc.DocumentElement;
XmlElement elem = doc.CreateElement("price");
elem.InnerText = "100";
root.AppendChild(elem);

MessageBox.Show(root.OuterXml);