Search This Blog

Monday, November 25, 2013

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
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


         





Friday, November 22, 2013

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


DateAddDateDiffDatePartDateSerial
DateValueDayDDBDeleteSetting
Derived MathDirEnvironEOF
ErrorToStringFileAttrFileCloseFileCopy
FileDateTimeFileGetFileGetObjectFileLen
FileOpenFilePutFilePutObjectFileWidth
FilterFixFormatFormatCurrency
FormatDateTimeFormatNumberFormatPercentFreeFile
FVGetAllSettingsGetAttrGetChar
GetExceptionGetObjectGetSetting