SHASHANGKA SHEKHAR'S BLOG

#Author #Speaker

Char, Nchar, Varchar & Nvarchar Data Types

CHAR vs VARCHAR

CHAR:

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

VARCHAR:

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

NCHAR vs NVARCHAR

NCHAR

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

NVARCHAR

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

What is ORM?

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:

  1. Domain class objects,
  2. Relational database objects and
  3. 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

There are many performance enhancements in SQL Server 2014.

  1. Standard and BI Editions now support 128 GB of memory (SQL Server 2008 R2 and 2012 only supports 64 GB).
  2. 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.
  3. SQL Server 2014 now supports native backup encryption in Standard, BI and Enterprise Editions.
  4. Support for window functions, which makes it possible to write fast queries for running totals, sliding windows etc.
  5. 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

  • 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

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

Insert Sample Data:

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

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

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

Temp variable can be created like this:

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

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

  1. Local Temp Table
  2. Global Temp Table

Read More

Pages:123