Why use sql server express?

Many times I have found people asking questions like “why to use sql express?”

Today we will discuss about some points why to use sql express.

·         First of all SQl express is free product of Microsoft

·         When it is not required to use all features of sql server we can use sql express

·         Sql express supports maximum size of 4GB (sql server 2008 and earlier) and 10 GB (sql server 2008R2) per database excluding the log files

·         The tool should be used for small to medium scale applications

·         This edition supports many of the more advanced features available in the full SQL Server version, such as stored procedures, views, functions, CLR integration, snapshot isolation, and XML support

·         There is no problem for concurrent users to access and update data at the same time

Hardware-utilization limits:

• Single physical CPU, but multiple cores allowable
• 1 GB of RAM (runs on a system with any RAM amount, but uses only at most 1 GB)

Merge statement sql server 2008

Merge statement sql

Merge statement is used to insert and update existing conditions data based on conditions whether they are met or not.

Facts about Merge statement:

• We can perform atomic statements (Insert,delete,update) based on conditions
• End of merge statement contains semicolon mark (;)
• Merger is defined by ANSI SQL, works in other database platforms as well
• Contains conditions as MATCHED and NOT MATCHED

CREATE TABLE STUDENT (

Student_Id int,

First_Name  VARCHAR(20),

Last_Name   VARCHAR(20),

Branch_No     int,

Fees      int);

INSERT INTO STUDENT VALUES (1, 'Neil', 'Sarkar', 11, 100000);

INSERT INTO STUDENT VALUES (2, 'deepak', 'Mishra', 52, 100000);

INSERT INTO STUDENT VALUES (3, 'Neha', 'Kumari', 52, 50000);

INSERT INTO STUDENT VALUES (4, 'Deepa', 'Sarkar', 52, 40000);

INSERT INTO STUDENT VALUES (5, 'Mukul', 'Bharti', 52, 30000);

INSERT INTO STUDENT VALUES (6, 'Vipul', 'Dalvi', 52, 70000);

INSERT INTO STUDENT VALUES (7, 'Michael', 'Puling', 52, 90000);

CREATE TABLE SCHOLARSHIP (

Student_Id int, Scholarship int DEFAULT 200);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (1);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (2);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (4);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (6);

INSERT INTO SCHOLARSHIP (Student_Id) VALUES (7);

MERGE [SCHOLARSHIP] AS S

USING

(

  SELECT Student_Id, Fees

  FROM STUDENT

  WHERE Branch_No =52) as E

ON (S.Student_Id = E.Student_Id)

WHEN MATCHED THEN

  UPDATE SET S.Scholarship = E.Fees * 10

WHEN NOT MATCHED THEN

  INSERT (Student_Id, Scholarship)

  VALUES (E.Student_Id, E.Fees * 0.6);

SSIS- script task to export data from database table to .csv file

SSIS- script task to export data from database table to .csv file

User::INPUT_FILE_PATH - input file path to get output file name same as the input file name

User::OutputPath - output path where we get the output in .csv format

User::outputdatabaseConnectionString - Database connection string

