I created a web app that uses a MySQL database, but I have to migrate the database to Microsoft SQL Server 2008 R2 and I'm using the SQL Server Migration Assistant (SSMA).

I'm getting errors in my report for some tables that use foreign keys.

1. Self-referencing foreign keys

I have one table that has a parent-child relationship between rows; map table:

| map_id | map_title           | latitude  | longitude  | map_zoom | map_parent |
|:------:|:-------------------:|:---------:|:----------:|:--------:|:----------:|
| 1      | My Parent Map       | 50.364829 | -52.635623 | 17       | NULL       |
| 2      | Some Child Map      | 50.366916 | -52.634718 |          | 1          |
| 3      | Another Child Map   | 50.364898 | -52.634543 |          | 1          |
| 4      | My Last Example Map | 50.361986 | -52.638891 |          | 3          |

The report generated by SQL Server Migration Assistant (SSMA) shows the SQL that would be used to create a table in SQL Server.

MySQL (source):

1  CREATE
2      TABLE `map`
3          (
4              `map_id` int(11) UNSIGNED NOT NULL  AUTO_INCREMENT, 
5              `map_title` varchar(50) DEFAULT NULL, 
6              `latitude` varchar(12) DEFAULT NULL, 
7              `longitude` varchar(12) DEFAULT NULL, 
8              `map_zoom` varchar(5) NOT NULL, 
9              `map_parent` int(11) UNSIGNED DEFAULT NULL, 
10               PRIMARY KEY  (`map_id`) , 
11               KEY `map_parent`  (`map_parent`) , 
12               CONSTRAINT `map_ibfk_2` FOREIGN KEY  (`map_parent`)  REFERENCES `map`  (`map_id`)   ON DELETE CASCADE   ON UPDATE CASCADE 
13          )  ENGINE = InnoDB AUTO_INCREMENT = 12 DEFAULT  CHARSET = utf8;

SQL Server (target, SQL generated by SSMA):

1  CREATE TABLE dbo.map
2  (
3      map_id bigint NOT NULL IDENTITY(12, 1), 
4      map_title nvarchar(50) NULL DEFAULT NULL, 
5      latitude nvarchar(12) NULL DEFAULT NULL, 
6      longitude nvarchar(12) NULL DEFAULT NULL, 
7      map_zoom nvarchar(5) NOT NULL, 
8      map_parent bigint NULL DEFAULT NULL, 
9      CONSTRAINT PK_map_map_id PRIMARY KEY (map_id), 
10      /* 
11      *   SSMA error messages:
12      *   M2SS0040: ON DELETE  CASCADE|SET NULL|SET DEFAULT action  was changed to NO ACTION to avoid circular references of cascaded foreign keys.
13  
14      CONSTRAINT map$map_ibfk_2 FOREIGN KEY (map_parent) REFERENCES dbo.map (map_id) 
15           ON DELETE NO ACTION 
16          /* 
17          *   SSMA error messages:
18          *   M2SS0036: ON UPDATE CASCADE|SET NULL|SET DEFAULT action  was changed to NO ACTION to avoid circular references of cascaded foreign keys.
19  
20           ON UPDATE NO ACTION
21          */
22  
23  
24      */
25  
26  
27  )
28  GO
29  CREATE NONCLUSTERED INDEX map_parent
30      ON dbo.map (map_parent ASC)
31  GO

As you can see it gives an error indicating it changed my ON UPDATE CASCADE and ON DELETE CASCADE to NO ACTION in order to "to avoid circular references of cascaded foreign keys."

2. Many-to-many tables

I have two tables that got an error for "multiple paths" and similarly were changed to NO ACTION.

asset_property table:

| asset_id | property_id | property_value  |
|:--------:|:-----------:|:---------------:|
| 933      | 1           | Joseph          |
| 933      | 2           | Green           |
| 936      | 1           | Jacob           |
| 936      | 2           | Yellow          |
| 942      | 1           | Susan           |
| 942      | 2           | Blue            |

MySQL (source):

