June 17, 2017

file extensions available in Tableau



Different file extensions available in Tableau


  • Tableau Workbook (.twb).
  •  Tableau Packaged Workbook (.twbx).
  • Tableau Datasource (.tds).
  • Tableau Packaged Datasource (.tdsx).
  • Tableau Data extract (.tde).
  • Tableau Bookmark (.tdm).
  • Tableau Map Source (.tms).
  • Tableau Preferences (.tps)

January 19, 2017

Get list of tables used in Stored procedure


SELECT DISTINCT        so.ID, so.name AS 'Procedure Name' , oo.name AS 'Table Name'
FROM sysdepends d, sysobjects so, sysobjects oo
WHERE    so.id=d.id
      AND so.name= 'Stored_Proc_Name'   -- Enter Your stored Procedure Name here
      AND oo.id=d.depid
ORDER BY so.NAME,oo.NAME


January 13, 2017

Find a sting in SQL table

CREATE PROCEDURE dbo.FindStringInTable @stringToFind VARCHAR(100)
AS

BEGIN TRY
 
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [dbo].[TableName] WHERE '
          
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   
   FROM INFORMATION_SCHEMA.COLUMNS
   
   WHERE TABLE_SCHEMA = 'dbo'
   
   AND TABLE_NAME = 'TableName' -- This condition can be removed.
   
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   
   EXEC (@sqlCommand)
   
   PRINT @sqlCommand

END TRY

BEGIN CATCH
   PRINT 'Unknown Error !!'

END CATCH

 Execute FindStringInTable '[String to Search]'

August 6, 2016

SQL Date Practical Question

Question here was we need to find the maximum date from the list of rows.
Consider an example…….



with Dataset (dt1, dt2, dt3)
as  (

select Getdate() as dt1 , getdate() + 10 as dt2 , getdate() + 3 as dt3
union
select Getdate() + 5 as dt1 , getdate() -5   as dt2 , getdate() + 12 as dt3
union
select Getdate() + 10 as dt1 , getdate() -3   as dt2 , getdate() + 4 as dt3


)


SELECT * FROM Dataset



The Question is we need a maximum date in each row

We can write the query in multiple ways. But simplest way to get the output is as follows

SELECT
CASE WHEN (dt1 > dt2) and (dt1> dt3) then dt1 end,

CASE WHEN (dt2 > dt3) and (dt2> dt1) then dt2 end,

CASE WHEN (dt3 > dt1) and (dt3> dt2) then dt3 end,

dt1, dt2, dt3


FROM dataset







Add your comments or different ways to get the above output

July 9, 2016

SSIS upload a files in folder


SSIS Scenario Based Question

Consider a scenario where you have to upload 4 files into SQL server from folder daily.
Client has requirement that if there are only 3 files are available out of 4 files then also our package should execute with those 3 files and load those 3 files into server.

Provided that your file format is different for all the files and destination tables are also different.

To solve this problem we have to write a package with dynamic Enable and disable attribute of a container or data flow task

Create few variables to enable and disable the task.

In this script task we need to write a logic to read the file from the provided folder and based on the file availability we set value to variables





In the data flow task we need write logic to upload the file.

 

April 17, 2016

Group by and Having Clause

HAVING CLAUSE and a WHERE CLAUSE?


The HAVING clause is a filter that acts similar to a WHERE clause, but the filter acts on groups of rows rather than on individual rows.


In other words, the HAVING clause is used to qualify the results after the GROUP BY has been applied.
The WHERE clause, in contrast, is used to qualify the rows that are returned before the data is 

aggregated or grouped.
HAVING qualifies the aggregated data after the data has been grouped or aggregated.


Isolation Level

Isolation Levels in SQL Server

Isolation levels in SQL Server control the way locking works between transactions.
Various Isolation levels:
Read uncommitted
Read Committed
Repeatable Read
Phantom Read


READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent
Other transactions from modifying data read by the current transaction.

READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions.

When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.

 Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.

This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:


READ COMMITTED

The default transaction isolation level of SQL server is READ COMMITTED.

Under READ COMMITTED isolation levels, changes to the data is not visible to other transactions until the transaction is committed.

When reading data, SQL Server will attempt to put a shared lock on all the rows that it reads and when it finds a row exclusively locked by another transaction, the query will wait till the transaction completes and the lock is released.

This prevents dirty reads.



REPEATABLE READ:



Ensures that statement cannot read uncommitted data that has been modified by other transaction.

Also no other transaction can modify the data that has been read by current transaction.

This can be achieved by REPEATABLE READ transaction isolation level. SQL Server will lock each row that you touch irrespective of whether it matches the criteria or not. So the rows you have already read cannot be modified by other transactions.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes.

This prevents other transactions from modifying any rows that have been read by the current transaction.

Other transactions can insert new rows that match the search conditions of statements issued by the current transaction.

Phantom Read

REPEATABLE READ isolation level can cause Phantom Reads.

Under REPEATABLE READ, SQL Server will lock the rows it reads.

But it does not prevent from inserting new rows.

So, it can happen that when you run the same query for second time, under REPEATABLE READ, you might find new rows in the second query.

Such a row is called 'Phantom Row' and a read that returns a Phantom Row is called a Phantom Read.


So, REPEATABLE READ does not guarantee that you will always get the same result. But it guarantees that the rows that SQL Server has read to process the query are locked and no other transaction can modify it.

SERIALIZABLE

SERIALIZABLE is very close to REPEATABLE READ isolation level, except that it prevents phantom rows.

The principal difference between SERIALIZABLE and REPEATABLE READ is that SERIALIZABLE applies a range lock so that you cannot insert new rows within the range locked by the transaction.


This behavior prevents Phantom rows.