-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

TSQL Circle Live

Arun Kumar has over 8 years' extensive working experience in database query development, design, supporting many different versions of MS-SQL. He has worked to develop efficient reports, index optimization for complex queries, and has finance and operation domain experience. He has also worked with SPLUNK and enjoys training technical and non-technical colleagues.

Missing index with create statements


Missing index :- Scripts will help you to get all the missing index and prepare script for new index.

/*
Description:- This Query will provide you detail of missing indexes on a table and also 
 prepare sql script for new index
*/
SELECT
DISTINCT
   @@SERVERNAME AS [ServerName]
   ,…

Read more

0 comments, 428 reads

Posted in TSQL Circle Live on 10 April 2017

How we can determine table column dependencies within a SQL database?

I came a situation during some analysis where i had to figure out all dependency of a column across database. This also required when we do some changes on table columns and wanted to know the impact of such changes. One way is that  we can use SYSCOMMENTS table.  It is…

Read more

0 comments, 209 reads

Posted in TSQL Circle Live on 29 December 2016

Column and table constraints

Column and Table Constraints

Constraints can be column constraints or table constraints.
A column constraint is specified as part of a column definition and applies only to that column.

A table constraint is declared independently from a column definition and can apply to more than one column in a table.…

Read more

0 comments, 139 reads

Posted in TSQL Circle Live on 23 December 2016

What is Constraints in TSQL?

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.Constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses…

Read more

0 comments, 111 reads

Posted in TSQL Circle Live on 23 December 2016

What is – DML, DDL, DCL and TCL in TSQL?

In transact SQL , different type of activity performed ,like we can create database , manipulate date , update, delete even control access to database or database objects.

In some case we also need to secure data or control data so we can restrict unauthorized access. All these activity categorized…

Read more

0 comments, 419 reads

Posted in TSQL Circle Live on 19 December 2016

Combining statement with conditional operators

Operators “AND”, “OR”
Sometime we need to combine two or more TSQL statement based on some conditions, that we can achieve using Conditional operator, Boolean   “AND operator” and “OR operator”

Boolean Operator: AND (&), OR (||)

Conditional Operator:-  
1.       GREATER THEN EQUAL    (>=) 
                X>Y;
2.       LESS THEN…

Read more

0 comments, 82 reads

Posted in TSQL Circle Live on 12 December 2016

Aggregate Functions

Aggregate Functions

MIN
Returns the smallest value in a given column
MAX
Returns the largest value in a given column
SUM
Returns the sum of the numeric values in a given column
AVG
Returns the average value of a given column
COUNT
Returns the total number of values in a…

Read more

0 comments, 61 reads

Posted in TSQL Circle Live on 11 December 2016

TSQLCircleLive: What will be the result of the query below? Explai...

TSQLCircleLive: What will be the result of the query below? Explai...: select case when null = null then 'True' else 'False' end as ResultSet;

Read more

0 comments, 140 reads

Posted in TSQL Circle Live on 27 September 2016

What will be the result of the query below? Explain your answer?

select case when null = null then 'True' else 'False' end as ResultSet;

Read more

0 comments, 56 reads

Posted in TSQL Circle Live on 26 September 2016

Different types of ANSI standard joins

INNER JOIN (SIMPLE JOIN): This returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): This returns all rows from the left table, and the…

Read more

0 comments, 44 reads

Posted in TSQL Circle Live on 26 September 2016

What does UNION do or what result set UNION return ? What is the difference between UNION and UNION ALL?



UNION merges the contents of two structurally-compatible tables into a single combined table. This return unique records from all the table. 

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance…

Read more

0 comments, 64 reads

Posted in TSQL Circle Live on 26 September 2016

SPACE

USE CASE: When we are working on TSQL or some text based scripting we may need some extra space in between two string, we can add space without space function as well but it is possible we may miss to put space in between string or text. If we have…

Read more

0 comments, 68 reads

Posted in TSQL Circle Live on 4 September 2016

STUFF

Use case when we are working on TSQL or Query ,some time we came such a situation when we need to replace a set of string into another set of string, this can be anything like string, character, numeric or special character. We can handle such situation using STUFF function.

Read more

0 comments, 44 reads

Posted in TSQL Circle Live on 4 September 2016

Common uses of stored Procedure sp_MSforeachtable

We will get the brief idea about undocumented procedure sp_MSforeachtable of the Master database.

sp_MSforeachtable can be used to loop through all the tables in your databases.

Some of the basic usages of this stored procedures are:-

1.     Display the size of all tables in a database

USE NORTHWIND

Read more

0 comments, 409 reads

Posted in TSQL Circle Live on 9 May 2016

Get each table space and their rows count

Sometime we need to know how much table space are used to store the data and also wish to know the number of rows stored in it, this query help you to get all the detail.


SELECT
 SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
 reserved_page_count * 8 as space_used_kb,
 row_count

Read more

0 comments, 63 reads

Posted in TSQL Circle Live on 11 April 2016

Generate list of all Month name


DECLARE @year INT
SET @year = 2016

;WITH months AS(
    SELECT 1 AS Mnth, DATENAME(MONTH, CAST(@year*100+1 AS VARCHAR) + '01')  AS monthname
    UNION ALL
    SELECT Mnth+1, DATENAME(MONTH, CAST(@year*100+(Mnth+1) AS VARCHAR) + '01') FROM months WHERE Mnth < 12
)
SELECT * FROM months;

Read more

0 comments, 85 reads

Posted in TSQL Circle Live on 11 April 2016

SQL Create Table


SQL CREATE TABLE
The CREATE TABLE statement is used to create a table in a database to stored data.
Tables are organized into rows and columns; and each table must have a name.
SQL CREATE TABLE Syntax
CREATE TABLE tablename
(
columnname1 datatype(size) [null | Not…

Read more

0 comments, 59 reads

Posted in TSQL Circle Live on 8 February 2016

SQL Basics for Beginner

SQL Basics
An instruction to a database to combine data from more than one table.
A SQL join combines records from two 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

Read more

1 comments, 56 reads

Posted in TSQL Circle Live on 8 February 2016

What is an Identity?

  •         Identity (or AutoNumber) is a column that automatically generates numeric values.
  •          A start and increment value can be set, but most DBA leave these at 1.
  •          A GUID column also generates numbers; the value of this…

Read more

0 comments, 35 reads

Posted in TSQL Circle Live on 26 January 2016

What is Normalization in SQL Server?


In relational database design, the process of organizing data to minimize redundancy is called normalization. It usually involves dividing a database into 2 or more tables and defining relationships between tables. Objective is to isolate data so that additions, deletions, and modifications can be made in just one table. 


Benefits:-…

Read more

0 comments, 70 reads

Posted in TSQL Circle Live on 26 January 2016

Older posts