1  CREATE
2      TABLE `asset_property`
3          (
4              `asset_id` int(11) NOT NULL, 
5              `property_id` int(11) NOT NULL, 
6              `property_value` varchar(100) DEFAULT NULL, 
7               PRIMARY KEY  (`asset_id`, `property_id`) , 
8               KEY `asset_id`  (`asset_id`) , 
9               KEY `property_id`  (`property_id`) , 
10               CONSTRAINT `asset_property_ibfk_1` FOREIGN KEY  (`asset_id`)  REFERENCES `asset`  (`asset_id`)   ON DELETE CASCADE   ON UPDATE CASCADE , 
11               CONSTRAINT `asset_property_ibfk_2` FOREIGN KEY  (`property_id`)  REFERENCES `property`  (`property_id`)   ON DELETE CASCADE   ON UPDATE CASCADE 
12          )  ENGINE = InnoDB DEFAULT  CHARSET = utf8;

SQL Server (target, SQL generated by SSMA):

1  CREATE TABLE dbo.asset_property
2  (
3      asset_id int NOT NULL, 
4      property_id int NOT NULL, 
5      property_value nvarchar(100) NULL DEFAULT NULL, 
6      CONSTRAINT PK_asset_property_asset_id PRIMARY KEY (asset_id, property_id), 
7      /* 
8      *   SSMA error messages:
9      *   M2SS0041: ON DELETE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
10  
11      CONSTRAINT asset_property$asset_property_ibfk_1 FOREIGN KEY (asset_id) REFERENCES dbo.asset (asset_id) 
12           ON DELETE NO ACTION 
13          /* 
14          *   SSMA error messages:
15          *   M2SS0037: ON UPDATE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
16  
17           ON UPDATE NO ACTION
18          */
19  
20  
21      */
22  
23  , 
24      CONSTRAINT asset_property$asset_property_ibfk_2 FOREIGN KEY (property_id) REFERENCES dbo.property (property_id) 
25           ON DELETE CASCADE 
26           ON UPDATE CASCADE
27  )
28  GO
29  CREATE NONCLUSTERED INDEX asset_id
30      ON dbo.asset_property (asset_id ASC) 31  GO 32  CREATE NONCLUSTERED INDEX property_id
33      ON dbo.asset_property (property_id ASC) 34  GO

I've only found one article that talks about these errors. The article's solution for the self-referencing table error doesn't seem to apply, and the many-to-many error solution is to just remove the constraint "because the application or user shouldn’t be modifying these values."

Thanks for any help!!

---

I am not experienced with SSMA, I have used SSIS for migrating databases.After reading your question, I think I could give you some suggessions..

You have created circular dependency in your database.When your database have circular dependencies and you have data in both dependent tables, if you want migrate the data you must have to disable the key constraints in the destination database.To avoid the second problem should avoid the cascading option and instead use stored procedure or trigger.

You can take a look of this link :

http://blogs.msdn.com/b/ssma/archive/2011/03/19/mysql-to-sql-server-migration-method-for-correcting-schema-issues.aspx

Why is it that in SQL Server I can't do this:

select  sum(count(id)) as 'count'
from    table

But I can do

select sum(x.count)
from
(
    select  count(id) as 'count'
    from    table   
) x

Are they not essentially the same thing? How am I meant to be thinking about this in order to understand why the first block of code isn't allowed?

SUM() in your example is a no-op - SUM() of a COUNT() means the same as just COUNT(). So neither of your example queries appear to do anything useful.

It seems to me that nesting aggregates would only make sense if you wanted to apply two different aggregations - meaning GROUP BY on different sets of columns. To specify two different aggregations you would need to use the GROUPING SETS feature or SUM() OVER feature. Maybe if you explain what you want to achieve someone could show you how.

According to this MSDN page:

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.

How does this affect SQL Server Compact Edition? varbinary(max) is not currently supported in SQL Server Compact Edition (CE) 4.0.

Going off of what a SQL Server Compact expert said, "Microsoft SQL Server != Microsoft SQL Server Compact".

http://social.msdn.microsoft.com/Forums/en/sqlce/thread/29f05956-cb61-48c4-a6f6-c1e2d9fe5ea5

If that changes, I'm sure MSFT will explain when the older types actually get deprecated.

I have a SQL query with a result like this:

value | count
------+------
foo   |     1
bar   |     3
baz   |     2

Now I want to expand this so that each row with a count larger than 1 occurs multiple times. I also need these rows to be numbered. So I would get:

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

I have to make this work on all the major databases (Oracle, SQL Server, MySQL, PostgreSQL, and maybe more). So a solution that works across different databases would be ideal, but clever ways to make it work on any database are appreciated.

For MySQL, use the poor man's generate_series, which is done via views. MySQL is the only RDBMS among big four that don't has any CTE feature.

Actually you can use this technique on database that supports view. So that's virtually all database

