SQL delete query on non key column

I’m new to the SQL query language and I’m trying to figure out how to delete a row (in a SQL DB table) with a non key ID column value using vb.net. For example in the table TEST below: ID Name 1 x 2 y 3 z I have a windows form that the user […]

Transfer data in SQL Server 2008 R2

I have two databases on separate servers (dev and production) I need to move my data from dev to production from multiple tables without affecting the pre-existing data on production. Any idea if SQL Manager support something like this or am I going to have to write a script for it? My situation in detail: […]

SQL Server for xml returns custom xml format?

I have the following SQL with X(x) as (select ‘a’ union select ‘b’ ) , Y(y) as (select 1 union select 2 union select 3) select x, y from X cross join Y for xml path(”) and it returns <x>a</x> <y>1</y> <x>a</x> <y>2</y> <x>a</x> <y>3</y> <x>b</x> <y>1</y> <x>b</x> <y>2</y> <x>b</x> <y>3</y> Is it possible to […]

SQL OrderBy two columns as one

I have a table that has two columns: Height and Width What is the easiest way to sort this table incrementally based on either Height or Width In other words, it would pick the smallest value from Height and Width for each row and sort it based on this number? Thanks in advance!

Why specify primary key not null when creating a table?

A piece of sql in sql server CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) Primary key is not null by nature, why we still specify not null here? Do I miss anything?

SQL Server Memory Optimization

This is going to be my first attempt at fine tuning our SQL Server 2008R2, and I’d like a starting point based on the following. When I view the resource monitor, I see (in KB): Commit: 843,948 Working Set: 718,648 Shareable: 26,276 Private: 692,372 Out of 2 gigs available on our virtual server, 1.6 is […]

SQL Average Incorrect

I have the following query which returns Rate —— 4 6 8 SELECT Rate FROM t_Vote EXCEPT SELECT MAX(Rate) FROM t_Vote EXCEPT SELECT MIN(Rate) FROM t_Vote My aim is to get the average of those 3 numbers so I just need to replace the first SELECT Rate with SELECT AVG(Rate). But instead of getting 6 […]

T-SQL date conversion

I have the following date format 92845 which represent hhmmss. I would like to convert that to a datetime in SQL. I could use something like this: SELECT STUFF(STUFF(‘84936′,3,0,’-‘),6,0,’-‘) but it appears T-SQL is not liking the hour part. thank you!

T-SQL Syntax Issue Else if style logic

two questions today, I’m a busy bee and luckily I have an awesome community at my disposal! My issue here is this: I have a field i need to update based on existing field data. If Gender = F then foo = 1 If Gender = M then foo = 2 If Gender = Male […]

C# insert data into SQL table

I m getting sqlException was unhandled. Incorrect syntax near ‘nvarchar’ and Incorrect syntax near ‘Name’. I used SMO to create a table like this Table TechOpsProjectTracker = new Table(db, “TechOpsProjectTracker”); //Add column ID DataType dt = new DataType(SqlDataType.Int); Column idColumn = new Column(TechOpsProjectTracker, “ID”, dt); TechOpsProjectTracker.Columns.Add(idColumn); //Add Column Alignment DataType dt1 = new DataType(SqlDataType.NVarChar, 50); […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.