MS SQL Server | SHASHANGKA SHEKHAR'S BLOG

Function evaluation disabled because a previous function evaluation timed out.

Mar 31, 2016 Posted by Shashangka Shekhar .Net, Entity Framework (EF), Tips & Tricks 0 comments

Error: Function evaluation disabled because a previous function evaluation timed out. You must continue execution to re enable function evaluation.

Solution: This is happen while we are time-extended queries in EF.

Convert to List from iDataReader

Mar 25, 2016 Posted by Shashangka Shekhar .Net, Entity Framework (EF), MVC, Tips & Tricks 0 comments

Here we have demonstrate how we can convert execute reader to list.

First: Check if there is any virtual properties.

public virtual tbl_Product tbl_Product { get; set; }

1	public virtual tbl_Product tbl_Product { get; set; }

Second: Reader Property Name should always matches with table column name Read More

Char, Nchar, Varchar & Nvarchar Data Types

Dec 6, 2015 Posted by Shashangka Shekhar T-SQL 1 comment

CHAR vs VARCHAR

CHAR:

It is a fixed length data type
Used to store non-Unicode characters
Occupiers 1 byte of space for each character

VARCHAR:

It is a variable length data type
Used to store non-Unicode characters
Occupies 1 byte of space for each character

NCHAR vs NVARCHAR

NCHAR

Is a fixed length data type
Used to store Unicode characters (for example the languages Arabic, German and so on)
Occupies 2 bytes of space for each character

NVARCHAR

It is a variable-length data type
Used to store Unicode characters
Occupies 2 bytes of space for each character

What is ORM?

Nov 30, 2015 Posted by Shashangka Shekhar Entity Framework (EF) 0 comments

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages.

ORM is a tool for storing data from domain objects to relational database like MS SQL Server, in an automated way, without much programming. O/RM includes three main parts:

Domain class objects,
Relational database objects and
Mapping information on how domain objects map to relational database objects (tables, views & storedprocedures).

ORM allows us to keep our database design separate from our domain class design. This makes the application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so that the developer doesn’t need to write it manually.

Click for more…

SQL Server 2012 VS SQL Server 2014

Nov 12, 2015 Posted by Shashangka Shekhar MS SQL Server 1 comment

There are many performance enhancements in SQL Server 2014.

Standard and BI Editions now support 128 GB of memory (SQL Server 2008 R2 and 2012 only supports 64 GB).
In-Memory Online Transaction Processing(OLTP) a new in-memory engine is a new lock-free, latch-free, optimistic concurrency approach to storing your data in memory. This is nothing like DBCC PINTABLE we might remember from years ago, which still suffered from the locking and latching problems that are resolved in SQL Server 2014.
SQL Server 2014 now supports native backup encryption in Standard, BI and Enterprise Editions.
Support for window functions, which makes it possible to write fast queries for running totals, sliding windows etc.
Columnstore indexes, which can speed up data warehouse enormously. But of no interest for OLTP. Only writable in SQL 2014.

Difference between Stored Procedure and Function

Nov 11, 2015 Posted by Shashangka Shekhar MS SQL Server 0 comments

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from Function.

Aggregate Functions in SQL Server

Oct 3, 2015 Posted by Shashangka Shekhar T-SQL 0 comments

Let’s create a SampleDB database fist with SQL Management Studio. Now create a table in SampleDB.

CREATE TABLE [dbo].[tblEmployee](
	[Emp_ID] [int] NOT NULL,
	[Emp_Name] [varchar](max) NOT NULL,
	[Emp_Age] [int] NOT NULL,
	[Emp_Salary] [int] NOT NULL,
	[Emp_City] [varchar](max) NOT NULL,
	[Emp_Gender] [char](1) NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblEmployee](

[Emp_ID] [int] NOT NULL,

[Emp_Name] [varchar](max) NOT NULL,

[Emp_Age] [int] NOT NULL,

[Emp_Salary] [int] NOT NULL,

[Emp_City] [varchar](max) NOT NULL,

[Emp_Gender] [char](1) NOT NULL,

CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED

(

[Emp_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Insert Sample Data:

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL
SELECT 2,'Shekhar',	19,	40000,	'Bagerhat',	1 UNION ALL
SELECT 3,'Shamim',	21,	35000,	'Dhaka',	0 UNION ALL
SELECT 4,'Shariar',	20,	40000,	'Rajshahi',	1 UNION ALL
SELECT 5,'Rajib',	22,	35000,	'Rajshahi',	0 UNION ALL
SELECT 6,'Avishek',	21,	35000,	'Barishal',	1 UNION ALL
SELECT 7,'Asif',	23,	40000,	'Dhaka',	0

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL

SELECT 2,'Shekhar', 19, 40000, 'Bagerhat', 1 UNION ALL

SELECT 3,'Shamim', 21, 35000, 'Dhaka', 0 UNION ALL

SELECT 4,'Shariar', 20, 40000, 'Rajshahi', 1 UNION ALL

SELECT 5,'Rajib', 22, 35000, 'Rajshahi', 0 UNION ALL

SELECT 6,'Avishek', 21, 35000, 'Barishal', 1 UNION ALL

SELECT 7,'Asif', 23, 40000, 'Dhaka', 0

Aggregate Functions in SQL Server

COUNT (Transact-SQL)

Returns the number of items in a group. COUNT always returns an int data type value. Read More

The History of SQL Server

Oct 3, 2015 Posted by Shashangka Shekhar MS SQL Server 0 comments

SQL stands for Structured Query Language and is pronounced either ess-que-el orsequel. It is the language used by relational database management systems (RDBMS) to access and manipulate data and to create, structure and destroy databases and database objects. Read More

Keyboard Shortcuts in SQL

Oct 3, 2015 Posted by Shashangka Shekhar MS SQL Server 0 comments

This list displays the keyboard shortcuts available in SQL Query Analyzer.

CTRL+F2 :: Insert or remove a bookmark (toggle).
F2 :: Move to next bookmark.
SHIFT+F2 :: Move to previous bookmark.
ALT+BREAK :: Cancel a query. Read More

Table Variables

Oct 3, 2015 Posted by Shashangka Shekhar T-SQL 0 comments

Temp variable can be created like this:

DECLARE @temp_tblEmployee TABLE 
(
	[Emp_ID] [int] NOT NULL,
	[Emp_Name] [varchar](max) NOT NULL
)
INSERT INTO @temp_tblEmployee
SELECT 1,'Shashangka Shekhar'

SELECT * FROM @temp_tblEmployee

GO

DECLARE @temp_tblEmployee TABLE

(

[Emp_ID] [int] NOT NULL,

[Emp_Name] [varchar](max) NOT NULL

)

INSERT INTO @temp_tblEmployee

SELECT 1,'Shashangka Shekhar'

SELECT * FROM @temp_tblEmployee

Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

Temporary Tables

Oct 3, 2015 Posted by Shashangka Shekhar T-SQL 0 comments

Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

Local Temp Table
Global Temp Table

Pages:‹123 ›