Generator technique sourced here: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

The only minor modification we made is we replace the bitwise (shift left and bitwise or) technique from the original technique with mere multiplication and addition respectively; as Sql Server and Oracle has no shift left operator.

This abstraction is 99% guaranteed to work on all database, except Oracle; Oracle's SELECT can't function without any table, in order to do this, one need to select from dummy table, Oracle provided one already, it's called DUAL table. Database portability is a pipe dream :-)

Here's the abstracted views that works on all RDBMS, devoid of bitwise operations(which is not really a necessity anyway in this scenario) and feature nuances(we remove OR REPLACE on CREATE VIEW, only Postgresql and MySQL supports them) among all major database.

Oracle caveat: Just put FROM DUAL after each SELECT expression

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Then use this query:

SELECT t.value, t.cnt, i.n
FROM tbl t
JOIN generator_64k i 
ON i.n between 1 and t.cnt
order by t.value, i.n

Postgresql: http://www.sqlfiddle.com/#!1/1541d/1

Oracle: http://www.sqlfiddle.com/#!4/26c05/1

Sql Server: http://www.sqlfiddle.com/#!6/84bee/1

MySQL: http://www.sqlfiddle.com/#!2/78f5b/1

Creating index on computed column of type nvarchar raises following error:

Cannot create index or statistics 'MyIndex' on table 'MyTable' because the computed column 'MyColumn' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

What does imprecise column mean?

UPDATE. The definition is following:

alter table dbo.MyTable
    add [MyColumn] as dbo.MyDeterministicClrFunction(MyOtherColumn)
go  
create index MyIndex on dbo.MyTable(MyColumn)
go

UPDATE2. The MyDeterministicClrFunction is defined as following:

[SqlFunction(IsDeterministic = true)]
public static SqlString MyDeterministicClrFunction(SqlString input)
{
    return input;
}

Per MSDN, CLR Function columns must be persisted to be indexed:

Any computed column that contains a common language runtime (CLR) expression must be deterministic and marked PERSISTED before the column can be indexed. CLR user-defined type expressions are allowed in computed column definitions. Computed columns whose type is a CLR user-defined type can be indexed as long as the type is comparable. For more information, see CLR User-Defined Types.

Persist the column and I suspect it will work.

If I was using C# I could use the .net framework's SqlDataSourceEnumerator to discover and show a user a list of SQL Server instances on the network.

How can I do that in Delphi?

To enumerate all available Microsoft SQL Servers, you can follow this excellent tutorial:

Enumerating available SQL Servers. Retrieving databases on a SQL Server

Included in Zarko's tutorial, there's a link to download the full source code which can be useful to quickly test it and check if it fits your needs.

Edit Zarko Gajic's main routine is:

procedure ListAvailableSQLServers(Names : TStrings);
var
  RSCon: ADORecordsetConstruction;
  Rowset: IRowset;
  SourcesRowset: ISourcesRowset;
  SourcesRecordset: _Recordset;
  SourcesName, SourcesType: TField;

    function PtCreateADOObject(const ClassID: TGUID): IUnknown;
    var
      Status: HResult;
      FPUControlWord: Word;
    begin
      asm
        FNSTCW FPUControlWord
      end;
      Status := CoCreateInstance(
                  CLASS_Recordset,
                  nil,
                  CLSCTX_INPROC_SERVER or CLSCTX_LOCAL_SERVER,
                  IUnknown,
                  Result);
      asm
        FNCLEX
        FLDCW FPUControlWord
      end;
      OleCheck(Status);
    end;
begin
  SourcesRecordset := PtCreateADOObject(CLASS_Recordset) as _Recordset;
  RSCon := SourcesRecordset as ADORecordsetConstruction;
  SourcesRowset := CreateComObject(ProgIDToClassID('SQLOLEDB Enumerator')) as ISourcesRowset;
  OleCheck(SourcesRowset.GetSourcesRowset(nil, IRowset, 0, nil, IUnknown(Rowset)));
  RSCon.Rowset := RowSet;
  with TADODataSet.Create(nil) do
  try
    Recordset := SourcesRecordset;
    SourcesName := FieldByName('SOURCES_NAME'); { do not localize }
    SourcesType := FieldByName('SOURCES_TYPE'); { do not localize }
    Names.BeginUpdate;
    try
      while not EOF do
      begin
        if (SourcesType.AsInteger = DBSOURCETYPE_DATASOURCE) and (SourcesName.AsString <> '') then
          Names.Add(SourcesName.AsString);
        Next;
      end;
    finally
      Names.EndUpdate;
    end;
  finally
    Free;
  end;
