Limitations of Views in SQL Server

1. You can't create a parameterized view, in other words you can't create a view with a parameter.
2. We can't create view on temporary table.
3. All the tables referenced by the view must be in the same database as the view.
4. An indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed, for example adding or dropping a column.
5. If you add any new column to a table then it would not be reflected in the View until you won't execute the below queryEXEC sp_refreshview 'ViewName'.
   
   Don't use Select *, just use a select specific columnnames
   It's a best practice to create a view with SCHEMABINDING using this, the base table will not be modified.
    
6. You can't use count (*) in a view creation query, for example:

CREATE VIEW vwEmployeeDetails

AS

SELECT COUNT(*) FROM Employee

GO

 Error Message : Msg 4511, Level 16, State 1, Procedure vwEmployeeDetails,        

 Line 10

 Create View or Function failed because no column name was specified for column 1.

Workaround for this issue shown below.

          CREATE VIEW vwEmployeeDetails

          AS

          SELECT COUNT(*) AS Total FROM Employee

          GO

   7.We can't use an order by clause at the time of view creation.

         CREATE VIEW vwEmployeeDetails

         AS

         SELECT EmpId, Name, Department, Salary FROM Employee ORDER BY     

         EmpId    DESC

         GO

   Error Message:

    Msg 1033, Level 15, State 1, Procedure vwEmployeeDetails, Line 5

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table      

    expressions, unless TOP, OFFSET or FOR XML is also specified.

       It clearly specifies to use TOP or FOR XML in your TSQL. Lets make some changes as    

       shown below and run the query again.

     CREATE VIEW vwEmployeeDetails

     AS

     SELECT TOP 5 EmpId, Name, Department, Salary FROM Employee ORDER BY EmpId     

     DESC

     GO

     SELECT * FROM vwEmployeeDetails

     

     

EmpId	Name	Department	Salary
10	Rajesh	PHP	4300
9	Airi	PHP	4300
8	Neha	PHP	2000
7	Harinder	PHP	4000
6	Bharat	Design	3040

SQL Puzzle 2

Q1. How You will replace "A" with "B" and "B" with "A" in the following string "ABAB" ?

  ‘ABAB’ à ‘BABA’

    SELECT REPLACE((REPLACE((REPLACE('ABAB','A','C')),'B','A')),'C','B')

Q2. What would be output of below query ?

    SELECT A.A FROM (SELECT 1 A, 2 B) A

    JOIN

    (SELECT 1 A, 2 B ) B

    ON A.A=B.B

Output:

Q3. What would be output of below query ?

SELECT B.A FROM (SELECT 1 A) A

JOIN

(SELECT 1 A, 2 B) B

ON A.A = B.A

Output:

Repeat Rows N times According to column values mentioned.

Question:

id	TextValue
3	Binod
2	Vikash

Expected Output 1:

TextValue

Binod

Binod

Binod

Vikash

Vikash

SELECT t.TextValue

FROM #temp t

CROSS JOIN (SELECT ROW_NUMBER() over(order by OBJECT_ID) as rn from sys.columns) as t1

WHERE t.id >= t1.rn

ORDER BY t.TextValue

Expected Output 2:

TextValue	level
Binod	1
Binod	2
Binod	3
Vikash	1
Vikash	2

WITH  CTE AS

(

SELECT TextValue, 1 as level

FROM #temp

UNION ALL

SELECT t.TextValue,level+1 

FROM #temp t join CTE c on t.TextValue = c.TextValue

WHERE level < t.id

)

SELECT * FROM CTE ORDER BY TextValue

How to remove all non-alphabetic characters from string in SQL Server?

Create a function to solve the given problem:

CREATE FUNCTION RemoveNonAlphaCharacters

(@StringName VARCHAR(1000))

RETURNS VARCHAR(1000)

AS

BEGIN

     DECLARE @KeepValues as Varchar(50)

     SET @KeepValues='%[^a-z]%'

     WHILE PATINDEX(@KeepValues,@StringName)>0

     SET @StringName= STUFF(@StringName,PATINDEX(@KeepValues,@StringName),1,'')

     RETURN @StringName

END

Now, Call the Function as below

select DBO.RemoveNonAlphaCharacters('fefwsAA1456sdfsf')

output : fefwsAAsdfsf

Print 1 to 10 Table using SQL Query

;WITH CTE AS

(

SELECT 0 TableOf1, 0 TableOf2,

0 TableOf3,0 TableOf4,0 TableOf5,

0 TableOf6,0 TableOf7,0 TableOf8,

0 TableOf9,0 TableOf10

UNION ALL

SELECT TableOf1 + 1, TableOf2 + 2,

TableOf3 + 3, TableOf4 + 4, TableOf5 + 5,

TableOf6 + 6, TableOf7 + 7, TableOf8 + 8,

TableOf9 + 9, TableOf10 + 10

FROM CTE WHERE TableOf1 < 10

)

SELECT * FROM CTE WHERE TableOf1 <> 0

TableOf1	TableOf2	TableOf3	TableOf4	TableOf5	TableOf6	TableOf7	TableOf8	TableOf9	TableOf10
1	2	3	4	5	6	7	8	9	10
2	4	6	8	10	12	14	16	18	20
3	6	9	12	15	18	21	24	27	30
4	8	12	16	20	24	28	32	36	40
5	10	15	20	25	30	35	40	45	50
6	12	18	24	30	36	42	48	54	60
7	14	21	28	35	42	49	56	63	70
8	16	24	32	40	48	56	64	72	80
9	18	27	36	45	54	63	72	81	90
10	20	30	40	50	60	70	80	90	100