What are the most important things to learn in web development as of 2015?

If you are web development then you must know following.

Front End Developer
1. HTML5, CSS3, JavaScript, Jquery
2. JS Framework- angular
3. UI library- Semantic UI, Bootstrap, Foundation, Polymer
4. Front End Tools - Gulp, Grunt, Bower
5. CSS preprocessor - LESS, SAAS, Stylus
6. JS preprocessor - CoffeeScript, TypeScript, LiveScript
7. Asynchronous - Ajax

Back End Developer
1. PHP, Python, Ruby
2. Backend Framework -
               PHP - Symfony, Laravel, Nette, Zend, CodeIgniter
               Ruby - Ruby On Rail
               Python - django
               JavaScript - Node.js, Express.js, Total.js, Sails.js, Meteor.js
 3. Database - MySQL, MongoDB, PostgressSQL, MariaDB

Code Quality
1. TypeSystems - FlowTyped, TypeScript
2. Testing - Mocha, qunit
3. Quality - jslint

Summary
This is latest list for development but you can also need to learn ASP.NET, Java, MS SQL Server etc
             

How to insert 1000 rows at a time

How to insert 1000 rows at a time

Create a table, suppose Status in which two columns first Id with Identity and another one is value.

CREATE TABLE Status
(
    ID INT IDENTITY(1,1),
   Value INT
)

Now Insert data into table Status

DECLARE @ID INT
SELECT @ID = 1
WHILE @ID>=1 AND @ID<=1000
BEGIN
  INSERT INTO Status VALUES(1)

END

Results:

UNION and UNION ALL

UNION and UNION ALL

UNION allows to combine the results of two or more TABLE using SELECT statement into a single result set.

You can use UNION on table that have same structure to get results like they must have the same number of columns and columns must have compatible data types

by default, UNION removes duplicate rows from the result set but if you use ALL (UNION ALL) then all rows are included in the results and duplicates are not removed

When UNION is used, the individual SELECT statement cannot have their own ORDER BY or COMPUTE clause
but There can be only one ORDER BY or COMPUTE clause after the last SELECT statement.

Syntax:
select_statement UNION [ALL] select_statement

CREATE TABLE  FY1
(CustomerNo INT PRIMARY KEY,
 OrderAvg_FY1 INT,
 OrderCount_FY1 INT)

INSERT INTO FY1 VALUES(0155, 300, 7),
(0133, 700, 6),
(0144, 200, 2)

CREATE TABLE  FY2
(CustomerNo INT PRIMARY KEY,
 OrderAvg_FY2 INT,
 OrderCount_FY2 INT)

INSERT INTO FY2 VALUES(0155, 100, 2),
(0130, 500, 4),
(0144, 20, 1),
(0011, 25, 3)


SELECT *FROM FY1
UNION
SELECT *FROM FY2

Results:

and result set of UNION ALL are:

What is RDBMS?

Relational DataBase Management System (RDBMS) is a database management system (DBMS) that is based on the relational model as invented by E. F. Codd. In RDBMS relationships maintained throughout the data and tables.
                                                                                           
RDBMS is the base for SQL and all latest database system like MS SQL Server, Oracle, MySQL MS Access.  Data in RDBMS is stored in database called tables.

There are the following categories of data integrity exist with each RDBMS:

Entity integrity: 

It specifies that there should be no duplicate rows in a table.

Domain integrity: 

It enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: 

It specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: 

It enforces some specific business rules that are defined by users. These rules are different from entity, domain or referential integrity.

Although DBMS and RDBMS both are used to store information in physical database but there are some remarkable differences between them.

Difference between DBMS and RDBMS

You can also read other differences:

• What is the difference between text and image data type?
• The difference between out and ref
• Difference between Copy and Clone Method
• Difference between Close() and Dispose() Method
• Difference Between String and StringBuilder

The main differences between DBMS and RDBMS are given below:

1. In DBMS, applications store data as file while in RDBMS applications data store in tables.

2. In DBMS, Data is generally stored in either heirarchical form or navigational form while in RDBMS, the tables have an identier called primary key and the data values are stored in the form of tables.

3. In DBMS, there is no Normalization while in RDBMS Normalization applies on tables

4. DBMS does not apply any security with regards to data Manipulation While in RDBMS defines the integrity constraint for the purpose of ACID(Atomicity, consistency, Isolation and Durability) property.