end;

---

I don't know what I can add without lamering what Zarko's explained.

Is it possible to know when and if the contents of certain tables in a database has changed? How can my SQL Server notify the client applications that the data was changed by another user? How to implement query notifications with dbGo ?

Do my clients need to poll the database, or is there a callback mechanism for this?

My client is a Delphi application with TADODataSet, and my server is SQL Server 2005/2008, serving multiple clients.

The Delphi dbGo controls does not support Query Notifications, so either poll the database or try to check e.g. some of the following:

• WMI Provider for Server Events - MSSQL Service Broker
• DA-SOFT AnyDAC TADEventAlerter component - Query Notifications mechanism
• Devart SDAC TMSChangeNotification component - Query Notifications mechanism

I have a table in SQL Server 2008 R2 with close to a billion rows. I want to change the datatype of two columns from int to bigint. Two times ALTER TABLE zzz ALTER COLUMN yyy works, but it's very slow. How can I speed the process up? I was thinking to copy the data to another table, drop, create, copy back and switching to simple recovery mode or somehow doing it with a cursor a 1000 rows a time but I'm not sure if those will actually lead to any improvement.

Depending on what change you are making, sometimes it can be easier to take a maintenance window. During that window (where nobody should be able to change the data in the table) you can:

1. drop any indexes/constraints pointing to the old column, and disable triggers
2. add a new nullable column with the new data type (even if it is meant to be NOT NULL)
3. update the new column setting it equal to the old column's value (and you can do this in chunks of individual transactions (say, affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log)
4. once the updates are all done, drop the old column
5. rename the new column (and add a NOT NULL constraint if appropriate)
6. rebuild indexes and update statistics

The key here is that it allows you to perform the update incrementally in step 3, which you can't do in a single ALTER TABLE command.

This assumes the column is not playing a major role in data integrity - if it is involved in a bunch of foreign key relationships, there are more steps.

EDIT

Also, and just wondering out loud, I haven't done any testing for this (but adding it to the list). I wonder if page + row compression would help here? If you change an INT to a BIGINT, with compression in place SQL Server should still treat all values as if they still fit in an INT. Again, I haven't tested if this would make an alter faster or slower, or how much longer it would take to add compression in the first place. Just throwing it out there.

What is the correct syntax to create an inline scalar function in SQL Server?

Books Online, in the Types of Functions chapter (2005 and up), talks about Inline Scalar Functions as if they exist and as if no BEGIN...END block is required (in contrast with multiline functions):

For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value.

I also noticed a row for "IS: inline scalar function" in the list of object types in the spt_values table:

SELECT name
FROM master..spt_values
WHERE type = 'O9T'
AND name LIKE '%function%'

I have tried to create such a function with no success:

CREATE FUNCTION AddOne(@n int) RETURNS int
AS
    RETURN @n + 1

The error message is

Msg 102, Level 15, State 31, Procedure AddOne, Line 3 Incorrect syntax near 'RETURN'.

Am I missing something or is there an error in Books Online?

Well, AFAIK, none exist (not even in the hidden [mssqlsystemresource] database) and there's no syntax to create one. So it appears that this is something that Microsoft must have anticipated in the run-up to SQL Server 2005 by adding a type for it (and doc!), but never actually implemented for some reason.

Though it is one of the single most requested features for all of Ms Sql Server. Primarily because the default UDF's are so slow and we end up having to back-end ITVF's to get the same effect. (difficult and clumsy, but it works).

I want to create a Y/N flag, where Y indicates every valid value in every column in a given row is equal, and N otherwise. I need to exclude from consideration any column that contains nulls, blanks, or all zeroes. Suppose:

CREATE TABLE z_test
(ID INT NOT NULL,
D1 VARCHAR(8)NULL,
D2 VARCHAR(8)NULL,
D3 VARCHAR(8)NULL,
D4 VARCHAR(8)NULL,
DFLAG CHAR(1)NULL)

INSERT INTO z_test VALUES (1,NULL,' ','000000','00000000',NULL)
INSERT INTO z_test VALUES (1,'20120101','0000','20120101','00000000',NULL)
INSERT INTO z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL)
INSERT INTO z_test VALUES (2,'00000000','20090101','0','20090101',NULL)
INSERT INTO z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL)
INSERT INTO z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL)

