MSBI

USING   CASE   IN   SELECT   STATEMENT/ IMPLEMENT IF-THEN-ELSE LOGIC IN SELECT STATEMENT With Example let us understand, How to use CASE Condition in SQL SELECT Statement. I have table EMP having column EMPLOYEEID , MARITALSTATUS , GENDER. Click on pic to enlarge view Say if we want to Display under following condition   If MaritalStatus is M, Then display it as Married, If MaritalStatus is S, then display it as Single and Other than M or S, then Display it as Unknown.   If Gender is M, Then display it as Male, If Gender is F, then display as Female and Other than M or F, then Display it as Unknown. This can be achieved in many ways, for example using REPLACE Function etc, but since my intention is on CASE Statement, I will Continue to demonstrate it using CASE. Let’s do it for Condition 1 first: As per Condition 1, we can use CASE STATEMEN...

Many times, When we come to a situation to start our work from already developed project, Where Foreign Keys and Primary Keys are already developed in Database, It is Necessary for us to know which table is referring to which table as with Primary Key and Foreign Key Constraint. For this Purpose, We have System Tables which will Help us to find the Primary key and Foreign key and related Tables. The query is as follows: USE ADVENTUREWORKS2008 GO SELECT OBJECT_NAME ( SFK . PARENT_OBJECT_ID ) AS [FORIEGN KEY TABLE] , COL_NAME ( SFKC . PARENT_OBJECT_ID , SFKC . PARENT_COLUMN_ID ) AS [FORIEGN KEY COLUMN] , OBJECT_NAME ( SFK . REFERENCED_OBJECT_ID ) AS [PRIMARY KEY TABLE] , COL_NAME ( SFKC . REFERENCED_OBJECT_ID , SFKC . REFERENCED_COLUMN_ID ) AS [PRIMARY KEY COLUMN] FROM SYS . FOREIGN_KEYS AS SFK INNER JOIN SYS . FOREIGN_KEY_COLUMNS AS SFKC ON SFK . OBJECT_ID = SFKC . CONSTRAINT_OBJECT_ID Here I'm trying to find Related tables under Primary key an...

[OLE DB Destination [730]] Error: There was an error with input column "Email" (689) on input "OLE DB Destination Input" (362). The column status returned was: "The value violated the integrity constraints for the column.". We have created table initially with EMAIL Column as "NOT NULL". While loading Data from Flat File to SQL Server Table, We got this above mentioned error. We looked into few data from Source where we found we had Some "NULL" Values and this is reason we got this Error. We fixed it by changing EMAIL Column at Destination Table like to allow "Null" Values and then populated the records and it worked like charm!!!

If we don't have any identity column and yet, we wish to see on what position does record lies, here is simple Query I did. We are using  ROW_NUMBER ( ) Without using ORDER BY clause. That means, We are suppressing the effect of ORDER BY Clause used in ROW_NUMBER ( ) CREATE TABLE C ( NAME VARCHAR ( 100 )) INSERT INTO C SELECT ( 'ABC' ) UNION ALL SELECT ( 'bharath' ) UNION ALL SELECT ( 'job' ) UNION ALL SELECT ( 'raghavendra' ) UNION ALL SELECT ( 'anita' ) UNION ALL SELECT ( 'prakash' ) UNION ALL SELECT ( 'dhinakaran' ) SELECT * FROM C  We know name ' Raghavendra ' lies at 4th Position and if we want to get this position number, then, we think of ROW_NUMBER ( ) concept but we know ROW_NUMBER ( ) will order the sequence the Records are stored and then It displays it. So in order to avoid this ordering of the records, I will use some dummy values...

Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the SQL server is controlled by Windows NT/2000 account or group used when logged in. this means that Windows username and password are used to access the database server. Mixed mode authentication : - Mixed mode allows use of Windows credentials and the local SQL server account. Admin’s might maintain user accounts in SQL Server.

We know basically when restarting the SQL server, it is basically means clearing Caches along with some other properties to get reset. But since our main Priority lies in Cleaning the Cache files, we can write a SQL script for doing this. SQL server has inbuilt function for clearing Cache Files. The main Cache files required to cleared during Restart is DATA CACHE and PROCEDURE CACHE.  This effectively will restart your server. The Script for clearing these two caches are as follows: DBCC DROPCLEANBUFFERS ---- This will clear the data cache DBCC FREEPROCCACHE ---- This will clear the procedure cache Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Let me create a table having Identity column. CREATE   TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100)) As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column. INSERT   INTO IDENT VALUES ('DHINAKARAN') SELECT   *   FROM IDENT. Here we can see that after running the above query, the result is as shown in below snippet. Let me create a table having Identity column. CREATE   TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100)). As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column. INSERT   INTO IDENT VALUES ('DHINAKARAN') SELECT   *   FROM IDENT ...