29 Dec 2017

What is temporary table in sql server


Q. What are temporary tables? when to use them?

Ans. Temporary tables are used to store any data on a temporary basis. in Tempdb.
used to perform data calculations, data formatting operations for reporting.

create table #table1
(
col1 int,
col2 int,
col3 int
)  -- Local temporary table
insert into #table1 values (10,10,10), (20,20,20)

select * from #table1


Local temp tables:- Local temp tables are auto removed from temp-db once you close the session.and local temp tables are represent by single # symbol.

-session means this query windows

create table ##table1
(
 col1 int,
 col2 int,
 col3 int
)  -- global temporary table

insert into ##table1 values (10,10,10), (20,20,20)

select * from ##table1


Global temp tables:- global temp tables are auto removed from temp-db once you close the connection.and global temp tables are represent by ## symbol.


connection means server level authenticated entry to the database.
             
ssms >> server name >> connect close ssms: connection close


for better reuse: permenant tables are recommended.

for faster performance: temp tables are recommended.




Read Complete

Mock interview questions for sql server

 

Q1. WHICH OF THE SYSTEM DATABASES IS LOADED FIRST?

Ans. RESOURCE

Q2. WHAT IS THE SIZE OF TDS PACKETS?

Ans. 4096 BYTES

Q3. WHAT IS THE DEFAULT DRIVER FOR SQL SERVER?

Ans. SQL NATIVE CLIENT (SNAC)

Q4. CREATE TABLE tblprodcts
(
PrdID int,
PrdName varchar(30),
PrdDesc char(8000)
)

FROM ABOVE TABLE, WHAT IS THE MAXIMUM POSSIBLE SIZE OF A ROW?

Ans. 8034 BYTES

Q5.FROM ABOVE TABLE, HOW MANY ROWS CAN BE INSERTED?

Ans. UNLIMITTED ROWS

Q6. WHICH COMES FIRST? SERVICE OR AUTHENTICATION?

Ans.  SERVICE

Q7. AUTHENTICATION IS COMMUNICATION BETWEEN.

USER AND _ SQL SERVER_____________

Q8. SERVER CONTAINS DATABASE. DATABASE CONTAINS PHYSICAL

Ans. FILES.

Q9. SIZE OF A DATA PAGE .

Ans. 8 KB.

Q10. GIVEN A TABLE WITH 3 COLUMNS. HOW TO ADD 4TH COLUMN? WRITE THE SYNTAX.

Ans.ALTER TABLE TABLE-NAME ADD COL4 <TYPE>


Read Complete

What is filegroup in sql server



Database is physically collection of files. Grouped into file groups.
Database is logically collection of tables. Grouped into schemas.

To bridge the gap between these entities: we use “file groups"
File groups are used to group one or more data files and route table data.

Whenever we define a database one file group is auto created: "primary file group"

Syntax for table creation:

                Create table <schema name>.<table name>
                                (
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                ......
                                )
                                On file group name

Only one primary key is possible per table.
No limit on number of unique & foreign keys.
               
Purpose of file groups: easy data allocation + performance tuning +Easy recovery

Purpose of schemas:-      easy security management

Default schema is dob = database owner.


Default file group is primary.

Read Complete

25 Dec 2017

What is Store procedure in SQL server

What is Store procedure in SQL server :-

Stored procedures are database objects used to store any type of T-SQL statements.

Advantage: we code the T-SQL script once. Compile it and store the same in the
Stored procedure Database object.

Whenever we execute the procedure, above compiled code is auto executed.

Compilation means converting high level language code to machine understand format.

               
Purpose of stored procedures:
                                1. Used for data validations
                                2. Used for dynamic SQL programs
                                3. Used for data Formatting and reporting

Types of stored procedures:
                                1. System predefined stored procedures
                                2. User defined stored procedures

                                3. Extended stored procedures

How to create insert procedure :-

EX-         

create table Employee
(
Empid int primary key identity(1,1),
Fname varchar(50),
Lname varchar(50),
Gender int,
Country int,
State int,
DOB datetime,
Email varchar(50),
Password varchar(50),
Salary float,
Hobbies varchar(100),
File_Upload varchar(100),
)

Value insert procedure:-

