All Articles

SQL 2016 Features News

1] Stretch Database Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the cloud(Microsoft Azure). This new feature gives you the benefit of presenting the data as a single data store while behind the scenes allowing you the flexibility to segregate active andContinue reading “SQL 2016 Features News”

SQL – Index Rebuild / Index Reorganize

Index Rebuild will drop the existing Index and it will recreates the index. ALTER INDEX index_name ON databasename.tablename REBUILD Index Reorganize physically reorganizes the leaf nodes of the index. ALTER INDEX index_name ON databasename.tablename REORGANIZE Index should be rebuild when index fragmentation is most of the time great than 40%. Index should be reorganized whenContinue reading “SQL – Index Rebuild / Index Reorganize”

SQL – Search Text in Stored Procedure

There is below few questions which will ask you while Interview or Any time …How can I find if particular table is being used in the stored procedure?How to search in stored procedures?How can I do dependency check for objects in stored procedure without using sp_depends? The same feature can be implemented using following script.Continue reading “SQL – Search Text in Stored Procedure”

SQL – Temp Table v/s Temp Variable

Temp Tables are originated for the storage and it’s manipulation of temporal data. Also its stored physically created in tempDB. Temp Variables are originated for returning date-sets from table-valued functions. Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not. Temp Table create using SELECT INTO, which can be quickerContinue reading “SQL – Temp Table v/s Temp Variable”

Identify Locking SPs

In a previous section we learn about how to get Blocking SPs, In Todays section we seen how to identify locking . Locking is integral part of successful transactions of RDBMS. It is important in SQL Server that transactions pass the ACID test: Atomic – the transaction performs in an all-or-nothing fashion Consistent Isolated –Continue reading “Identify Locking SPs”

SQL – Unique Index and Unique Constraint

Unique Index and Unique Constraint both are the same in terms of working in Tables. They achieve same goal. Performance wise also both are same. —Unique ConstraintALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE NONCLUSTERED(column_names) ON [PRIMARY] —Unique IndexCREATE UNIQUE NONCLUSTERED INDEX index_name ON table_name(column_name) ON [PRIMARY] In both syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain theContinue reading “SQL – Unique Index and Unique Constraint”

SQL – DISTINCT and GROUP BY Difference

Distinct and Group By executed the same result as well as query plan of execution.Group BY clause is used whenever aggregate functions are used.Distinct use to remove the duplicates and it can’t be use in aggregate functions. examples for DISTINCTSELECT DISTINCT col1FROM table1 examples for GROUP BY SELECT col1FROM table1GROUP BY col1 both query willContinue reading “SQL – DISTINCT and GROUP BY Difference”

SQL – Find number Rows, Columns for each table in the database

From below query you will get the number of data present in each table of databases. Most consume table data. –created the temp table to store the tables data sizeCREATE TABLE #DBEachTable (TableName sysname ,RowCounts INT,ReservedSize VARCHAR(50),DataSize VARCHAR(50),IndexSize VARCHAR(50),UnusedSize VARCHAR(50)) –insert the tables data in created temp tableINSERT #DBEachTableEXEC sp_msforeachtable ‘sp_spaceused ”?”’ –fetching the eachContinue reading “SQL – Find number Rows, Columns for each table in the database”

SQL – Display Primary and Foreign Key Relationships Table With Their Constraints

In below query, We can view the relationships between more than one table as primary and foreign key … SELECT PK_Table = PK.TABLE_NAME,PK_Column = PT.COLUMN_NAME,K_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,Constraint_Name = C.CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAMEINNER JOINContinue reading “SQL – Display Primary and Foreign Key Relationships Table With Their Constraints”

SQL BASIC COMMANDS

In below sections there are basics sql queries about SELECT , WHERE , AND OR NOT , INSERT … There are other query commands also those we will see in the Next blog … SELECT Select command is use for to get the data from the table Example: SELECT * FROM tablename The above exampleContinue reading “SQL BASIC COMMANDS”