sql server tuning
how does the query optimizer determine estimated row size when we execute any query? ...
sql-server
SQL Server Indexes
what is the difference between clustered index scan and clustered index seek? ...
SQL Server: the maximum number of rows in table
I develop software that stores a lot of data in one of its database tables (SQL Server version 8, 9 or 10). Let's say, about 100,000 records are inserted into that table per day. This is about 36 million records per year. For fear that I would lose on performance, I decided to create a new table everyday (a table with current date in its...
To change date format in sql
hi guys, I have date in mm/dd/yyyy format in database.I want to display in form as dd/mm/yyyy. Can anybody help?I want to get time along with date. ...
Is there any difference between DECIMAL and NUMERIC in MS SQL?
Is there any difference between DECIMAL and NUMERIC data types in MS SQL? When should I use DECIMAL and when NUMERIC? ...
SQL Server Stored Proc takes (much) longer to run than the same query executed from text in Management Studio
So here is a bit of a strange one... I have a stored proc that takes 40 seconds to run. I copy the contents of the stored proc to a new query window, change the 2 input parameters (which are both dates) so they are declared and set, and run the query. It is basically instant, sub 1 second execution. The only difference is the stored proc...
Import de-normalized relational data from Excel into SQL Server
I need to import data from an Excel spreadsheet into SQL Server, but the data isn't in a relational/normalized format so the import wizard isn't going to cut it (as far as I know). The data is in this format: Category SubCategory Name Description Category#1 SubCategory#1 Product#1 Description#1 Category#1 SubCatego...
make server name as a variable
I am trying to get data from different sever, and the sever name might change. So I set the server name as a parameter. The idea of my sql store procedure is something like this CREATE PROCEDURE [dbo].[GetData] @ServerName AS BEGIN SELECT * FROM @ServerName.ClientDataBase.dbo.Client END Does anyone know how to achieve this...
Does SQLBulkCopy dramatically increase the .mdf file size?
I am using the SqlBulkCopy class to do a bulk insert into a SQLServer DB. Original size of the .mdf file associated with the DB is 1508 Mb. When I run it (on the same data of about 4 million records) with : BatchSize of 100000, the size of the .mdf grows to 1661 MB. BatchSize of 1000000, size of the .mdf grows to 1659 MB. Why this vari...
is there a sql server profiler for sql express
is there a sql server profiler for MSsql expess ? maybe an open source one ? or maybe just a tool that helps me see what query is passed to the database ? ...
Emulate ".contains()" in Reporting Services
I have a dataset in Reporting Services that looks something like this: ID | Type | Name -----+--------+-------- 1 | A | Mary 2 | A | Joe 3 | D | Steve 12 | F | Irving I want to show a textbox IF AND ONLY IF there exists a row where type = A. Basically, I want to do "Fields!Type.Contains("A...
How can I keep my SQL Server Express connection from timing out the first time through testing?
We have some automated tests for the interaction between our data access layer (C#) and the database (MS SQL). We are using SQL Express to mount an mdf, which we revert after the testing is done. It seems that the first time the tests are run on a freshly booted machine, we see timeouts, even though SQLExpress is running. The second t...
What's the best method to stop duplicate inserts into the database
I have a forum application using asp.net, c#, sql server and linq to sql. It suffers from multiple inserts, I think when the insert button is pressed but also maybe when the browser is refreshed or back is clicked. What's the most comprehensive way, or ways to prevent this. UPDATE: I currently use response.redirect after inserting text...
How do you get around multiple database connections inside a TransactionScope if MSDTC is disabled?
I have a web application that issues requests to 3 databases in the DAL. I'm writing some integration tests to make sure that the overall functionality round trip actually does what i expect it to do. This is completely separate from my unit tests, just fyi. The way I was intending to write these tests were something to the effect of ...
Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?
I'm trying to store Japanese characters in nvarchar fields in my SQL Server 2000 database. When I run an update statement like: update blah set address = N'スタンダードチャ' where key_ID = 1 from SQL Server Management Studio, then run a select statement I see only question marks returned to the results window. I'm seeing the same question ...
What resources exist for Database performance-tuning?
What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area? The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources. General SQL Book: SQL Perform...
How to Enable Full-Text Index on Sql Server 2008 Table
Not sure what's happening with this, but here's my question. I have a Sql Server 2008 database that I need to be able to do full-text indexing/searching but when I try to setup my indices on the table, I get the following: I've tried running this stored procedure on my database and it's successful: EXEC sp_fulltext_database @action ...
SQL Server load balancing
What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased? I'm trying to find out if there is a solution today that makes this very easy from the view point of the webservers or other database access to SQL Serve...
How can I check for average concurrent events in a SQL table based on the date, time and duration of the events?
I have a set of call detail records, and from those records, I'm supposed to determine the average concurrent active calls per system, per hour (at a precision of one minute). If I query 7pm to 8pm, I should see the average concurrent calls for the hour (averaging the concurrent calls for each minute) within that hour (for each system). ...
problem with asp membership
i have the following code: if (Membership.FindUsersByName(username) == null) { Membership.CreateUser(username, password, email); } if (!Roles.RoleExists("USR")) { Roles.CreateRole("USR"); } Roles.AddUserToRole(username,"USR"); Data is being inserted in th...