SQL Server sp_server_info example

SQL Server > System Stored Procedures   > sp_server_info

Sp_server_info returns a list of attribute names and values for SQL Server.

Example:

exec sp_server_info

Result:

attribute_id  attribute_name       attribute_value

1      DBMS_NAME     Microsoft SQL Server

2      DBMS_VER      Microsoft SQL Server 2012 - 11.0.2100.60 - 11.0.2100.60
10     OWNER_TERM    owner
11     TABLE_TERM    table
12     MAX_OWNER_NAME_LENGTH      128
13     TABLE_LENGTH  128
14     MAX_QUAL_LENGTH      128
15     COLUMN_LENGTH 128
16     IDENTIFIER_CASE      MIXED
17     TX_ISOLATION  2
18     COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19     SAVEPOINT_SUPPORT    Y
20     MULTI_RESULT_SETS    Y
22     ACCESSIBLE_TABLES    Y
100    USERID_LENGTH 128
101    QUALIFIER_TERM       database
102    NAMED_TRANSACTIONS   Y
103    SPROC_AS_LANGUAGE    Y
104    ACCESSIBLE_SPROC     Y
105    MAX_INDEX_COLS       16
106    RENAME_TABLE  Y
107    RENAME_COLUMN Y
108    DROP_COLUMN   Y
109    INCREASE_COLUMN_LENGTH     Y
110    DDL_IN_TRANSACTION   Y
111    DESCENDING_INDEXES   Y
112    SP_RENAME     Y
113    REMOTE_SPROC  Y
500    SYS_SPROC_VERSION    11.00.2100

sp_tables SQL Server Example

SQL Server > System Stored Procedures   > sp_tables

Returns a list of tables and views that can be queried in the current environment.

Example:

EXEC sp_tables;

 

LINQ Query Expressions C# example

C# > LINQ Query Expressions

Language-Integrated Query (LINQ) is the name for a set of technologies of .NET Framework. This component adds data query  capabilities directly into the C# language.
LINQ expressions are like SQL statements  and can be used to extract and process data from arrays, classes, XML and databases.

Data Source - Query Expression - Execute

Methods

• Single
• FirstOrDefault
• Take
• Join
• Where
• Average

Examples

Example 1.  Fundamental query concepts

            // data source

            int[] int_array = new int[] { 5, 4, 2, 8, 10 };

            // query expression

            IEnumerable<int> intQuery =
                from int_v in int_array
                where int_v >= 5
                select int_v;

            // Execute the query
            foreach (int i in intQuery)
            {
                MessageBox.Show(i.ToString());
            } 
    
Example 2. Order by query

            IEnumerable<int> intQuery =
                from int_v in int_array
                where int_v >= 5
                orderby int_v ascending
                select int_v;

Example 3. Implement RANK function

Example 4. IsNumeric

Example 5. Order By Key Dictionary


         

sp_columns SQL Server example

SQL Server > System Stored Procedures   > sp_columns

sp_columns returns column information for the specified object

Example

create table tmp_Sales(ProductId int, [Year] int, Country varchar(100))
EXEC sp_columns @table_name = N'tmp_Sales'
drop table tmp_Sales

Result:

PIVOT SQL Server Example

SQL Server  > Operators > PIVOT

PIVOT rotates a values from a table by turning the unique values from one column into multiple columns in the output (cross-tab). Also performs aggregations where they are required.

Example:

1.Using PIVOT to get number of product sales

create table #Sales(ProductId int, [Year] int, Country varchar(100))

insert into #Sales(ProductId, [Year], Country  ) values  (1,2012,'Germany'), (1,2012,'UK') , (1,2013,'France') , (2,2013,'Germany')  , (2,2013,'Germany')  , (2,2013,'UK')

SELECT

       Country, [2012], [2013]
FROM
       (SELECT ProductId, [Year], Country FROM #Sales) p
       PIVOT
       (
              COUNT (ProductId)
              FOR [Year] IN ( [2012], [2013] )
       ) AS pvt
ORDER BY pvt.Country ;

drop table #Sales

Result:



2. Dynamic PIVOT and cross-tab query

Create Local temporary table in SQL Server

SQL Server  > Temporary table > Create

Use # character in front of table name to create local temporary table

Example: Create temporary table #user

create table #user(id int, superiorid int)
insert into #user(id, superiorid)
values (1,null), (2,1) , (3,1)
drop table #user

Visual Basic run-time member functions

VB.NET  > Run-time member functions

AppActivate	Asc	AscW	Beep
CallByName	CBool	CByte	CChar
CDate	CDbl	CDec	ChDir
ChDrive	Choose	Chr	ChrW
CInt	CLng	CObj	Command
CreateObject	CShort	CSng	CStr
CType	CurDir

DateAdd	DateDiff	DatePart	DateSerial
DateValue	Day	DDB	DeleteSetting
Derived Math	Dir	Environ	EOF
ErrorToString	FileAttr	FileClose	FileCopy
FileDateTime	FileGet	FileGetObject	FileLen
FileOpen	FilePut	FilePutObject	FileWidth
Filter	Fix	Format	FormatCurrency
FormatDateTime	FormatNumber	FormatPercent	FreeFile
FV	GetAllSettings	GetAttr	GetChar
GetException	GetObject	GetSetting