How do you unit test your T-SQL? Which libraries/tools do you use? What percentage of your code is covered by unit tests and how do you measure it? How do you decide which modules to unit test first? Do you think the time and effort which you invested in your unit testing harness has paid off or not? If you do not use unit testing, ca...
I have an web application that creates printable forms, these forms have a unique number on them, the problem is I have 2 forms that separate numbers need to be created for them. ie) Form1- Numbered 2000000-2999999 Form2- Numbered 3000000-3999999 dbo.test2 - is my form information table Tsel - is my autoinc table for the 3000000 ser...
I'm trying to put my database scripts into a VS Database project (I haven't generated them yet). I'm assuming there should be some kind of "auto generate all" functionality, but I can't find anything. Or do I really have to generate them one by one manually from management studio? ...
Hi all, Its my first post and I'm really rusty on MSSQL so be gentle :-) I have a table in which I am trying to use datediff. I think it will be easiest if I post the query and results first select mh.agetime, mh.whatid from mailhistory mh inner join mail m on mh.mailid=m.myid where (mh.whatid=17 or mh.whatid=11 or mh.whatid=0) and...
The problem is, we have a huge number of records (more than a million) to be inserted into a single table from a Java application. The records are created by the Java code, it's not a move from another table, so INSERT/SELECT won't help. Currently, my bottleneck is the INSERT statements. I'm using PreparedStatement to speed-up the proce...
Hi All, I am struggling finding an efficient way to find a distance between a Point that intersects a polygon and the border of that polygon. I was able to use the STDistance comparing the point to every point that made up the polygon but that is taking a lot of time. Using SPatial indexed wasn't much helpful because the STDistance is n...
I have some SQL I need to get working on SQL 2005/2008. The SQL is from SQL 2000 and uses some system objects to make it work. master.dbo.spt_provider_types master.dbo.syscharsets systypes syscolumns sysobjects I know SQL 2005 does no longer use system tables and I can get the same information from views, but I am looking for a solut...
I ask this question as a followup of this question. A solution that uses bcp and xp_cmdshell, that is not my desired solution, has been posted here. I am new to c# (since I am a Delphi developer) anyway I was able to create a simple CLR stored procedure by following a tutorial. My task is to move a file from the client file system to ...
Is there any sort of non-SQL API for talking to SQL Server? I'm curious if there is a more direct way to retrieve table or view data. (I don't have a problem with SQL, just curious if any of the layer between the SQL parser and the underlying data store is exposed.) ...
Currently I am designing a database for use in our company. We are using SQL Server 2008. The database will hold data gathered from several customers. The goal of the database is to acquire aggregate benchmark numbers over several customers. Recently, I have become worried with the fact that one table in particular will be getting very ...
How can i view all the functions (build in) in sql database using sql management studio? ...
I was wondering if there was any recommended max size for MDF and/or LDF Files for an SQL server instance. For example, if I want to create a 400 GBytes Database, is there a rule to help me decide how many mdf files I should create ? or should I just go ahead and create a single gigantic 400Gbytes mdf file? If so is this going to someh...
I know how derived tables are used, but I still can’t really see any real advantages of using them. For example, in the following article http://techahead.wordpress.com/2007/10/01/sql-derived-tables/ the author tried to show benefits of a query using derived table over a query without one with an example, where we want to generate a re...
Every time you make use of a derived table, that query is going to be executed. When using a CTE, that result set is pulled back once and only once within a single query. Does the quote suggest that the following query will cause derived table to be executed three times ( once for each aggregate function’s call ): SELECT AVG...
SQL Server doesn't allow creating an view with schema binding where the view query uses OpenQuery as shown below. Is there a way or a work-around to create an index on such a view? ...
I need to count occurrence of a list of words across all records in a given table. If I only had 1 word, I could do this: select count(id) as NumRecs where essay like '%word%' But my list could be hundreds or thousands of words, and I don't want to create hundreds or thousands of sql requests serially; that seems silly. I had a though...
For a particular apps I have a set of queries that I run each time the database has been restarted for any reason (server reboot usually). These "prime" SQL Server's page cache with the common core working set of the data so that the app is not unusually slow the first time a user logs in afterwards. One instance of the app is running o...
I'm using the MySql connector for .NET to copy data from MySql servers to SQL Server 2008. Has anyone experienced better performance using one of the following, versus the other? DataAdapter and calling Fill to a DataTable in chunks of 500 DataReader.Read to a DataTable in a loop of 500 I am then using SqlBulkCopy to load the 500 Da...
I'm having issues when trying to insert nvarchar values in sql server with a linq-to-sql + c# program. Here is the code: public partial class Recipient { private static object _recipientLock = new object(); public static Recipient Find(string email, string displayName, MyDataContext db) { ...
Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user wou...