The desired output (excluding D1 through D4, though I don't want to drop them) is:

ID       DFLAG
---------------
1        N
1        Y
2        Y
2        Y
3        N
3        Y

Speed is not a concern as this query will not be run very often but it is on a largish table.

Any pointers or suggestions would be very much appreciated!!

SELECT ID, 
       CASE 
         WHEN C = 1 THEN 'Y' 
         ELSE 'N' 
       END AS DFLAG 
FROM   z_test 
       CROSS APPLY (SELECT COUNT(DISTINCT D) C 
                    FROM   (VALUES(D1), 
                                  (D2), 
                                  (D3), 
                                  (D4)) V(D) 
                    WHERE  LEN(D) > 0 /*Excludes blanks and NULLs*/
                         AND D LIKE '%[^0]%'/*Excludes ones with only zero*/) CA 

I inherited a MS Access front-end that has linked tables to SQL Server. The linked table names in MS Access do not match the table names in SQL Server. How can I find out what SQL server tables are actually linked to MS Access? Also, if I didn't know what SQL Sever the linked tables were connected to, how could I find that out?

You can use the tabledefs collection to check the connect property and the source table name.

CurrentDB.TableDefs("dbo_table_name").SourceTableName
CurrentDB.TableDefs("dbo_table_name").Connect

Or

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

SQL Server's T-SQL syntax seems to allow multiple plus signs in succession:

SELECT 1 + 2 --3
SELECT 1 ++ 2 --3
SELECT 1 ++++++ 2 --3
SELECT 1 + '2' --3
SELECT 1 ++ '2' --3
SELECT '1' + '2' --'12'
SELECT '1' ++ '2' --'12'

Multiple pluses seem to behave just like a single plus. Why does the "multiple plus operator" ++ exist? What does it do?

The first plus sign is interpreted as an addition operator. Each of the remaining plus signs is interpreted as a unary plus operator:

1 ++ 2   means   1 + (+2)
1 +++ 2  means   1 + (+(+2))

It's very common in programming languages to have this unary plus operator, though it's rarely used in SQL as it doesn't actually do anything.

Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression. Specifically, it will not return the positive value of a negative expression.

The unary plus operator is mentioned in the SQL-92 standard.

As well as the usual arithmetic operators, plus, minus, times, divide, unary plus, and unary minus, there are the following functions that return numbers: ...

While unary plus isn't all that useful, it has a more useful companion: unary minus. It is also known as the negative operator.

SELECT -(expression), ...
--     ^ unary minus

We have tables Site and Content in our database.

Each site is operated by a different client and each site has it's own content.

On the front end of the sites we offer a search box which uses a fulltext/freetext search on the content table to return results but each site can only return results from its self, not from other sites in the database.

SQL Server query optimizer is behaving badly here. If it optimizes the query for a site with little content then the query performs horribly for sites with lots of content causing timeouts.

We understand that we can add OPTION(RECOMPILE) to the end of the query to fix this but my question is this...

Would it be better to create a cache table for each site so that the content for each site could be cached periodically and have the search stored procedure look for a cache table instead using a parameter?

The cache would only be updated / refreshed whenever content is added/changed.

My thinking is that this would....

a) Reduce the size of the table being searched to only contain the records for the correct site

b) Allow the FullText search to generate a more accurate index of the content for each site

c) Allow the query optimizer to cache the optimized queries for each site independently

Is this correct? Am I right in doing it this way?

You are asking the right questions. It's a trade-off. You have to decide which is better/ or worse for your situation.

Will you be adding sites frequently? How many rows do you expect total and for each site? In general SQL Server 2008 Full-Text Search will do fine up to 10's of millions of rows. If you expect more than that, I'd split the sites out individual tables.

Keep in mind, even if you split out to multiple tables, your query plans could still vary greatly due to the number or words being returned from a given search term. You may still want to consider using OPTION(RECOMPILE).

Here are some advantages of each route:

Single Table

• No schema changes to add additional sites.
• Easier to manage.
• Don't need to worry about separate stored procedures or dynamic SQL to handle multiple tables.

Multiple Tables

