sql-server

When having an identity column is not a good idea?

In tables where you need only 1 column as the key, and values in that column can be integers, when you shouldn't use an identity field? To the contrary, in the same table and column, when would you generate manually its values and you wouldn't use an autogenerated value for each record? I guess that it would be the case when there are ...

Select many fields applying DISTINCT to only one particular field

In SQL Server, how can I achieve selecting many fields (without an agregation function) and apply the DISTINCT statement to only one particular field? For instance: if I have a table where I store user actions, the pseudo-schema would be like this: UserActions ------------ id, User, Action insertDate I want to get the latest actions ...

MS-SQL Server 2000 slow full text indexing

We have a full text index on a fairly large table of 633,569 records. The index is rebuilt from scratch as part of a maintenance plan every evening, after a bunch of DTS packages run that delete / insert records. Large chunks of data are deleted, then inserted (to take care of updates and inserts), so incremental indexing is not a possib...

WPF SQL Distributed Data Model with LINQ and SQL Express

I am developing an application in C#/WPF that requires a distributed data model, as it will have both online and offline access. My current thoughts are to develop the first version of the application against SQL Server express and LINQ to SQL. Then use the schema to create a SQL Express Compact DB (and modify the connection string) for...

MS-SQL In statement

Select a, b, c from table where a in (1, 2, 3) What if the list is in a column? I try this will error: Select a, b, c from a in b Sorry for not clear my question. It's not about join or in (select b from table) Column b type is nvarchar, data is a list, like this '1,2,5' Column a type is int. ...

How to display error message generated from raiserror() with SQL Server

I am interacting with SQL Server 2005 from a jsp via JDBC (this is an assignment not a real project) and I have created a trigger in the database I am using. If a certain condition is not met when this trigger executes I raise an error via raiserror(). I would like this error to be displayed on the actual page that calls the SQL Server q...

table size not reducing after purged table

I recently perform a purging on my application table. total record of 1.1 millions with the disk space used 11.12GB. I had deleted 860k records and remain 290k records, but why my space used only drop to 11.09GB? I monitor the detail on report - disk usage - disk space used by data files - space used. Is it that i need to perfrom sh...

MSSQL: Does last_user_update mean that the index hurt performance?

I've written a query to look for indexes that are not used in the production environment in order to drop them, however I'm curious whether the last_user_update column is an indication that the index was solely a maintenance overhead, in which case it would be good to drop it, or whether the index helped the performance of the insert/upd...

Bulk Copying and Deleting in OneTransaction

In C# application I like to copy the table data from one server(SQLServer2000) to another server (SQLServer2005). I like to copy the data in single instance and delete the existing data in the SQL Server 2000 table. I need to do all this(Bulk copying and Deleting) in single transaction. How to achieve this? Note: I am having two differe...

sort distinct date column

Hi Experts, I need distinct year and month from date column which would be sorted by same column. I have date coulmn with values like (YYYY/MM/DD) 2007/11/7 2007/1/8 2007/11/4 2007/12/3 2008/10/4 2009/11/5 2008/5/16 after having query, it should be 2007/1/1 2007/11/1 2007/12/1 2008/5/1 2008/10/1 2009/11/1 This doesn't seems to ...

SQL 2008 CALs - how are they enforced?

Hi I am running a server that needs a very limited number of user connections (max of 5 at a time). So the 5-CAL edition of SQL 2008 Workgroup seems perfect for me. Now, what I need to know is the following: do I actually need to physically install a CAL on each machine/user that will be using the server (by using I mean reading and wri...

size limit for XML datatype in SQL 2005

Is there a size limit on the XML data type in SQL 2005? When I try to return anything more than 44kb size of the XML string from my stored proc, it just returns an empty string. I am using FOR XML PATH to return hierarchical data sets in XML format from my stored procs. ...

Month wise totals in columns

Hello, I've a table (Consumption) as follows: DocDate ItemCode Quantity 01.01.09 A 5 02.01.09 A 6 and so on.. for the whole year I need results as follow: ItemCode Jan09Total Feb09Total Mar09Total The problem is that the quantities should be summed up for the months, which should be in columns and acco...

Create hexadecimal number in SQL

I want to generate unique hexadecimal numbers in SQL. How can I do this? If you know how to generate in c# please also include that. ...

How to look for a SQl Server row based on a paragraph - specifically, the words in the paragraph, not the exact paragraph

I want to use SQL Server 2005 Express to find the top five tuples related to a paragraph of text. I know I can use Full Text Indexing and CONTAINSTABLE to find rows that contain an exact phrase, but how do I get it to return the closest matches to the words in a paragraph, not the exact paragraph itself. So far the only way I can think...

SQL Server 2005 Links to Access

I m using Access 97 database.It has lots of forms. Data bulked. I have to upgrade it quickly. I bought SQL Server 2005 enterprise edition. I want to use SQL Server for data holder. I m going to use Access forms regularly. I just want to export data to the sql server. Is it possible to use "Linked" data storing? ...

Hide SQL database from Management Studio

How can you hide databases you do not have access rights to when logging into SQL Server 2005 / 2008? Currently if a user connects, they see all the databases on the server, meaning they have to scan though the list to find their database. ...

Passing multiple parameters of same column to SQL Server select SP

I have a string value in the web.config for example 2 guids separated by a ",". I need to query the database dynamically (i.e i have no idea how many values could be separated by a comma in the web.config) and run a select statement on the table passing these values and getting all that is relevant for example: select * from tablename...

Handling identity columns in an "Insert Into TABLE Values()" statement?

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following? Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all. ...

Does a trigger on update, that updates its own table, recall the trigger?

I have a table called Point. The table has two columns that we'll deal with, ActualDepth and DesignDepth. When someone updates ActualDepth I have a trigger that will recalculate the DesignDepths for all of the other points that are near the updated one. My question then is, Will my trigger be called recursively because other rows in...