Change The Server Collation after installation


After you finish installation for SQL server may be you want to change the Server collation or database Collation so leat’s see what will we do if we want to make this :

1- if you want to know what’s your database collation know

SELECT name, collation_name

FROM sys.databases WHERE name = ‘test’   — put your database name here

2- If you want to get a list of all available collations on the server using

SELECT * FROM ::fn_helpcollations()

3- Alter Database Collation

ALTER DATABASE test           — put your database name here

COLLATE Arabic_CI_AS        — replace with whatever collation you need

4- IF you want to know what’s your server collation

SELECT SERVERPROPERTY (‘Collation’)

Returen All File in Any Directory Stored Procedure Sp_ListFiles


USE master

GO

CREATE PROCEDURE dbo.sp_ListFiles

@PCWrite varchar(2000),

@DBTable varchar(100)= NULL,

@PCIntra varchar(100)= NULL,

@PCExtra varchar(100)= NULL,

@DBUltra bit = 0

AS

SET NOCOUNT ON

DECLARE @Return int

DECLARE @Retain int

DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(2000)

DECLARE @Work varchar(2000)

DECLARE @Wish varchar(2000)

SET @Work = ‘DIR ‘ + ‘”‘ + @PCWrite + ‘”‘

CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

SET @Retain = @@ERROR

IF @Status = 0 SET @Status = @Retain

IF @Status = 0 SET @Status = @Return

IF (SELECT COUNT(*) FROM #DBAZ) < 4

BEGIN

SELECT @Wish = Name FROM #DBAZ WHERE Work = 1

IF @Wish IS NULL

BEGIN

RAISERROR (‘General error [%d]’,16,1,@Status)

END

ELSE

BEGIN

RAISERROR (@Wish,16,1)

END

END

ELSE

BEGIN

DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING

(Name,40,1) = ‘.’ OR Name LIKE ‘%.lnk’

IF @DBTable IS NULL

BEGIN

SELECT SUBSTRING(Name,40,100) AS Files

FROM #DBAZ

WHERE 0 = 0

AND (@DBUltra = 0 OR Name LIKE ‘% %’)

AND (@DBUltra != 0 OR Name NOT LIKE ‘% %’)

AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)

AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)

ORDER BY 1

END

ELSE

BEGIN

SET @Task = ‘ INSERT ‘ + REPLACE(@DBTable,CHAR(32),CHAR(95))

+ ‘ SELECT SUBSTRING(Name,40,100) AS Files’

+ ‘ FROM #DBAZ’

+ ‘ WHERE 0 = 0’

+ CASE WHEN @DBUltra = 0 THEN ” ELSE ‘ AND Name LIKE ‘ + CHAR(39) + ‘% %’ + CHAR(39) END

+ CASE WHEN @DBUltra != 0 THEN ” ELSE ‘ AND Name NOT LIKE ‘ + CHAR(39) + ‘% %’ + CHAR(39) END

+ CASE WHEN @PCIntra IS NULL THEN ” ELSE ‘ AND SUBSTRING (Name,40,100) LIKE ‘ + CHAR(39) + @PCIntra + CHAR(39) END

+ CASE WHEN @PCExtra IS NULL THEN ” ELSE ‘ AND SUBSTRING

(Name,40,100) NOT LIKE ‘ + CHAR(39) + @PCExtra + CHAR(39) END

+ ‘ ORDER BY 1’

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

DROP TABLE #DBAZ

SET NOCOUNT OFF

RETURN (@Status)

GO

—-EXECUTE sp_ListFiles ‘E:\DataBase’,NULL,NULL,NULL,1

Enable xp_cmdshell using sp_configure


—- To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1

GO

—- To update the currently configured value for advanced options.

RECONFIGURE

GO

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

String Function in SQL Server 2012


CONCAT() Function

Concat Function is new function in SQL Server 2012 this Function is very Great Function becouse in 2008 when we want to concat two name + Number we must make ++and Cast the Number to String that’s very hard today in this version we will make this operation in one and easy step . Let’s See How :

