分页查询
通用方法:sqlserver 2005 +
ROW_NUMBER() OVER()方式:
SELECT TOP 50 * FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY ID) as rowID FROM TripDetail)as b where b.rowID >1010001 ;
TOP NOT IN方式 :
SELECT TOP 50 * FROM TripDetail where ID not in(SELECT TOP 1010001 ID FROM TripDetail ORDER BY ID) ORDER BY ID;
sqlserver 2012 +
offset fetch next方式:
SELECT * FROM TripDetail ORDER BY ID OFFSET 1010001 ROWS FETCH NEXT 50 ROWS ONLY;
经测试,最后一种方式性能最佳,但是需要sqlserver版本高一点,第一和第二个方式性能差不多。
批量插入
//SqlConnection conn; DataTable table SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = tablename; //表名
bulkCopy.BatchSize = table.Rows.Count; bulkCopy.WriteToServer(table);