Difference between VARCHAR and TEXT in SQL

basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

The text datatype is deprecated and should not be used for new development work.

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

XML: How to convert an XML file into a table?

Declare @ContentXML xml
set @ContentXML = '<Root>
  <Fulfil>
    <Id>1</Id>
    <Status>Test</Status>
  </Fulfil>
  <Fulfil>
    <Id>2</Id>
    <Status>Test-2</Status>
  </Fulfil>
  <Fulfil>
    <Id>3</Id>
    <Status>Test-3</Status>
  </Fulfil>
  <Fulfil>
    <Id>4</Id>
    <Status>Test-4</Status>
  </Fulfil>
  <Fulfil>
    <Id>5</Id>
    <Status>Test-5</Status>
  </Fulfil>
</Root>'

Declare @Temp table(Id int, status nvarchar(100))
insert into @Temp
select  pref.value('(Id)[1]','int'),
pref.value('(Status)[1]','nvarchar(100)')
from @ContentXML.nodes('/Root/Fulfil') AS People(pref)

select *, Id * Id as Multiple from @Temp

Table :-

Id status Multiple
1 Test 1
2 Test-2 4
3 Test-3 9
4 Test-4 16
5 Test-5 25

Find Stored procedure which are using temp tables

To write query to search through all procedures, and list procedures which uses temp tables.

SQL Server doesn't store any type of metadata or dependency information about temp tables potentially created inside of them


SELECT [schema] = s.name, 
  [procedure] = p.name 
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE (LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%create%table%#%'
OR LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%select%into%#%')
and p.name not like 'z%' 
and p.name not like '%old'
order by p.name

Process with an ID #### is not running in Microsoft Visual Studio Express 2015 for Web

Open Visual Studio as an administrator
Right-click your project and click on 'Unload Project'
Again, right-click your project and click on 'Edit PROJECT_NAME.csproj'
Find the code below and delete it:

<DevelopmentServerPort>57457</DevelopmentServerPort>
<DevelopmentServerVPath>/</DevelopmentServerVPath>
<IISUrl>http://localhost:57457/</IISUrl>

Save and close the file .csproj

Right-click your project and reload it
See its working

Generate Integer random number in sql server 2008 R2

select ABS(CAST(NEWID() AS binary(6)) %1000) as RandomNumber

Geneating Odd Number using CTE in SQL server

DECLARE @oddnum INT = 1;
;WITH n(n) AS
(
  SELECT @oddNum + ((@oddNum-1)%2)
  UNION ALL
  SELECT n + 2 FROM n WHERE n < 50
)
SELECT ROW_NUMBER() OVER(ORDER BY n ) as ID, n as OddNumber  FROM n;

Result :

ID OddNumber
1 1
2 3
3 5
4 7
5 9
6 11
7 13
8 15
9 17
10 19
11 21
12 23
13 25
14 27
15 29
16 31
17 33
18 35
19 37
20 39
21 41
22 43
23 45
24 47
25 49
26 51