create proc usp_employee_insert
@Fname varchar(50),
@Lname varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Email varchar(50),
@Password varchar(50),
@Salary float,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
insert into Employee(Fname,Lname,Gender,Country,State,DOB,Email,Password,Salary,Hobbies,File_Upload) values(@Fname,@Lname,@Gender,@Country,
@State,@DOB,@Email,@Password,@Salary,@Hobbies,@File_Upload)
end

How to create get procedure:-

create proc usp_employee_get
as
begin
select * from Employee
end

How to create update procedure:-

create proc usp_employee_update
@Empid int,
@Fname varchar(50),
@Lname varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Email varchar(50),
@Password varchar(50),
@Salary float,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
update Employee set Fname=@Fname,Lname=@Lname,Gender=@Gender,Country=@Country,State=@State,DOB=@DOB,Email=@Email,Password=@Password,Salary=@Salary
,Hobbies=@Hobbies,File_Upload=@File_Upload where Empid=@Empid
end

How to create Edit Procedure:-


create proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid=@Empid
end
Read Complete

23 Dec 2017

What is Trigger and Types of trigger


What is Trigger and Types of trigger

TRIGGERS : DATABASE OBJECTS USED TO STORE ANY TYPE OF T-SQL STATEMENT.
THE STATEMENTS ARE AUTO EXECUTED IN RESPONSE TO DML & DDL EVENTS

>>>>DML EVENTS : INSERT, UPDATE, DELETE
TRIGGERS DEFINED ON THESE DML EVENTS ARE CALLED "DML TRIGGERS"

TYPES OF DML TRIGGERS:\

1. FOR / AFTER TRIGGERS : Trigger code executed in ADDITION to actual operation
2. INSTEAD OF TRIGGERS : Trigger code executed in PLACE OF actual operation

DURING DML TRIGGERS : TWO MEMORY ARE AUTO CREATED. 

ONE FOR AUDITTING THE ROW(S) BEING INSERTED INTO TABLE : "inserted" memory table.
ONE FOR AUDITTING THE ROW(S) BEING REMOVED FROM TABLE : "deleted" memory table.
-------------------------------------------------------------------------------------

>>>>DDL EVENTS : CREATE, ALTER, DROP
TRIGGERS DEFINED ON THESE DDL EVENTS ARE CALLED "DDL TRIGGERS"

TYPES OF DML TRIGGERS:

1. FOR / AFTER TRIGGERS : Trigger code executed in ADDITION to actual operation


CREATE DATABASE TRIGGER_DB 

USE TRIGGER_DB


CREATE TABLE RESERVATION_TABLE
(
Railway_Code nvarchar(30),
No_Seats int,
ClassofCode varchar(10)
)


INSERT INTO RESERVATION_TABLE VALUES ('Ra01', 24, 'XX')


CREATE TRIGGER Trig
ON RESERVATION_TABLE
FOR -- IN ADDITION TO
INSERT, UPDATE, DELETE 
AS
PRINT 'GIVEN INSERT / UPDATE / DELETE OPERATION IS SUCCESSFUL'

-- TEST THE TRIGGER:

INSERT INTO RESERVATION_TABLE VALUES ('AI01', 11, 'EX')  -- TRIGGER AUTO EXECUTES.

INSERT INTO RESERVATION_TABLE VALUES ('AI02', 22, 'EX')  -- TRIGGER AUTO EXECUTES.



REQUIREMENT: RESERVATIONS ONCE CONFIRMED CANNOT BE MODIFIED / CANCELLED.

REQUIREMENT: HOW TO DISABLE UPDATES & DELETES ON THE TABLE?

CREATE TRIGGER Trig1
ON RESERVATION_TABLE
INSTEAD OF -- IN PLACE OF
UPDATE, DELETE
AS
PRINT 'RESERVATIONS ONCE CONFIRMED CANNOT BE MODIFIED/CANCELLED'


UPDATE RESERVATION_TABLE 
SET No_Seats = No_Seats - 1 
WHERE Railway_Code = 'AI01'


DELETE FROM RESERVATION_TABLE 

SELECT * FROM RESERVATION_TABLE 


SYNTAX FOR TRIGGER CREATION:
CREATE TRIGGER TRIG_NAME
ON TABLE / VIEW
FOR | INSTEAD OF
<DML EVENT(S): insert, update, delete>
AS
BEGIN
...............
..................
...............
END

PURPOSE OF TRIGGERS:

