Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used for managing and storing large amounts of data, and can be used for a variety of applications, including data warehousing, business intelligence, and online transaction processing.
table not used or accesses in last 60 days
Query
SELECT
t.name AS TableName,
t.create_date,
COALESCE(i.last_user_seek, i.last_user_scan, i.last_user_lookup, i.last_user_update) AS LastAccess
FROM
sys.tables t
LEFT JOIN
sys.dm_db_index_usag
You can delete all tables from the database that start with HR using the following SQL script:
sql
Copy code
DECLARE @TableName NVARCHAR(128) DECLARE @Sql NVARCHAR(MAX) -- Cursor to iterate over all tables starting with HR DECLARE table_cursor CURSOR FO
Introduction:
MS SQL Server, a relational database management system developed by Microsoft, is a robust and widely-used software solution that supports the storage, retrieval, and management of data. Behind its exceptional performance and functionality lies a comple
Problem:
Sometime, have any value, but we are unable to find, from where this value is displayed. to know what is table name and column name from where this value is beiing displayed.
we have written some ms sql script, that will take paramater as KeyWords or Any
use testshop2
go
-- how to find changes in database | find modifications in database
Declare @kw nvarchar(500)=a%
--Query to search last executed sps in db
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_sta
How to caculate Date of Age in ms sql server?
-- Step) 1
CREATE FUNCTION [dbo].[CalculateAge]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate @DOB )
RETURN -1
-- If a DOB is supplied after the comparison
How to use case statement in ms sql server?
select
case
when Role_ID=1 then admin
when Role_ID=2 then other
when Role_ID=3 then Nine
end
Role_ID
from RoleInterfaces
how to use if else in ms sql server
IF ( 1 1) ---- Some Expression
BEGIN
PRINT One is equal to One
END
ELSE IF (2=3)
BEGIN
PRINT First expression was not true
END
else
begin
Print else
end
The backend version is not supported to design database diagrams or tables.
Solution:
Database was designed in new version, and if user tried to add new table or any new database diagram. then you will get
error message The backend version is not supported to des
how to use join with temp table
CREATE TABLE [dbo].[NormalTable](
[Id] [int] NULL,
[name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[NormalTable] ([Id], [name]) VALUES (1, Nrange1)
GO
INSERT [dbo].[NormalTable] ([Id], [name]) VALUES (2, NRange2)
GO
Error handline in sql script?
GO
-- Basic example of TRY...CATCH
BEGIN TRY
-- Generate a divide-by-zero error
SELECT
1 / 0 AS Error;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS
how to check tables and number of rows in each table in ms sql server.
SELECT [Tables].name AS [TableName],
SUM( [Partitions].[rows] ) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[objec
getting this error in ms sql sever The backend version is not supported to design database diagrams or tables.
Solution:
Database was designed in new version, and if user tried to add new table or any new database diagram. then you will get
error message The back
check which store procedure has more lines?
select t.sp_name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc
from
(
select o.name as sp_name,
(len(c.text) - len(replace(c.text, char(10), ))) as lines_of_code,
case when o.xtype = P then Sto
How to check which database table has more records?
Problem:
sometime, we are not sure, which table has how much data. which table is consuming more space.
Solution:
you can run below sql script in sql server query editor, it will display table name and nu
--how to check modification in ms sql database
-- option
SELECT name, modify_date from sys.objects where type =U order by modify_date desc
-- option 1
select
object_name(object_id) as OBJ_NAME, *
from
sys.dm_db_index_usage_stats
where
database
USE [saudiphone]
GO
/***** Object: StoredProcedure [dbo].[Product_LookUp_MostViewedProductList] Script Date: 6/28/2018 2:10:16 PM *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[Product_LookUp_MostViewedProductList]
as
begin
selec
USE [saudiphone]
GO
/****** Object: StoredProcedure [dbo].[Product_LookUp_RecentViewedProduct] Script Date: 6/28/2018 2:26:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Product_LookUp_RecentViewedProduct]
as
begin
SELECT
USE [saudiphone]
GO
/****** Object: StoredProcedure [dbo].[Product_LookUp_SubCategoryID] Script Date: 6/25/2018 4:58:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Product_LookUp_SubCategoryID]
@SubCategoryID int
as
begin
USE [saudiphone]
GO
/****** Object: StoredProcedure [dbo].[Product_LookUp_CategoryID] Script Date: 6/28/2018 2:22:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Product_LookUp_CategoryID]
@CategoryID int
as
begin
SELECT TO
To find an MS SQL Server DBA (Database Administrator), you can try the following methods:
Online Job Portals: Websites like LinkedIn, Indeed, Glassdoor, and Monster often have job postings specifically for DBAs, including those with expertise in MS SQL Server. You can
script all the stored procedures ONLY from SQL Server?
- Click on Database- Tasks- Generate Script- Select Specific Script (Store Procedures) Only- Proceeded and Save as sql file
Pivot is very usefull and important Operator in SQL Because if we want to convert Rows in Columns then we use Pivot Operator and One Column Define Differnt Result set
For Example
We have table Of Daily Income
ID
Vendor
IncomeDay
Inc
This Query special for Delete large amount of data of data base .For example there is 50 tables in Database and want to delete all record of 50 tables then we use this querey
sp_msforeachtable delete ?
Note:
Be carefull this Query only Delete for all ta
Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query.
Queries Examples
1.
Select (age) AsTotalA
Between
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Queries Examples
1.
Select (Age)Fromusers_Tbl
WhereAge
Wildcard characters are used with theSQL LIKEoperator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
% - The percent sign represents zero, one, or m
The REPLACE() function replaces all occurrences of a specified string.
Note:The REPLACE() function performs a case-sensitive replacement.
For Example we have a string [First Name] we want to change in [Last Name ] means Replace first into last
SELECT Replace (F
Char index () function Return the Locationof substring in String,If we have a string value like this [Education management system]and want to return location of substring [Cati] in String then we use char Index To get result like this
Write this Query
SELECT CharI
Cross Join :
Cross join Produce a Result which is the number of rows in the first table multiplied by rows in the second tableCross joins are used to return every combination of rows from two tables, this sometimes called aCartesian product.
For Example
There i
The DATEDIFF() function returns the difference between two date values, based on the interval specified.
Queries Examples
1.Diiference Between Years
SELECT DATEDIFF(year, 2011/08/25,2017/08/25) AS Years;
It give use Result Of Difference of year
-----
1.Left () Function
The LEFT() function extracts a substring from a string (starting from left).
Queries Examples
If we want to Trim left side string words by Numbers
1.
SelectLeft(FirstName,3)AsFirstName
Fromusers_tbl
When we Execute the Query it g
Left Trim Right Trim
Definition
The LTRIM RTRIM function removes leading spaces from a string.
1 Left Trim
1.Select (Hello)
There is a Multiple Spaces in the left side of string if we want to remove it we use [Left trim]Write this Function
Select L
1.setNocounton;
Set no count On use for stop count in Queriese because when we execute a Query or Store Procedures it take a time to Count of Affected Rows when we use [setNocounton] in Query it stop the count of affected Rows in this way the Execution time will be D
Indexes :
If you want your sql queries work and Execute fast we have tips for your First our Topic is Index There is ThreeTypes of Index
1.Clustered Index
2.Non Clustered Index
3.Unique Index
When we create a table in SQL Clustered index auto apply on Pri
WITH DeleteDuplicate_Rows
AS (
SELECT
E.Name
,Row_Number()Over(Partition By E.name Order by Name )AS DuplicateRows
FROM Employee E)
Delete from DeleteDuplicate_Rows
Where DuplicateRows 1
WITH FirstEmployeeOf_EachDepartment
AS
(
SELECT E.Employee_ID , E.Name,E.Age , E.Department_ID,
ROW_NUMBER () OVER (PARTITION BY E.NAME ORDER BY E.Employee_ID Desc) AS RN
FROM Employee E)
SELECT F.Employee_ID,F.Name,f.Age,f.Department_ID FROM FirstEmploy
Convert() Function
When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or
other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things.
The CONVERT
How to cast data types
The Cast() function is used to convert a data type variable or data from one data type to another data type.
The Cast() function provides a data type to a dynamic parameter
For Example
There is four value and her data type is Varchar i
Advantages and Workings of DBMS
1)Redundancy Control:
A data which is stored multiple times at different locations is a redundant data.
Redundancy leads to usage of more disk space, inconsistency and duplication of effort.
When data has to be updated at
What is a Trigger?
A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBAs (Data Base Administrators) to create additional relationships between separate databases.
In other ways, a trigg
How to change column data type max length in ms sql script
Solution
alter table Product
alter column ProductTitle varchar(5000)
alter table Product
alter column PageTitle varchar(5000)
alter table Product
alter column Photo varchar(500
NORMALIZATION
Database normalization,
Simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancyand improve data integrity. Normalization is also the proces
Keys
What is Key?
Keys are fields in a table which participate in below activities in RDBMS systems:
To create relationships between two tables.
To maintain uniqueness in a table.
To keep consistent and valid data in database.
M
INTERSECT
TheSQL INTERSECTclause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This meansINTERSECTreturns only common rows returned by
Sql server Reporting services (SSRS) is a server based report desighner service from microsoft .It is a part of suite of microsoft sql services Including (SSIS)
Sql server Integeration services and (SSAS) Sql server Analysis services
Microsoft SSRS tool help to create
Store Procedures
Definition
Astored procedureis a set of Structured Query Language (SQL) statements with an assigned name, which arestoredin a relational database management system as a group, so it can be reused and shared by multiple progr
Views
What is View?
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a tab
Common Table Expression
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it i
Triggers
Definition
Atriggeris a special kind of stored procedure that automatically executes when an event occurs in the databaseserver. DMLtriggersexecute when a user tries to modify data through a data manipulation language (DML) event. DML events a
Union Union ALL
1.Definition of Union
InUNIONclause combines the results of twoSQLqueries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplica
Joins
An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values
Types Of Database
Established:
1.DB2
Since the 1970s, when IBM Research invented the Relational Model and the Structured Query Language (SQL), IBM has developed a complete family of data servers. Development started on mainframe platforms such as Virtual Mac
How to use cursor, How to update table using cursor, How to get and set in cursor in MS Sql Server 2008,2008R2, 2012,2014
DECLARE @userid bigint
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Users_ID
FROM IE_User
OPEN @getAccountID
FETCH NE
History Of Database
Nowadays we are used to having hundreds of gigabytes of storage capacity in our computers. Even tiny MP3 players and other hand held devices usually have several gigabytes of storage. This was pure science fiction only a few decades ago.
DBCC checkident (Customer, RESEED, 3)
Reseed the Identity field
You can also reseed the identity field value. By doing so identity field values will start with a new defined value.
Suppose you want to reseed the Customer table ID field from 3 then the new rec
FULL OUTER JOIN:
Another type of join is called a SQL Server FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
Syntax:
The syntax for the FULL OUTER JOIN in
RIGHT OUTER JOIN:
Another type of join is called a SQL Server RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition andonlythose rows from the other table where the joined fields are equal (join condition is met)
LEFT OUTER JOIN:
Another type of join is called a SQL Server LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition andonlythose rows from the other table where the joined fields are equal (join condition is met).
Overview:
The real power of stored procedures is the ability to pass parameters and have the stored procedure handle the differing requests that are made. In this topic we will look at passing parameter values to a stored procedure.
Explanation:
Just like you h
The date time function is used in the sql server that shows the query of inserting the functions likeCURRENT_TIMESTAMP function, DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE,GETTUTCDATE, MONTH AND YEAR.
This function shows the expression which starts with the
SQL Serveris a relational database technology developed by Microsoft.
Transact-SQL(T-SQL) is an extension of SQL that is used in SQL Server. Transact-SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL.
Ou
Types of Database
Established:
1.DB2
Since the 1970s, when IBM Research invented the Relational Model and the Structured Query Language (SQL), IBM has developed a complete family of data servers. Development started on mainframe platforms such as Virtual Machin
Executing Stored Procedure:
Although executing a stored procedure can be as easy as listing it on a line by itself in a T-SQL command batch, you should make a habit of prefixing all stored procedure calls with the EXEC keyword, like this:
EXEC dbo.sp_who
Stor
Aqueryis an inquiry into the database using theSELECTstatement. A query is used to extract data from the database in a readable format according to the users request.
For instance, if you have an employee table, you might issue a SQL statement that returns the employ
Amany-to-many relationshipoccurs when multiplerecordsin atableare associated with multiple records in another table.
For example:
a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purcha
The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created usingPrimary key-Foreign key relationship.
In a one-to-many relationship, onerec
One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created usingPrimary key-Unique foreign key constraints.
In a one-to-on
A table data field is a table in which it is shown as the excel sheet in the back side of the database table and it shows that user has to enter all the fields related to data types.
A data type defines what kind of value a column can hold: integer data, character
Astored procedureis a named collection ofSQLstatements and procedural logic i.e, compiled, verified andstoredin theserverdatabase. Astored procedureis typically treated like other database objects and controlled throughserversecurity mechanism.
The commands for st
What is an Index in SQL Server?
An index is a performance-tuning method of allowing faster retrieval of records and an index creates an entry for each value that appears in the indexed columns.
Unique:
Optional. Indicates that the combination of values in the i
In SQL Server, the Convert function converts an expression from one datatype to another datatype.
Syntax:
The syntax for the CONVERT function in SQL Server (Transact-SQL) is:
CONVERT (type [ (length) ], expression [ , style ] )
Type:
The datatype t
In the SQL Server, the cast function converts an expression from one datatype to another datatype.
Expression:
The value to convert to another datatype.
Type:
The datatype that you wish to convert expression to and it can be one of the following are:
A unique key is a set of one or more than one fields columns of a table that uniquely identify a record in a database table and the primary key which it can accept one null value but it cannot have duplicate values. The unique key and primary key that they both provide
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables and it acts as a cross reference between tables. Because it references the primary key of another table and thereby establishing a link between t
A primary key is a special relational database column or combinations of columns designed to uniquely identify all the table records.
It must contain a unique value for each row of data
It cannot contain null values.
The primary key is inserted in the first co
A table is a set of data elements(values) using a model vertical columns and horizontal columns. The cell being the unit where a row and column interacts that can have a specified number of columns but can have any number of rows.
The table names
The table is us