5. DBMS uses files system to store data, so there will be no relation between the tables while in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well

6. DBMS has to provide some uniform methods to access the stored information

while RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information

7. DBMS does not support distributed database

while RDBMS supports distributed database

8. DBMS is meant to be for small organization and deal with small data. it supports single user

while RDBMS is deigned to handle large amount of data. it supports multiple uses

9. Examples of DBMS are file systems, xml etc. and RDBMS example are mysql, postgres, sql server, oracle etc.

Top skill to get your dream jobs

Here I am listing Top skill to get your dream jobs. Currently many technology used in market but these technology are latest and mostly related to Business Intelligence.

1. PaaS

2. MapReduce

3. Cassandra

4. Cloudera

5. Hbase 

6. Pig 

7. ABAP 

8. Chef 

9. Flume 

10. Hadoop 

11. Puppet

12. NoSQL

13. Zookeeper

14. SOA

15. Data Architect

16. Solr

17. Data Scientist

18. Big Data

19. OpenStack

20. CMMI

21. R

22. CloudStack

23. OmniGraffle

24. Arista

25. Documentum

26. UML

27. Sqoop

28. JDBC

29. RDBMS

How to fetch the row count for all tables in a SQL SERVER database

Many times you have requirement to determine if any data or row count in a given database.

So, today I am scripting this scenario to fetch all data or row count in database. for this I will use sys.tables, sys.indexes, sys.partitopns, sys.allocation_units



Script 1:

SELECT t.name TableName, i.rows Records
FROM sysobjects t, sysindexes i
WHERE t.xtype='U' AND i.id = t.id AND i.indid in(0,1)
ORDER BY TableName

Script 2:

SELECT o.NAME,
  i.rowcnt
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME


script3:
SELECT o.name,
  ddps.row_count
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME

Script 4:

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        A.Name, SUM(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
GO

Script 5: Broadview

SELECT t.name[TableName], i.name[IndexName], p.[rows], SUM(a.total_pages)[ToalPages],
SUM(a.used_pages)[UsedPages],SUM(a.data_pages)[DataPages],
(SUM(a.total_pages)*8/1024)[TotalSpaceMB],
(SUM(a.used_pages)*8/1024)[UsedSpaceMB],
(SUM(a.data_pages)*8/1024)[DataSpaceMB]
 
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id =i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id =a.container_id
WHERE
t.name NOT LIKE 'dt%' AND
i.object_id>255 AND
i.index_id<=1
GROUP BY
t.name, i.object_id, i.index_id, i.name,p.[rows]
ORDER BY OBJECT_NAME(i.object_id)


you can get more solutions by manipulating sys table carefully.

How to count NULL values in all column in Table?

To solve this problem first create sample table


CREATE TABLE Employee
(
EmployeeId INT,
EmployeeName VARCHAR(50),
Contact INT
)
 
INSERT INTO Employee
VALUES(101,'Rishi Sanuj',NULL),(102,NULL,NULL), (103,NULL, 123456)

SELECT *FROM Employee

SELECT EmployeeId,
SUM(CASE WHEN EmployeeName IS NULL THEN 1 ELSE 0 END)
+ SUM (CASE WHEN Contact IS NULL THEN 1 ELSE 0 END) AS NumberOfNull

FROM Employee

GROUP BY EmployeeId

Temporary Store Procedure in SQL Server

Temporary Store Procedure is feature of SQL Server. But temporary store procedure is little known to us while we are more familiar to temporary Table.

Temporary Store procedure is similar to normal Store Procedure but as name describe it exists temporarily to database.

Like Temporary Table, Temporary Store procedure is also two types:

• Local 
• Global

You can create Temporary Store Procedure as you create normal store procedure but not forget to add # for local Temporary Store Procedure and ## for Global Temporary Store Procedure

Local Temporary Store Procedure available only in the current session( similarity with Local Temporary Table) and is automatically dropped when session end or closed.

Global Temporary Store Procedure is available to all session and is dropped when the session of the user that created it is closed.

You can also read:

• What are joins? How many types of Joins?
• Difference between char and nvarchar / char and varchar data-type?
• How to check if a table exists in SQL Server 2000/2005 using SQL Statement.
• Add a column to an existing table in SQL Server

Example Local Temporary Store Procedure

 CREATE PROC #TempStudentList
 AS
 DECLARE @Student TABLE (Id INT, StudentName VARCHAR(50), Course VARCHAR(50));

 INSERT INTO @Student
VALUES (1,'Rishi Sanuj', 'MCA'),
                         (2, 'Sushant Priyadarshi', 'Nursery'),
                 (3,'Ashish Kumar', 'Engineering'),
                         (4, 'Kundan Kumar','Hotel Management')

SELECT *FROM @Student
RETURN
GO


View Result:

EXEC #TempStudentList

Example Global Temporary Table

CREATE PROC ##TempStudentList
AS
DECLARE @Student TABLE (Id INT, StudentName VARCHAR(50), Course VARCHAR(50));

 INSERT INTO @Student
VALUES (1,'Rishi Sanuj', 'MCA'),
                         (2, 'Sushant Priyadarshi', 'Nursery'),
                         (3,'Ashish Kumar', 'Engineering'),
                         (4, 'Kundan Kumar','Hotel Management')

SELECT *FROM @Student

RETURN

GO

View Result:
        EXEC ##TempStudent

Uses of Temporary Store Procedure

1. When you have not object CREATE permission in any type of database then use it.
2. Test procedure procedure before actually creating it

How to create a function to encrypt password using RC4 algorithm

First we create a sql function using RC4 algorithm which encrypt password.

CREATE FUNCTION dbo.fnEncryptPassword
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

BEGIN
DECLARE @Key TABLE (i TINYINT, v TINYINT)

DECLARE @Index SMALLINT,
@PwdLen TINYINT

SELECT @Index = 0,
@PwdLen = LEN(@Pwd)

WHILE @Index <= 255
BEGIN
INSERT @Key
(
i,
v
)
VALUES (
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)

INSERT @Box
(
i,
v
)
VALUES (
@Index,
@Index
)

SELECT @Index = @Index + 1
END


DECLARE @t TINYINT,
@b SMALLINT

SELECT @Index = 0,
@b = 0

WHILE @Index <= 255
BEGIN
SELECT @b = (@b + b.v + k.v) % 256
FROM @Box AS b
INNER JOIN @Key AS k ON k.i = b.i
WHERE b.i = @Index

SELECT @t = v
FROM @Box
WHERE i = @Index

UPDATE b1
SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM @Box b1
WHERE b1.i = @Index

UPDATE @Box
SET v = @t
WHERE i = @b

SELECT @Index = @Index + 1
END

RETURN
END

And this function does the encrypt/decrypt part



CREATE FUNCTION dbo.fnEncryptDecrypt
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)

INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)

DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
      @CipherBy TINYINT,
      @Cipher VARCHAR(8000)

SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''

WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256

SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i

SELECT @t = v
FROM @Box
WHERE i = @i

UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i

UPDATE @Box
SET v = @t
WHERE i = @j

SELECT @k = v
FROM @Box
WHERE i = @i

SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j

SELECT @k = v
FROM @Box
WHERE i = @k

SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)

SELECT @Index = @Index  +1
      END

RETURN @Cipher
END



Use it tsql

-- returns encrypted password
Select dbo.fnEncryptDecrypt('sqlEncrypt', 'RishiSanuj')

output (encrypted password):
---------------
Q, Ý‚O6âY¦

-- decrypt it using same key
select dbo.fnEncryptDecrypt('sqlEncrypt', 'Q, Ý‚O6âY¦')

output
-----------------
RishiSanuj

What is Stored procedure?

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

Benefits of Store Procedures:
They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.

What are the different types of Storage Procedure?

Temporary Stored Procedures
- SQL Server supports two types of temporary procedures: local and global.
- A local temporary procedure is visible only to the connection that created it.
- A global temporary procedure is available to all connections.
- Local temporary procedures are automatically dropped at the end of the current session.
- Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be
created by any user.

System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored
procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)

Automatically Executing Stored Procedures -
- One or more stored procedures can execute automatically when SQL Server starts.
- The  stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process.
- The procedure(s) cannot have any input parameters.

User stored procedure
- Store procedure created by user is known as User Store Procedure

How do I mark the stored procedure to automatic execution?

You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start.

USE master
EXEC sp_procoption 'indRebuild', 'startup', 'true')


How will know whether the SQL statements are executed?

When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure
returns the value 0. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred.

Why one should not prefix user stored procedures with sp_?
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
0. The stored procedure in the master database.
1. The stored procedure based on any qualifiers provided (database name or owner).
2. The stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always
checked first, even if the stored procedure is qualified with the database name.