SELECT CONCAT(1, 2, 3, 4) AS SingleString
SELECT CONCAT(‘One’,1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT(‘One’,2,NULL) AS SingleString
SELECT CONCAT(”,”,”,”) AS SingleString
SELECT CONCAT(NULL, NULL) AS SingleString

in old SQL Server when you want to conact Mostafa + Elmasry +1985 you write

Select ‘Mostafa’ + ‘ Elmasry’ + Cast(‘ 1985’ as Nvarchar(10)) AS My_Name

But in New Version that’s very easy see :

Select Concat(‘Mostafa’+’ Elmasry’+1985)

 

 

Format() Function

SET DATEFORMAT DMY
GO
DECLARE @DAY DATETIME = ’17/09/2010′;
SELECT FORMAT ( @DAY, ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @DAY, ‘d’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @Month DATETIME = ’17/09/2010′;
SELECT FORMAT ( @Month, ‘M’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @Month, ‘M’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @Month, ‘M’, ‘de-DE’ ) AS DE_Result;
GO
DECLARE @YEAR DATETIME = ’17/09/2010′;
SELECT FORMAT ( @YEAR, ‘Y’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @YEAR, ‘Y’, ‘de-DE’ ) AS DE_Result;
GO

Day
SELECT FORMAT ( GETDATE(), ‘d’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘ddd’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( GETDATE(), ‘dddd’, ‘en-US’ ) AS US_Result;

Month
SELECT FORMAT ( GETDATE(), 'm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmmm', 'en-US' ) AS US_Result;

Year
SELECT FORMAT ( GETDATE(), 'y', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yy', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yyy', 'en-US' ) AS US_Result;

Currency
DECLARE @var INT = 50
SELECT FORMAT(@var,’c’) AS Currency;
SELECT FORMAT(@var,’c1′) AS Currency;
SELECT FORMAT(@var,’c2′) AS Currency;
SELECT FORMAT(@var,’c3′) AS Currency;
GO
DECLARE @d INT = 500;
SELECT FORMAT ( @d, ‘c’, ‘en-US’ ) AS US_Result;
SELECT FORMAT ( @d, ‘c’, ‘fr-FR’ ) AS FR_Result;
SELECT FORMAT ( @d, ‘c’, ‘de-DE’ ) AS DE_Result;

miscalculation format
DECLARE @var INT = 50
SELECT FORMAT(@var,’p’) AS Percentage;
SELECT FORMAT(@var,’e’) AS Scientific;
SELECT FORMAT(@var,’x’) AS Hexa;
SELECT FORMAT(@var,’x4′) AS Hexa1;

language
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘en-US’) AS English_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘AR’) AS ARABIC_Result;
SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’, ‘gu’) AS Gujarati_Result;

Logical Function in SQL Server 2012


logical function in SQL Server 2012 RC0

IIF() Function

IIF () Function is great function and all developer know this function becouse its arealdy in VB.NET but the new it’s will be used it in SQL

IIF () Function Syantax

IFF(the Condition , ‘1’,’2′) if the condition is true the result will return no 1 , Else the result will return no 2

Examples

SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
—Example 2
DECLARE @NAME NVARCHAR(15)= ‘MOSTAFA’
SELECT IIF(LEN(@NAME) > 5 , ‘BIG NAME’ , ‘SHORT NAME’) AS LONG
—Example 3
DECLARE @VARIBALE INT =   NULL
SELECT IIF (@VARIBALE IS NULL
, ‘YES’,’NO’)
—Example 4
 CREATE TABLE EMPLOYEE
 (
 EMP_NAME NVARCHAR(50),
 CODE NVARCHAR(50)
 )
 INSERT INTO EMPLOYEE VALUES (‘MOSTAFA’,’M100′),(‘ABDEL-KAREEM’,’100′),(‘OMAR’,’O100′)
SELECT EMP_NAME,CODE,IIF(TRY_PARSE(CODE AS INT) IS NULL , ‘VALUE IS STRING’ , ‘VALUE IS NOT STRING’)
  FROM EMPLOYEE

CHOOSE() FUNCTION

Choose Function is very simple function if the indesx is numeric it will convert to integer , and if the index is great than the elemant in the list it will return NULL

Example NO 1

 SELECT CHOOSE ( 1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;
 SELECT CHOOSE ( 2, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;
 SELECT CHOOSE ( 3, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Third;

Example NO 2 (if the index is great than the elemant in the list )

SELECT CHOOSE ( 0, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Null;
 SELECT CHOOSE ( 4, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Result_NULL;

Example NO 3 ( if the indesx is numeric it will convert to integer )

 SELECT CHOOSE ( 1.1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;
 SELECT CHOOSE ( 2.9, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;