/*

   Microsoft SQL Server Integration Services Script Task

   Write scripts using Microsoft Visual C# 2008.

   The ScriptMain is the entry point class of the script.

*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.Web;

using System.Collections.Generic;

//using System.Linq;

//using System.Web.Security;

//using System.Web.UI;

//using System.Web.UI.WebControls;

//using System.Web.UI.Adapters;

//using System.Web.UI.WebControls.WebParts;

//using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Text;

using System.IO;

namespace ST_f9e64226a54d4da28a70f42ab090a637.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        string _FileName = "";

        string unicode = "utf-8";

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

       

        public void Main()

        {

            // TODO: Add your code here

            string path = Dts.Variables["User::INPUT_FILE_PATH"].Value.ToString();

            String fname = Dts.Variables["User::OutputPath"].Value.ToString() + "\\" + GetFileNames(path);

            Dts.TaskResult = (int)ScriptResults.Success;

            //GetFileNames(path);

            CreateCSVFile(GetTableData(), fname);

        }

        //public static Encoding GetEncoding(string unicode);

        public string GetFileNames(string path)

        {

            string tFileName = new DirectoryInfo(path).GetFiles("*.csv")[0].Name;

            //string[] files = Directory.GetFiles(path);

            //string tfileName=files[0].ToString(); //as assuming always one file

            string[] fileparts = tFileName.Split('_');

            string filePart = fileparts[0] + "_" + fileparts[1] + "_" + fileparts[2] + "_" + "Othertext" + "_" + fileparts[5];

            _FileName = filePart;

            return _FileName;

        }

        public DataTable GetTableData()

        {

            String connstring = Dts.Variables["User::outputdatabaseConnectionString"].Value.ToString();

           

            SqlConnection conn = new SqlConnection(connstring);

            SqlCommand cmd = new SqlCommand("select * from table", conn);

            cmd.CommandType = CommandType.Text;

            SqlDataAdapter adap = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            adap.Fill(dt);

            return dt;

        }

        public void CreateCSVFile(DataTable dt, string strFilePath)

        {

            StreamWriter sw = new StreamWriter(strFilePath, false);

            int iColCount = dt.Columns.Count;

            for (int i = 0; i < iColCount; i++)

            {

                sw.Write(dt.Columns[i]);

                if (i < iColCount - 1)

                {

                    sw.Write(",");

                }

            }

            sw.Write(sw.NewLine);

            // Now write all the rows.

            foreach (DataRow dr in dt.Rows)

            {

                for (int i = 0; i < iColCount; i++)

                {

                    if (!Convert.IsDBNull(dr[i]))

                    {

                        sw.Write(dr[i].ToString());

                    }

                    if (i < iColCount - 1)

                    {

                        sw.Write(",");

                    }

                }

                sw.Write(sw.NewLine);

            }

            sw.Close();

        }

    }

}

Excel Functions - Statistical Functions part2

Excel Functions - Statistical functions part 1

Excel Functions - Logical Functions

Excel Functions - Lookup

Excel Functions - TEXT functions

Excel Functions - Conditional Formatting

Excel Function - Date and Time

The Next Topic Conditional Formatting will be explained in next blog

dynamic pivot table

Dynamic pivot table:- Easy learning

create table TestData

(

    Sno int,

    product varchar(30),

    product_value varchar(50)

   

)

--select * from TestData

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (1, N'A', N'100')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (2, N'A', N'200')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (3, N'A', N'300')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (4, N'B', N'400')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (5, N'A', N'500')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (6, N'C', N'600')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (7, N'C', N'700')

INSERT [dbo].[TestData] ([Sno], [product], [product_value]) VALUES (8, N'B', N'800')

DECLARE @cols AS VARCHAR(MAX),

            @colsname as varchar(max),

            @query  AS VARCHAR(MAX);

           

set @cols = ''

set @colsname = ''

SELECT @colsname = COALESCE(@colsname + '['+ product+'],' ,','),

            @cols = COALESCE(@cols + 'ISNULL(sum(CAST(['+ product+']AS FLOAT)),0)'+ '['+ product+'],' ,',')

FROM

(

select distinct product from TestData    

)

TEMP

SET @colsname = SUBSTRING(@colsname,1,LEN(@colsname) -1)

SET @cols = SUBSTRING(@cols,1,LEN(@cols) -1)

set @query = 'SELECT Sno, ' + @cols + 'from

            (

                select Sno, product_value,product

                from TestData

            ) x

            pivot

            (

                max(product_value)

                for product IN(' + @colsname + ')

            ) p group by sno'

execute(@query)

sql question

A good sql question:- what command should you write other than print  after set statement to get the message as shown below :)

sql server interview questions mostly asked

·         What is RDBMS?

o   RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields. 

RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language.

• What are the Properties of the Relational Tables?

o    Relational tables have six properties 
1. Values are atomic. 
2. Column values are of the same kind. 
3. Each row is unique. 
4. The sequence of columns is insignificant. 
5. The sequence of rows is insignificant. 
6. Each column must have a unique name. 

• What is Normalization?

o    Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

o    Edgar F. Codd, the inventor of the relational model

• What is De-normalization?

o    Denormalization is a strategy that database managers use to increase the performance of a database infrastructure. It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table. The definition of denormalization is dependent on the definition of normalization, which is defined as the process of organizing a database into tables correctly to promote a given use.

• How is ACID property related to Database?

o    The ACID properties of a DBMS allow safe sharing of data. Without these ACID properties, everyday occurrences such using computer systems to buy products would be difficult and the potential for inaccuracy would be huge. Imagine more than one person trying to buy the same size and color of a sweater at the same time -- a regular occurrence. The ACID properties make it possible for the merchant to keep these sweater purchasing transactions from overlapping each other -- saving the merchant from erroneous inventory and account balances.

• What is a Stored Procedure?

o    The name for a batch of Transact-SQL or CLR code that is stored within SQL Server and can be called directly by applications or within other programming constructs.

Types of Stored Procedures

There are two main types of stored procedure -

System stored procedures and

User-defined stored procedures

·         What is Trigger?

o    A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries

• What is a View?

o    A view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

• What is the Difference between a Function and a Stored Procedure?

o    Procedure can return zero or n values whereas function can return one value which is mandatory.

o    Procedures can have input/output parameters for it whereas functions can have only input parameters.

o    Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

o    Functions can be called from procedure whereas procedures cannot be called from function.

o    Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in function.

o    We can go for transaction management in procedure whereas we can't go in function.

o    Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

o    UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

o    UDFs that return tables can be treated as another row set. This can be used in JOINs with other tables.

o    Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowsetoperations.

·         What is subquery?

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

§   SELECT

§   INSERT

§   UPDATE

§   DELETE

·          Find rest sql questions:-

o    What are Different Types of Join?

o    What are Primary Keys and Foreign Keys?

o    What is User-defined Functions? What are the types of User-defined Functions that can be created?

o    What’s the Difference between a Primary Key and a Unique Key?

o    What is Difference between DELETE and TRUNCATE Commands?

o    What is the Difference between a HAVING clause and a WHERE clause?

o    Name 3 ways to get an Accurate Count of the Number of Records in a Table?

o    What is CHECK Constraint?

o    What is NOT NULL Constraint?

o    What is the difference between UNION and UNION ALL?

o    What is BCP? When is it used?

o    What is Aggregate Functions?

o    Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select from table)?

o    What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?

o    Does the Order of Columns in UPDATE statements Matter?

o    Explain Few of the New Features of SQL Server 2008 Management Studio