We a can get the list of all tables of a database in different ways : -
1.
SELECT * FROM sys.TABLES
2.
SELECT * FROM sysobjects WHERE TYPE='U'
3.
SELECT * FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Popularity: 3% [?]
To view the existing triggers in the database
SELECT * FROM sys.triggers OR SELECT * FROM sysobjects WHERE xtype='TR'
To view the existing triggers in a specified table
sp_helptrigger tablename
To drop a trigger
DROP TRIGGER triggername
To drop more triggers at a time
DROP TRIGGER [trigger1],[trigger2],[trigger3]...[triggern]
Popularity: 4% [?]
Sql Case Function :
Sql Case Function has two Types.
Simple Function :
Syntax :
Example for Simple Function:
Select
state,
case @state
when ‘IND’ then ‘INDIA’
when ‘PAK’ then ‘Pakistan’
when ‘RSA’ then ‘South Africa’
when ‘Lanka’ then ‘Sri Lanka’
end as State Name
from State_name
Result:
State State_name
==== =========
IND INDIA
Searched Function :
Syntax :
Example for Searched Function:
Select
state,
case
when state in (‘IND’,'I’) then ‘INDIA’
when state in (‘PAK’,'P’) then ‘Pakistan’
when state in (‘RSA’,'SA’) then ‘South Africa’
when state in (‘Lanka’,'SL’) then ‘Sri Lanka’
end as State Name
from State_name
Result :
State State Name
==== =========
I INDIA
IND INDIA
PAK Pakistan
RSA South Africa
SL Sri Lanka
Popularity: 21% [?]
SELECT INTO statement can be used to create backup copy of a table.This sql statement selects records from one table according to the selection we make and inserts the result set into another new table with the table name we specify.
Syntax given below is used to make a exact copy of the table.That is it creates the table with the same no of columns and all records in the old table.
SELECT *
INTO new_table_name
FROM old_table_name
select * into employee_backup from employee
In this example a exact copy of the table employee is backed up as a new table employee_backup.
Syntax given below is used to make a new table with selected columns from old table.
SELECT column_name(s)
INTO new_table_name
FROM old_table_name
select employee_id,employee_name into employee_backup from employee
In this example a copy of the table employee with only columns “employee_id,employee_name” is backed up as a new table employee_backup.
Popularity: 3% [?]
Reduce network traffic: You have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
Caching query plan: The first time the stored procedure is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
Ability to use output parameters: If you send inline SQL that returns one row, you can only get back a recordset. With stored procedures you can get them back as output parameters, which is considerably faster.
Permissions: When you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a stored procedure
Separation of logic: Remove the SQL-generating code and segregate it in the database.
Ability to edit without recompiling: This can be controversial. You can edit the SQL in a sproc without having to recompile the application.
Find where a table is used: With stored procedures, if you want to find all SQL statements referencing a particular table, you can export the stored procedure code and search it. This is much easier than trying to find it in code.
Optimization: It’s easier for a DBA to optimize the SQL and tune the database when stored procedures are used. It’s easier to find missing indexes and such.
SQL injection attacks: Properly written inline SQL can defend against attacks, but stored procedures are better for this protection.
Popularity: 1% [?]
1.you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function.
2.SUM(..) OVER(..) is most useful for calculating a Total or percentage of a total for each row
3.SUM() function works with any of the other aggregate functions as well, such as MIN() or AVG()
Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
select
StudentId,
StudentName,
Subject,
Mark,
sum(Mark) OVER (Partition by StudentId) as Total
from
Student
Output
=====
StudentId StudentName Subject Mark Total
======= ========== ===== ==== =====
1 Raja Physics 80 240
1 Raja Maths 90 240
1 Raja Biology 70 240
2 Sam Physics 90 150
2 Sam Maths 60 150
Popularity: 3% [?]
Database Creation :
Table Creation :
Procedure Creation :
INSERT INTO EMPINFO(EMPID,EMPNAME,EMPDEPT,EMPSAL,EMPADDRESS,ACTIVE)VALUES @iEMPID,@vEMPNAME,@vEMPDEPT,@iEMPSAL,@vEMPADDRESS,@cACTIVE)
END
ELSE
BEGIN
UPDATE EMPINFO SET EMPNAME = @vEMPNAME,EMPDEPT = @vEMPDEPT,EMPSAL = @iEMPSAL, EMPADDRESS = @vEMPADDRESS,ACTIVE = @cACTIVE WHERE EMPID = @iEMPID
END
END
Popularity: 2% [?]
If the collation property of a data base is
1.Latin1_General_BIN its case-sensitive
2.SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AI its case-insensitive
There are two ways to find out the SQL Server Database Collation
1. Using DATABASEPROPERTYEX
DATABASEPROPERTYEX gets the current setting of the specified database option or property for the specified database.
SELECT DATABASEPROPERTYEX(‘testdb’, ‘Collation’)
This returns the testdb Database Collation property value say if this testdb is a case-sensitive database then it returns
Latin1_General_BIN
2.Using Sql Server Management Studio
Step 1 : Open Sql Server Management Studio.
Step 2 : Right Click on database and click its properties
After clicking the properties you get a database properties modal box such as
The red box shows the collation property value of that database.
Popularity: 1% [?]
When we are connecting sql server using windows authentication then we do not need to give the user name and pwd in the connection string. We have to provide the Integrated Security property to true.
Note : If your sql server is of windows authentication and if you dont provide Integrated Security you may get an error such as follows
Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.
Popularity: 1% [?]
When you want to create a stored procedure dynamically,we can create a stored procedure within a stored procedure.
/*setting the stored procedure name with spname passed*/
SET @ProcedureName = @spname
/*setting the stored procedure name with spname passed*/
/*Nothing will be done if procedure name is empty*/
IF LTRIM(RTRIM(@ProcedureName)) = ”
BEGIN
RETURN
END
/*Nothing will be done if procedure name is empty*/
/*Creating the stored procedure*/
SET @Exe = ‘CREATE PROC ‘ + @ProcedureName + CHAR(13) +
‘AS’ + CHAR(13) + CHAR(13) +
‘BEGIN’ + CHAR(13) +
‘SELECT * FROM EMPLOYEE’+ CHAR(13) +
‘END’
/*Creating the stored procedure*/
/*Executing the created stored procedure*/
EXEC(@Exe)
/*Executing the created stored procedure*/
END
/*Executing procCreateProcedure to creat new stored procedure*/
EXEC procCreateProcedure ‘procTest’
/*Executing procCreateProcedure to creat new stored procedure*/
now execute and see the stored procedure by name you passed.
EXEC procTest
Here in this example a new stored procedure with name ‘procTest’ which is passed in ‘procCreateProcedure’ is created and employee table is fectched when ‘procTest’ stored procedure is executed.
Popularity: 1% [?]