1. TO PEFORM ADDITIONAL OPERATIONS IN RESPONSE TO DML  / DDL EVENTS
2. TO PERFORM ALTERNATE OPERATIONS IN PLACE OF DML / DDL EVENTS
3. TO DISTRIBUTE DATA TO MULTIPLE TABLES

CREATE TABLE Rail
(
Railway_Code NVARCHAR(30),
SOURCE_CITY VARCHAR(30),
DEST_CITY VARCHAR(30)
)


CREATE VIEW VW_Rail_RESERVATION
AS
SELECT 
F.Railway_Code, F.SOURCE_CITY, F.DEST_CITY,
R.ClassofCode, R.No_Seats FROM RESERVATION_TABLE R
JOIN
Rail F
ON
R.Railway_Code = F.Railway_Code


-- ISSUE:
INSERT INTO VW_Rail_RESERVATION VALUES ('Ra01', 'HYB', 'NYC', 'XX', '9')

-- REASON: WE DID NOT SPECIFY THE LOGIC FOR VALUE DISTRIBUTION TO BASE TABLES

-- SOLUTION TO ABOVE ISSUE: TRIGGERS

-- PLAN: AUDIT EVERY ROW BEING INSERTED. SCAN ALL THE DATA. USING "INSERTED" TABLE
-- IDENTIFY RELEVANT DATA FOR FLIGHT TABLE, INSERT.
-- IDENTIFY RELEVANT DATA FOR RESERVATION TABLE, INSERT.

CREATE TRIGGER trigdatadistribution
ON VW_Rail_RESERVATION
INSTEAD OF 
INSERT
AS
BEGIN
DECLARE @Railway_Code NVARCHAR(30), @SRC VARCHAR(30), @DEST VARCHAR(30)
DECLARE @SEATS INT, @CLASS VARCHAR(30)
SELECT @Railway_Code = Railway_Code FROM INSERTED 
SELECT @SRC = SOURCE_CITY FROM INSERTED 
SELECT @DEST = DEST_CITY FROM INSERTED 
SELECT @SEATS = No_Seats FROM INSERTED 
SELECT @CLASS = ClassofCode FROM INSERTED 

INSERT INTO Rail VALUES (@Railway_Code,@SRC,@DEST) 
INSERT INTO RESERVATION_TABLE VALUES (@Railway_Code, @SEATS, @CLASS) 

END



INSERT INTO VW_Rail_RESERVATION VALUES ('Ra99', 'HYB', 'NYC', 'EX', '9')

INSERT INTO VW_Rail_RESERVATION VALUES ('Ra88', 'HYB8', 'NYC8', 'EX', '8')

SELECT * FROM Rail

SELECT * FROM RESERVATION_TABLE 


-- DATABASE SCOPED DDL TRIGGERS

CREATE TRIGGER TRIGER_DBLEVEL_DDL
ON DATABASE
FOR
CREATE_TABLE, ALTER_TABLE, DROP_FUNCTION, CREATE_TRIGGER, ALTER_PROCEDURE
AS
PRINT 'GIVEN DATABASE LEVEL DDL OPERATION IS SUCCESSFUL'

EX: 
                          ALTER TABLE FLIGHT ADD STS BIT

-- SERVER SCOPDED DDL TRIGGERS

CREATE TRIGGER TRIGER_SERVERLEVEL_DDL
ON ALL SERVER
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
PRINT 'GIVEN SERVER LEVEL DDL OPERATION IS SUCCESSFUL'



Read Complete

13 Dec 2017

Difference between delete and truncate in sql


Difference between delete and truncate in sql

create table tblstudents
(
std_id int,   
stdname varchar(30), 
age  tinyint,
gender char
)

query 2:  how to insert data into above table

insert tblstudents (std_id, stdname, age, gender)  values (1001, 'jeff',21, 'm')

insert tblstudents (std_id, stdname, age)  values (1002, 'john',null)


Delete  : To remove data from table.      
        
  Query:            delete from tblstudents



Truncate : to remove data from table. this is not logged by log file. 


Query:              Truncate table tblstudents



Drop    : To remove structure of table. 

Query:  drop table tblstudents



Read Complete

what is system database in sql server


How many types of system database:-

 SYSTEM DATABASES:  Once we install SQL Server, a set of FIVE system databases are AUTO created.

 A. MASTER  : Used to control connections and Client-Server TDS Packets including Other databases in the instance (server).

B. MODEL    : Used to define a template for new databases we create on the server. We can create upto 32767 databases in an instance.


