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

Popular Posts

Popular Posts

Translate

Total Pageviews