What can cause a Stored procedure execution plan to become invalidated and/or fall out of cache?
0. Server restart
1. Plan is aged out due to low use
2. DBCC FREEPROCCACHE (sometime desired to force it)

When do one need to recompile stored procedure?
if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).

SQL Server provides three ways to recompile a stored procedure:

• The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

• Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it
is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.

• You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if
the data has significantly changed since the stored procedure was created.

What is derived table?

Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement.

For example, this SELECT uses a derived table to find if any store carries all book titles in the pubs database:

SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

What are joins? How many types of Joins?

Many times we have to select data from two or more tables to make our result complete.  to achieve desired or expect result we have to perform a join.

How many types of Joins?

Joins can be categorized as:

Inner joins
- The typical join operation, which uses some comparison operator like = or <>
- These include equi-joins and natural joins.
- Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table.

Example:
retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins:
- Outer joins can be a left, a right, or full outer join.
- Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER
clause, not just the ones in which the joined columns match.
When a  row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN
- A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null
values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN
- A full outer join returns all rows in both the left and right tables. Any time a row has no match in the
other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins -
- Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table.
- Cross joins are also called Cartesian products. (A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can
also get one by joining every row of a table to every row of itself.)

What is self join?
A table can be joined to itself in a self-join.

What is the difference between text and image data type?

Text and image.
Use text for character data if you need to store more than 255 characters in SQL Server 6.5, or more than 8000 in SQL Server 7.0.

Use image for binary large objects (BLOBs) such as digital images. With text and image data types, the data is not stored in the row, so the limit of the page size does not apply.

All that is stored in the row is a pointer to the database pages that contain the data.Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row.

Difference between char and nvarchar / char and varchar data-type?

char[(n)] -
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

nvarchar(n) :
 Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000.
Storage size, in bytes, is two times the number of characters entered.
The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national
char varying and national character varying.

Varchar[(n)] :
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

Garbage Collection Concept

Garbage collection is the mechanism that reclaims the memory resources of an object when it is no longer referenced by a variable.

 .Net Runtime performs automatically performs garbage collection, however you can force the garbage collection to run at a certain point in your code by calling System.GC.Collect().

 Advantage of Garbage collection : It prevents programming error that could otherwise occur by incorrectly deleting or failing to delete objects.

.NET Framework versions

.NET Framework 2.0

•  64bit support
• Generics
• Anonymous Methods
• partial Class 
• Nulllable type
• new API gives a fine control on the behavior of the runtime with regards to multithreading, memory allocation, assembly loading and more
• new personalization feature for asp.net like themes,skin and web parts
• .NET Micro Framework
• Language support for generic built directly into the .NET CLR
• ADO.NET
• support for user defined type
• xml data type
• large value type
• snapshot isolation
• new attribute that allow application to support  multiple active result set(MARS) with sql server 2005
• ASP.NET
• Authenticated stream
• COM interop service enhancement
• Generic and collections
• Manifest based activation
• .NET Framework remoting

.NET Framework 3.0

• Framework2.0+WCF+WPF +WF
• It is called as WinFX, includes a new set of managed code API that are an integral part of window vista and windows server 2008 OS
• Wpf formaly called Avalon; a service-oriented messaging system which allows programs to interoperate locally or remotely similar to web services
• WWF allows for building of task automation and integrated transactions using workflows
•         windows cardspace, formerly called infoCard; a software component which securely store a person’s digital identities and provides a unified interface for choosing the identity for a particular transaction

       
  .NET Framework 3.5

• most dominant change is the addition of LINQ
• implements LINQ evolution in language
• addin system 
• p2p base class
• Active directory – asp.NET ajax
• anonymous types with static type inference
• paging support for ADO.NET
• ADO.NET synchronization API to synchronize local caches and server side datastores
•  asynchronous network I/O API
• support for HTTP pipelining and syndication feeds
• new system.codeDom namespace

 .NET Framework 4.0

• Parallel Extensions to improve support for parallel computing, which target multi-core or distributed systems
• New Visual Basic .NET and C# language features, such as implicit line continuations, dynamic dispatch, named parameters, and optional parameters
• Support for Code Contracts
• Inclusion of new types to work with arbitrary-precision arithmetic (System.Numerics.BigInteger) and complex numbers (System.Numerics.Complex)
• Introduce Common Language Runtime (CLR) 4.0