C. MSDB  : Used to manage the server & databases. This database is used by DBAs & MSBI Deployment Managers for routine maintenance activities.

 Ex: SQL Server Jobs, Alerts, Emails, SSIS Packages....

D. TEMPDB   : Used to perform calculations, indexes, query tuning (making the queries run faster), temporary storage. TEMPORARY TABLES

E. RESOURCE DATABASE  : Read Only, hidden databases used to control all above system databases. This is started by SQL Server "Service".
Read Complete

12 Dec 2017

how to install sql server 2012 step by step

 install sql server 2012 step by step

Step 1: Open installation media in new window then right click on setup file to run the setup file.

 Step 2: Installation Center

Then Click on Installation section .



Step 3: Product Updates

Product  Updates to  SQL Server performance as my setup found one update of 22 MB .


Step 4: Install Setup Files

  Install Setup Files on this window, then you click on Install button to install the updates.


Step 5: Setup Support Rules

After successful then  setup will again run a check to ensure .


Step 6: Product Key

Product Key SQL Server you want to install on your machine with your product key and click Next.


Step 7: License Term

Accept the license by clicking on (I accept license terms. then ) Next click.


Step 8: Setup Role

 Setup Role like install SQL Server instance or install instance  By default it is select SQL Server Feature Installation.


Step 9: Components or Features to Install


Step 10: Installation Rules


Step 12: Instance Configuration

This step will  ask, how many type of instance you want to configure,   we can install Default or Named instance. The default instance is already installed.


Step 13: Disk Space requirement summary

This step, you will get disk space summary which will show how much disk space your instance will take on the machine.


Step 14: Server Configuration

This step you will find options to Service  Configuration. 


Step 15: Database Engine Configuration

This is the most important step because , you will configure your servers configuration, data directories and file stream options.

Data root directory.
System database directory,
User database log directory,
User database directory,
Temp data and log directories, and
Backup location


Step 16: Error Reporting to Microsoft.


Step 17: Installation Configuration Rule



Step 18: Installation Summary

you will get the summary of your installation .


Step 19:  Installation process Go!

Installation process will start.


Step 20: Installation completed

After successful installation you will get the following window. show you the components installed on your machine with (Succeeded) message .


Read Complete

Sql Server 2012 Free Download


SQL  Server Management Studio Express Free Download 


Download SQL server 2012  

Read Complete

7 Dec 2017

What is Constraints and types of Constraints



constraints :  conditions defined on table columns.

                                      ex: course_dur should be either 180 or 240
                                      ex: student age should be minimum 18

Types of constraints:

1. Null    : column can accept null values
Not null : column cannot accept null values

 2. unique : column does not accept duplicates but can accept upto 1 null values

 3. primary key  : column does not accept duplicates and cannot accept null 

values

4. foreign key : column accept values based on a different column in same/different table for table to table communication (references)

 ex: assume a courses table with few courses. students table should reference above courses table staff table should reference above courses table

5. check constraint : used to specify conditions on column values
                                    
 ex: student gender should be either 'm' or 'f'

6. default constraint : used to specify a default value.
                            applicable for inserts when we do not speficy column name & value

7. indentity property : used to generate a sequence of integer values  *


Read Complete

8 Nov 2017

How To Add Entity Framework Reference in Visual Studio 2012


How To Add Entity Framework Reference in Visual Studio 

Entity Framework is the access the data technology in asp.net ans.Entity framework create the data model.The Microsoft provide to Object/Relational Mapping(ORM) Framework
but call Entity Framework. Entity Framework syntax (.edmx).


Reference entity framework in project in asp.net:-

First of all we create the new project in visual studio and chooses the template according to your require. Go to the visual stdio then click on new project and click on add reference. 



2. In this slide click on add then add new item. this the second step of entity framework reference add. 


3. In this slid go to the visual c# then Select the  Data option and add the ADO.NET entity data model.then add name entity framework and syntax of entity .edmx then click on Add button.


4. Step four in you select the data format, but it the by default select (Generate data format), After select data click on next button.


5. Add new Connection in Entity framework.


6. Add  the server name and database name  then test the connection then enter.

7. Click on New connection.


8. Select the table and store procedure etc....


9. your entity data model is ready. entity data model show this type .

Read Complete

Popular Posts

Popular Posts

Translate

Total Pageviews