• Smaller tables and indexes (don't need a SiteId).
• Better full-text performance due to smaller catalogs.
• Potentially better separation of Data.

I am writing a .NET application that writes data to SQL Server 2008r2. I have two options for inserting the data, either I can create a large string insert statement, and send it as a text command, or I can collect the data in a .NET DataTable, and pass it as a table valued parameter. What are the benefits and costs of each method?

(I am omitting a good deal of code since I am just asking about the relative benefits, not the specific syntax)

e.g.:

Option 1:

    string insert = @"insert into MyTable (id, val) values
        ( 1, 'a'),(2,'b'),(3,'c'),(4,'d');"

Option 2:

    DataTable dt = new DataTable();
    dt.Columns.Add("id", typeof(int));
    dt.Columns.Add("val", typeof(string));
    ....
    create procedure uspMyProc 
                    @tt ttMyTableType readonly
                as
                begin
                    insert into TestTable1 (id, strValue)
                    select myId, myVal from @tt;
                end"

Thanks for any help.

Option 3: In the first instance I would populate the insert stored procedure with one insert statement's worth of parameters and call it multiple times in a loop from the C# code:

Option 4: If you truly have lots of rows to insert, perhaps you need to look into the SqlBulkCopy class. It consumes either DataTable, DataRow or an IDataReader. You can make an IDataReader from a list of objects using some custom code, a question of this ilk is asked here:

Get an IDataReader from a typed List

---

I would say it depends.

If you really want to pass many rows of parameters in tabular form, for whatever reason, use a table valued parameter - that's what it's there for.

I have seen Option 1 - some generic DAL code would script out a SQL "batch" of commands to run. It worked, but didn't give any defence against injection attacks. Parameterised SQL does.

---

All that said, I would favour calling the insert sproc once for each row to be inserted from code - the calls will be fully parameterised and performance is fine. If performance becomes a problem I would favour Option 4.

Been Trying to figure this out and according to Cast and Convert Documentation on MSDN, this should be possible ( http://msdn.microsoft.com/en-us/library/ms187928.aspx )

Have a look at the Conversion table.

I am running the following Code:

CREATE TABLE TableName (
    ID bigint Identity(1,1),
    SomeValue nvarchar(20) not null,
    TimestampColumn timestamp not null)

Insert Into TableName (SomeValue)
values ('testing')

SELECT Convert(decimal, TimeStampColumn) from TableName

However I simply get the following error:

Error converting data type timestamp to numeric.

This should be possible according to the documentation or am I missing something? Note I will also need to convert back to the same timestamp value.

SELECT TimeStampColumn, Convert(timestamp, Convert(decimal, TimeStampColumn)) 
FROM TableName

In the end the above query should render the same value.

Try this; though MSDN says it's a implicit conversion but it actually doesn't work. So, what I am doing is converting it to INT and then to decimal (INT to decimal is implicit anyways)

select val, CAST((CONVERT(bigint, timestampcol)) as decimal) as 'TS as decimal' 
from teststmp

I have the following rows:

CREATE TABLE #TEMP (id int, name varchar(255), startdate datetime, enddate datetime)
INSERT INTO #TEMP VALUES(1, 'John', '2011-01-11 00:00:00.000','2011-01-11 00:01:10.000')
INSERT INTO #TEMP VALUES(2, 'John', '2011-01-11 00:00:20.000','2011-01-11 00:01:05.000')
INSERT INTO #TEMP VALUES(3, 'John', '2011-01-11 00:01:40.000','2011-01-11 00:01:50.000')
INSERT INTO #TEMP VALUES(4, 'Adam', '2011-01-11 00:00:40.000','2011-01-11 00:01:20.000')
INSERT INTO #TEMP VALUES(5, 'Adam', '2011-01-11 00:00:45.000','2011-01-11 00:01:15.000')

SELECT * FROM #TEMP

DROP TABLE #TEMP

I am trying to remove records that have dates contained within other dates to obtain the following:

John 2011-01-11 00:00:00.000 2011-01-11 00:01:10.000
John 2011-01-11 00:01:40.000 2011-01-11 00:01:50.000
Adam 2011-01-11 00:00:40.000 2011-01-11 00:01:20.000

Any suggestions on how to achieve this for a table of about 100K rows?

This gives the desired result:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate >= t1.startdate
    AND     t1.enddate   <= t1.enddate
)

http://msdn.microsoft.com/en-us/library/ms188336.aspx

Edit: I've just noticed that there's one problem. If there are duplicates (same start- and enddate), both would be deleted (none with John's approach, even with only one equal date). So you need to take this into account:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   = t2.enddate
    OR      t1.id > t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   = t2.enddate
)