.NET Framework 4.5

• .net for windows store app
• Portable class library
• Managed Extensible Framework
• Asynchronous File Operations
• Parallel computing

What is the difference between the destructor and the Finalize() method? When does the Finalize() method get called?

Finalize() corresponds to the .Net Framework and is part of the System.Object class.

Destructors are C#'s implementation of the Finalize() method.

The functionality of both Finalize() and the destructor is the same, i.e., they contain code for freeing the resources when the object is about to be garbage collected.

In C#, destructors are converted to the Finalize() method when the program is compiled.

The Finalize() method is called by the .Net Runtime and we can not predict when it will be called.

It is guaranteed to be called when there is no reference pointing to the object and the object is about to be garbage collected.

What is “ref” keyword in C#

Passing variables by value is the default. However, we can force the value parameter to be passed by reference.

Point to be kept in mind while using ref keyword.

variable “must” be initialized before it is passed into a method.

Difference between Copy and Clone Method

Clone will copy the structure of a data where as

Copy will copy the complete structure as well as data.

Difference between Close() and Dispose() Method

The basic difference between Close() and Dispose() is, when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used.

Where as Dispose() method permanently removes any resource ((un)managed) from memory for cleanup and the resource no longer exists for any further processing.

Difference Between String and StringBuilder

String:

      - Once the string object is created, its length and content cannot be modified.

      - this is Slower.

 StringBuilder:

     - Even after object is created, it can be able to modify length and content.

     - Faster

.NET Framework 2.0, 3.0 features

Currently .NET version 5.0 released by Microsoft. But to keep update you must know feature  of earlier version of .net

Following features were introduce in .NET Framework 2.0

• Full 64-bit computing support for both the x64 and the IA-64 hardware platforms
• Many API changes
• Microsoft SQL Server integration
• A new hosting API for native applications wishing to host an instance of the .NET runtime
• Many additional and improved ASP.NET web controls
• New data controls with declarative data binding
• New personalization features for ASP.NET, such as support for themes, skins, master pages and webparts
• .NET Micro Framework, a version of the .NET Framework related to the Smart Personal Objects Technology initiative
• Membership provider
• Partial classes 
•  Generics
• Nullable Types
• Anonymous Methods
• Property Access Accessibility Modifiers
• Iterators
• Data tables
• Common Language Runtime (CLR) 2.0
• Language support for generics built directly into the .NET CLR
  
  
  

Following features were introduced in  3.0
.NET Framework 3.0, formerly called WinFX

.NET Framework 3.0 consists of four major new components:

• Windows Presentation Foundation (WPF)
• Windows Communication Foundation (WCF)
• Windows Workflow Foundation (WWF)
• Windows CardSpace

      

         

The Heap and The Stack

The Heap and The Stack

Procedural programming languages that let you to create variables dynamically at run-time use two different areas of Ram for holding variables;. the Stack and the Heap. The Heap is basically all unallocated memory.

The Stack holds value type variables plus return addresses for functions. All numeric types, ints, floats and doubles along with enums, chars, bools and structs are value types.

The Heap hold variables created dynamically- known as reference variables and mainly instances of classes or strings. These variables are stored in two places; there's a hidden pointer to the place in the heap where the data is stored.

Another distinction between value and reference type is that a value type is derived from System.ValueType while a reference type is derived from System.Object.

If you assign a value type variable to another then a direct copy of the value is made. But copying a reference type variable just makes a copy of the reference to the variable and does not affect the variable itself. This is like pointers in C and C++. You aren't copying what the pointer points to but making a copy of the pointer itself.

How to replace a string in a SQL Server Table Column

In a table which a column references string  and I need to change the same string to the new one, in every record.

Sytax

update my_table
set path = replace(Column_Name, 'oldstring', 'newstring')

you can also read:

• Add a column to an existing table in SQL Server
•  Eliminating Duplicates with DISTINCT

Example

update tblStudent
set Email = replace(Email, '@yahoo.com', '@gmail.com')

How to check if a table exists in SQL Server 2000/2005 using SQL Statement.

How to check if a table exists in SQL Server 2000/2005 using SQL Statement.

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

First Method:

IF EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tblStudent'
)
BEGIN
    -- do needful
END

Second Method:

IF OBJECT_ID(N'tblStudent',N'U') IS NOT NULL
BEGIN
    SELECT *FROM tblStudent
