Learning Curve…

Archive for the ‘SqlServer’ Category

The script to check if column exists and then renaming the column:-

IF EXISTS (SELECT * FROM sys.columns WHERE Name = N’OldColumnName’ AND Object_ID = Object_ID(N’TableName’))
BEGIN
   EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’
END

Advertisements

Style Code

Style

Format

Example

0 or 100 Default. Equivalent to not specifying a style code. mon dd yyyy hh:mmAM Sep 8 2007 9:00PM
1 USA date. mm/dd/yy 09-08-2007
2 ANSI date. yy.mm.dd 07-09-2008
3 UK / French date. dd/mm/yy 08-09-2007
4 German date. dd.mm.yy 08.09.07
5 Italian date. dd-mm-yy 08-09-2007
6 Abbreviated month. dd mmm yy 08-Sep-07
7 Abbreviated month. mmm dd, yy Sep 08, 07
8 or 108 24 hour time. HH:mm:ss 21:00:00
9 or 109 Default formatting with seconds and milliseconds appended. mon dd yyyy hh:mm:ss:fffAM Sep 8 2007 9:00:00:000PM
10 USA date with hyphen separators. mm-dd-yy 09-08-2007
11 Japanese date. yy/mm/dd 07-09-2008
12 ISO date. yymmdd 70908
13 or 113 European default with seconds and milliseconds. dd mon yyyy HH:mm:ss:fff 08 Sep 2007 21:00:00:000
14 or 114 24 hour time with milliseconds. HH:mm:ss:fff 21:00:00:000
20 or 120 ODBC canonical date and time. yyyy-mm-dd HH:mm:ss 08-09-2007 21:00
21 or 121 ODBC canonical date and time with milliseconds. yyyy-mm-dd HH:mm:ss.fff 00:00.0
101 USA date with century. mm/dd/yyyy 09-08-2007
102 ANSI date with century. yyyy.mm.dd 08-09-2007
103 UK / French date with century. dd/mm/yyyy 08-09-2007
104 German date with century. dd.mm.yyyy 08.09.2007
105 Italian date with century. dd-mm-yyyy 08-09-2007
106 Abbreviated month with century. dd mmm yyyy 08-Sep-07
107 Abbreviated month with century. mmm dd, yyyy Sep 08, 2007
110 USA date with hyphen separators and century. mm-dd-yyyy 09-08-2007
111 Japanese date with century. yyyy/mm/dd 08-09-2007
112 ISO date with century. yymmdd 20070908
126 ISO8601, for use in XML. yyy-mm-ddThh:mm:ss 2007-09-08T21:00:00

 

You can check if constraints exist in a column in sql server  and if its not there you can alter table to add a constraint using below query :-

IF NOT EXISTS (SELECT  *  FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintsName]’))
    ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [ConstraintsName]  DEFAULT (newid()) FOR [ContactID]
GO

Happy Coding !!!

Just add these lines before your SP Statements. :-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Exp :-

Use YourDatabaseName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE …

Tags: ,

Here is the Query to Insert large data in a sql table to test performance . This query could come in handy to test performance of application with  large data .

Just replace 1000000 in below query with your desired number of rows in a table.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=1000000)
BEGIN

SET @intFlag = @intFlag + 1
INSERT INTO dbo.testTable
(id,Name)
VALUES
(newid(),’test’)
END
PRINT @intFlag
GO

Note :-after adding large data in your table , you may want to replace hard coded strings (like ‘test’ in above query) with some unique values to make data more readable , you can use below technique for the same :-

https://nikhatshahin.wordpress.com/2010/12/20/row_number-and-overorder-by/

Tags:

char / nchar  :- It Contains fixed-length text. When you store text shorter than the defined length, the text is padded with spaces. The nchar stores the data in Unicode format, which enables you to store data for many foreign languages.

text / ntext :- Used to store large amounts of text.

varchar / nvarchar :- Used to store text with a variable length. The nvarchar stores the data in Unicode format, which enables you to store data for many foreign languages.

Date range :- January 1, 1753, through December 31, 9999

Time range :- 00:00:00 through 23:59:59.997


Author

Learning days (Calendar)

December 2017
M T W T F S S
« Jun    
 123
45678910
11121314151617
18192021222324
25262728293031

Knowledge Bank (Archives)

I am on Twitter

Blog Stats

  • 520,020 hits

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 43 other followers

%d bloggers like this: