I was just playing with adventureworks database in sqlserver. I got stuck in a query. I wanted to Select all titles from HumanResources.Employee which are either 'Male' or 'Female' but not both. i.e if title Accountant is Male and Female both I want to leave that title. I need only those titles where Gender is either Male or Female. I h...
I'm helping a friend learn SQL, and need more databases to help him get more practice. We have of course AdventureWorks, Northwind, and Pubs. Does anybody know of any other SQL databases samples that might be available to download? I know some sites have some databases where you can practice queries on the site, but I was looking for s...
I'm looking for a way to handle the following scenario. I have a database table that I need to return only one record for each "group id" that is contained within the table, furthermore the record that is selected within each group should be the oldest person in the household. ID Group ID Name Age 1 134 John B...
I know how to use indexes(clustured and non clustured) But when should i use non clustured indexes in my table. What scenarios should be there, so as to make my column non clustured index. I have gone throught msdn guidelines but still little bit confusion. Should i make only unique columns as NC or should there any other columns also a...
I run this query "insert into students (StudentName) values ('reza');insert into Records (RecordValue,StudentID)" + " values (20,@@IDENTITY)"; in C# and get following exception : Characters found after end of SQL statement ...
I'm specifically referring to how stored procedure input parameters are handled. Does it just change how SQL Server interprets the data, or does it require SQL Server to make a copy of the data? Thanks! ...
The sys.dm_db_index_usage_stats DMV gives information about number of seeks and scans against a index in sql server I have a large number of composite index that includes multiple columns. I suspect that these indexes cause a lot of maintenance overhead ann would like to narrow the number of columns. Is there a way we can find out seek...
Hi, I have a situation where by i have a catalog on Server A, and a same named Catalog on Server B. I'm using synonyms on Server B so that some tables are actually just going through to server A. Such that, calling select * from ServerB.Table, is actually returning Data from ServerA.Table transparently. This seems to work fine when ...
i'm using sql server 2000 and i have some table need to update. one of problem is how to update some portion of column? for example: here is two table one is OrderDate and ShipName and i want to change Date value in OrderDate, such like all 1996 year to 1999 year and all 'tom' in ShipName to 'ken' value OrderDate ShipName ...
I got a SP with a parameter that is supposed to be fed unique value like '111','112',... I would like to execute stress testing to compare the latency after adding index. Is there any sql command or tool can help on this? ...
I would like to close all idle connections in the SqlClient connection pool. Is there a documented method for doing this? The issue is that as part of the normal processing of this application (A testing harness) I need to drop the database and recreate it at the start of each test scenerio. Obviously I can't drop the database with ac...
Hi There, Please let me know how to install and run DotNetNuke_Community_05.02.00_Source.zip in visual studio 2008. I keepp getting all kinds of errors Thanks in advance ...
Hi all, I need some help to build SQL Query. I have table having data like: ID Date Value 1 12/01/2009 4 2 12/02/2009 3 3 12/03/2009 6 4 12/01/2008 2 5 12/02/2008 4 6 12/03/2008 5 Here is my query: select ID, Date, Value from MyTable where Date between '12/01/2009' and '12/04/2009' ...
I have a SQL Server table that contains postal addresses. In preparation for mailing, I need to do a number of string replacements to conform with USPS preferences ("Avenue" becomes "Ave", for example). To save me the trouble of enumerating all of the replacements, I have the label/abbreviation pairs saved in a two-column table. Is ther...
i have change name of table through procedure sp_rename.Do i need to change fk constraint of child table? ...
"Error: Schema: relationship expected on 'headerRecord'." I get the above error while load xml file to SQL Server 2008 using SQLXMLBulkLoad4 Component , the xml file contains Call Detail records, I have generated schema file from xml file using both , Dataset and XSD.exe tool, but the error remains same., if there is another way to imp...
I have a table, let's call it Users. This table has primary key called Id. Despite having Id as primary key (unique clustered), it has other index (unique nonclustered) on the same column(Id). I would like to drop this constraint, but foreign keys reference this unique nonclustered index and I get The constraint ... is being referenced...
Is there any tool for ER modeling and then converting models to a Microsoft SQL Server database? The tool should has this features: ER Diagrams shoud be in UML Notation (see this, the last is UML notation). Export models to a Microsoft SQL Server 2005/2008 database (by creating a mdf file or connecting to a MSSQL Server and creating s...
Let's asume I have a parent-child structure setup in SQL (server 2005): CREATE TABLE parent (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255)) CREATE TABLE child (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255)) insert into parent select 'parent with 1 child' insert into parent select 'parent with 2 children' insert into ch...
After installing SQL Server 2005, I tried to create a new database (command: create database database_name). But the message was an error: permission denied to create a database in 'master' database. My question is 1.) how to exit this master database and create a new database outside it, 2.) what is a master database. ...