END


Also to check for a temporary Table, we have to query the tempdb database and use a Like operator for the table name

SELECT *FROM tempdb.INFORMATION_SCHEMA WHERE TABLE_SCHEMA =' TheSchema' AND TABLE_Name LIKE '#MyTemp%'

another method

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
BEGIN
SELECT *FROM #Temp2
END

Add a column to an existing table in SQL Server

Altering a table is common situation during development. Sometimes it create some trouble. So Here I describing how to add a column to an existing table in SQL Server

Here Table structure

CREATE TABLE tblStudent
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 StudentName VARCHAR(50) NOT NULL DEFAULT('')
)


Here,  Adding a column in email to existing tblStudent. To achieve this alter the table tblStudent
Syntax
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Example-
ALTER table to add a column with DEFAULT

ALTER TABLE tblStudent
ADD Email VARCHAR(50) NOT NULL DEFAULT('')

ALTER table to add a column with DEFAULT and  CONSTRAINT

ALTER TABLE tblStudent
ADD Contact VARCHAR(50)
CONSTRAINT cntContact DEFAULT 0 WITH VALUES

How to Encrypt password in SQL Server?

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function are PWDENCRYPT and HASHBYTES.  By the name it is clear that it will crease the password.

The syntax for PWDENCRYPT is:

  PWDENCRYPT(N’String to encrypt’)

HASHBYTES function which is available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below
HASHBYTES (ALGORITHM, ‘Text to secure’)


Here, SHA2_256, SHA2_512 available with 2012 version and above.

Regular Expression to validate input text

Regular expression for Alpha numeric:

^[0-9a-zA-Z]+$


Regular expression for Alpha numeric with space:

^[0-9a-zA-Z ]+$


Regular expression for Alpha numeric with length:

^[0-9a-zA-Z]{5,} +$


Regular expression for Alpha numeric with single Quote and dot:

^[0-9a-zA-Z'.]+$

Regular expression for Email:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

Regular expression for For contact:

^[01]?[- .]?(\([2-9]\d{2}\)|[2-9]\d{2})[- .]?\d{3}[- .]?\d{4}$

There are different format for Phone number. To Validate following:

• (123) 456 7899
• (123).456.7899
• (123)-456-7899
• 123-456-7899
• 123 456 7899
• 1234567899

use following expression:

'\(?([0-9]{3})\)?([ .-]?)([0-9]{3})\2([0-9]{4})'

Abstract class in c#

An Abstract class is a class that provides a common definition to the subclasses and this is the type of class whose object is not created. 

A classes cannot initialize are called the abstract class. Abstract Classes are incomplete classes, means it will have both combination of implemented and unimplemented method with the properties, index, member, events and delegates. Use the abstract modifier in a class declaration to indicate that a class is made only to be a base class of other classes. We can declare any no of abstract methods in abstract class, but whenever you inherit this class into child class you must be implemented those abstract methods in your child class; otherwise complier gives an error in this case.

Why we use Abstract Class?

 The mail idea to have class as abstract is to provide the flexibility to derived classes to implement as per requirement. Abstract method does not provide an implementation and force to derived class to override this method.

When we use Abstract Class?

If you are creating something for objects that are closely related in a hierarchy, use an abstract class. Abstract classes allow you to provide default functionality for the derived classes. If you plan on updating this base class throughout the life of your program, it is best to allow that base class to be an abstract class. Why? Because you can make a change to it and all of the inheriting classes will now have this new functionality.

Rules of Abstract Class

• Abstract classes are declared using the abstract keyword
• We cannot create an object of an abstract class
• If you want to use it then it must be inherited in a subclass
• An Abstract class contains both abstract and non-abstract methods
• The methods inside the abstract class can either have an implementation or no implementation
• We can inherit two abstract classes; in this case the base class method implementation is optional
• Methods inside the abstract class cannot be private
• Abstract classes are useful when you need a class for the purpose of inheritance and polymorphism
• The purpose of an abstract class is to provide default functionality to its subclasses
• When a class contains at least one abstract method, then the class must be declared as an abstract class
• It is mandatory to override the abstract method in the derived class.

Eliminating Duplicates with DISTINCT

Eliminating Duplicates with DISTINCT 

The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement.

If DISTINCT is not specified, all rows are returned,including duplicates. 

ex. Result without DISTINCT

Result with DISTINCT kwyword