Drop multiple table with a naming pattern in sql server

Yesterday I got an email from my boss to drop all tables starting with naming pattern ‘DUMMY’ in test database. There are thousands of tables with naming pattern DUMMY in that database. It is very tedious task to delete all the tables’ one by one.

  

First I have to select all tables’ names from database with given naming pattern. Then fetching them one by one then applies the drop statement.

With the help of below sql script we can achieve the same

--Get the list DataBase name DECLARE @tmpdatabase table (ID int IDENTITY (1,1), tablename varchar(100)) --Insert table name in variable table Insert into @tmpdatabase SELECT NAME FROM SYS.TABLES WHERE name LIKE 'DUMMY%' DECLARE @MinID int=1, @MaxID int = (select Count(*) from @tmpdatabase) while (@MinID<=@MaxID) BEGIN declare @tName varchar(100) select @tName=tablename from @tmpdatabase where ID=@MinID DECLARE @s varchar(100) set @s= N'DROP TABLE ' + @tName exec (@s) set @MinID=@MinID+1 End

See the result

Now I am seeing it on the Test database

  

All tables which started with DUMMY is dropped.

Note:- Before dropping tables from database I strongly recommend you to take full backup of database.  By doing so make sure you are not going to lose any data.

Use of dot (.) in sql server

The dot (.) is used to separate database name, table name and column name when we use JOINs. The simple example is

select cust.customer_name, sum (detail.total_spend) as total_spend from
customers as cust inner join sales as detail
on cust.customer_id=detail.customer_id
group by cust.customer_name

Ø  When a single dot is used, by default the current user is considered so it becomes username.tablename.

Ø  When two dots are used, by default the current database and user are considered so it becomes dbname.username.tablename.

Ø  When three dots are used, by default the current server, database and user are considered so it becomes servername.dbname.username.tablename

Select * from . Customers 
select * from .. Customers 
select * from … customers 

Temporary Stored Procedure in sql server

Temporary stored procedures on Microsoft SQL Server are prefixed with pound sign #. One pound sign means that it’s temporary within the session, two pound signs ## means it’s a global temporary procedure, which can be called by any connection to the SQL server during its lifetime.

A local temporary stored procedure is available only in the current session and is dropped when the session is closed. A global temporary stored procedure is visible to all sessions and is dropped when the session of the user that created it is closed. If there are any executing versions of the global stored procedure when the creator session is closed, those are allowed to complete, but once they are done and the creator session is closed, no further execution of the global temporary stored procedure is allowed.

See the example

Local stored procedure

create proc #GetEmpDetails As Begin select EmpID,EmpName,EmpAdd from Test.dbo.Emp End

 

SP created successfully.

We can see this on Temp db

 

Now I am executing this SP

exec #GetEmpDetails

It will be dropped when the session is closed.

Global stored procedure

Global Stored Procedure is start from ##.

create proc ##GetEmpDetails As Begin select EmpID,EmpName,EmpAdd from Test.dbo.Emp End

See the out put

exec ##GetEmpDetails

 It can be used on all session. See the example

 

It is visible to all sessions and is dropped when the session of the user that created it is closed.

Find the Leap year

With the help of below sql script we can find the year is leap year or not.

DECLARE @year AS INT SET @year=2016 DECLARE @Result As int set @Result= ( select 365+ case when @year%400=0 then 1 when @year%100=0 then 0 when @year%4=0 then 1 else 0 end) select case when @Result=366 then 'Leap year' else 'Not Leap Year' End as 'Result'

See the result

  

I am changing the year. Taking 2017. See the result.

  

Extract only numeric from alphanumeric string

With the help of below sql script we can find the only numeric from the given string.

Declare @s varchar(100),@result varchar(100) set @s='bageshkumarsingh12346@gmail.com' set @result='' select  @result=@result+case when number like '[0-9]' then number else '' end     from     (          select substring(@s,number,1) as number from         (             select number from master..spt_values where type='p' and                    number between 1 and len(@s)         ) as t     ) as t select @result as Result

See the result

  

Find Duplicate Fields in a Table

A common scenario when querying tables is the need to find duplicate fields within the same table. To do this is simple; it requires utilizing the GROUP BY clause and counting the number of recurrences. For example, let’s take a employee table. Within the customers table, we want to find all the records where the Employee Names are the same. We also want to find which Employee Names are the same and count them.

