What is difference between datetime and timestamp
One is a date and time, the other is a column type that is updated every time a row is updated. [Note timestamp is being deprecated; use rowversion instead]
sql-server-2005
Integer division in sql server
In the first you are getting the result of two integers and then casting the result as DECIMAL(9,2). In the second you’re just dividing two integers and that’s expected. If you cast one of the integers as a decimal BEFORE you do the division, you’ll get a decimal result. SELECT 151/CAST(6 AS DECIMAL (9,2))
Foreign key relationship with composite primary keys in SQL Server 2005
Since Table2 has a composite primary key (FileID, FileType), then any reference to it must also include both columns. ALTER TABLE dbo.Table1 ADD CONSTRAINT FK_Table1_Table2 FOREIGN KEY(FileID, FileType) REFERENCES Table2(FileID, FileType) Unless you have a unique constraint/index on the Table2.FileID field (but if so: why isn’t this the PK??), you cannot create a FK relationship … Read more
Unable to create index because of duplicate that doesn’t exist?
It’s not that the index already exists, but that there are duplicate values of the TopicShortName field in the table itself. According to the error message the duplicate value is an empty string (it might just be a facet of posting I guess). Such duplicates prevent the creation of a UNIQUE index. You could run … Read more
How get the T-SQL code to find duplicates?
Well, if you have entire rows as duplicates in your table, you’ve at least not got a primary key set up for that table, otherwise at least the primary key value would be different. However, here’s how to build a SQL to get duplicates over a set of columns: SELECT col1, col2, col3, col4 FROM … Read more
Import Package Error – Cannot Convert between Unicode and Non Unicode String Data Type
The problem of converting from any non-unicode source to a unicode SQL Server table can be solved by: add a Data Conversion transformation step to your Data Flow open the Data Conversion and select Unicode for each data type that applies take note of the Output Alias of each applicable column (they are named Copy … Read more
SQL Server: how to write an alter index statement to add a column to the unique index?
You cannot alter an index – all you can do is drop the old index (DROP INDEX (indexname) ON (tablename)) re-create the new index with the additional column in it: CREATE UNIQUE NONCLUSTERED INDEX (indexname) ON dbo.YourTableName(columns to include) The ALTER INDEX statement in SQL Server (see docs) is available to alter certain properties (storage … Read more
How to use alias column name in where clause in SQL Server
You can’t use aliased columns in a WHERE clause. You can try using a derived table. Perhaps something like this (sorry, not tested): SELECT * FROM ( SELECT SQRT( POWER( cast(Program_Latitude as float) – cast(‘41.5126237’ as float), 2) + POWER( cast(Program_Longitude as float) – cast(‘-81.6516411’ as float), 2) ) * 62.1371192 AS DistanceFromAddress from tblProgram … Read more
UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’
To avoid that error, I needed to add SET ANSI_NULLS, QUOTED_IDENTIFIER ON; for all my stored procs editing a table with a computed column. You don’t need to add the SET inside the proc, just use it during creation, like this: SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE dbo.proc_myproc …
Check whether a table contains rows or not sql server 2005
For what purpose? Quickest for an IF would be IF EXISTS (SELECT * FROM Table)… For a result set, SELECT TOP 1 1 FROM Table returns either zero or one rows For exactly one row with a count (0 or non-zero), SELECT COUNT(*) FROM Table