Use below sql script

SELECT EmpName     ,DuplicateCount = COUNT(1) FROM [Emp] GROUP BY EmpName HAVING COUNT(1) > 1  -- more than one value ORDER BY COUNT(1) DESC

  

Difference between Scan and Seek in SQL Server

Scan

Ø  When search definition can’t point close on single row/ Range of rows to satisfy search predicates. It called as scan

Ø  SQL Server has to scan multiple pages to find range of rows which satisfy search predicates.

Ø  Scan is good if we have to retrieves all the rows from the table.

Ø  It reads every row in the table whether or not it qualifies

Ø  Scan cost is proportional to the total number of rows in the table

Ø  If Scan is table scan means table doesn’t have index

Ø  If table is having very small no. of rows and it’s not very frequently used, scan and seek will be almost same

Ø  When SQL Server does a scan it loads the complete object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs.

Ø  We never want to convert seek to scan but if  we just want it, then drop index

Ø  A scan means whole data irrespective of data we want

Ø  Scan can be possible with index or table

Ø  Scan is of 3 types

o   clustered index scan

o    non clustered index scan

o    table scan

Ø  An Non Clustered index scan is a complete scan of all the leaf pages in B tree to to find index key of cluster index

Ø  A clustered index scan is a complete scan of all data pages at leaf in a clustered index

Ø  No clustered index scan means lot fewer pages then in clustered index scan

Ø  Order of data doesn’t impact much

Ø  Fragmented data affects scan most as whole data need to be read from disk

Ø  Scan is generally not good, some queries it will use a table scan even when a useful index is present – this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good).

Ø  Table scan only appears for a heap like table without a clustered index. The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible

Ø  Clustered index scan is similar to table scan, just on a table that has clustered index. This operation reads the leaf pages of the clustered index, using the next and previous page pointers. Like with the table scan, this can be an expensive operation and should, wherever possible be avoided

Ø  Index scan means reading all the leaf pages of a non-clustered index using the next and previous page pointers to navigate. Because non-clustered indexes generally have fewer pages in the leaf than a clustered index, this operation is usually cheaper than a clustered index scan

Ø  This sometimes comes with lookups (Row \ Bookmark) , a part of scanning , Another index is used to over result of scan

Ø  We can see scan is horizontal arrow

Seek

Ø  When index definition can point close on single row/ Range of rows to satisfy search predicates. It called as Seek

Ø  SQL Server knows which page / Range of rows to read

Ø  Seek is used if we need selected rows from the table

Ø  It reads rows that qualify and pages that contain these qualifying rows

Ø  The cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Ø  Seek is always on index only

Ø  If table is having huge no. of records then seek will give huge performance impact.

Ø  It knows where in the index data is going to be, so goes directly to the part of the index that it needs and load to memory

Ø  A seek means that we are looking for specific value(s) and the index provides us with the best way to do it

Ø  Seek is always with index , DB engine search data in B tree

Ø  Seek is of two types

o   clustered index seek

o   non clustered index seek

Ø  An Non clustered index seek is a seek through the B-tree structure of a non-clustered index to find index key of cluster index leaf page, from the root down to the leaf

Ø  A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf

Ø  Non clustered index seek means only pages required for data address, where as in clustered index seek it only reads data pages.

Ø  Search is fast because data is stored in order based on the clustered index key

Ø  Fragmented data affects but not as compare to scan, as sql engine reed minimal required data.

Ø  seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table.

Ø  Seek is not possible without index

Ø  Clustered index seek uses the clustered index’s b-tree structure. The seek starts at the root of the tree and navigates down the levels of the index until it reached the leaf page(s) with the desired data. This operation also appears when a partial scan of the table is done, when the index’s tree is used to locate a page, and the index is scanned from that point until another point in the table (possibly the end).

Ø  Non-clustered index seek is the same as the clustered index seek, just using a non-clustered index

Ø  In a seek not all of the index is considered. Only one used index is enough

Ø  Seek is vertical arrow explain its nature of work

Difference between SSIS features of SQL Server 2008 R2 & SQL Server 2012

·         Server Environments

·         SSISDB Catalog

·         Project Connection Managers

·         Offline Connection Managers

·         Flat File Connection Manager Changes

·         Parameters

·         Comparing and Merging Packages

·         Undo/Redo in SSIS Designer

·         Column Mapping

·         Script Task and Script Component