EzDevInfo.com

nvarchar(max) vs NText

What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don't need backward compatibility, so it is fine that nvarchar(max) isn't supported in older SQL Server releases.

Edit: Apparently the question also applies to TEXT and IMAGE vs. varchar(max) and varbinary(max), for those searching for those data-types later.

Thanks!

How can I do an UPDATE statement with JOIN in SQL?

I need to update this table in SQL Server 2005 with data from its 'parent' table, see below:

sale

id (int)
udid (int)
assid (int)

ud

id  (int)
assid  (int)

sale.assid contains the correct value to update ud.assid. What query will do this? I'm thinking a join but I'm not sure if it's possible.

Get size of all tables in database

I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.

Is there an easy way to determine how much space on disk each table is consuming?

Insert results of a Stored Procedure into a Temporary Table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select * 
into tmpBusLine
from BusinessLine

Trying the same, but using a stored procedure that returns data, is not quite the same.

select * 
into tmpBusLine 
from
exec getBusinessLineHistory '16 Mar 2009'

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

How do I split a string so I can access item x?

Using SQL Server 2005, how do I split a string so I can access item x?

For example

Take the string "Hello John Smith". How can I split the string by a space and access the item at Index 1 which should return "John"?

check if temp table exist and delete if it exists before creating a temp table

I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give an error saying "invalid column". Please let me know what I am doing wrong.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company             CHAR(3),
    StepId              TINYINT,
    FieldId             TINYINT,
)

select company, stepid, fieldid from #Results

--Works fine to this point

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company             CHAR(3),
    StepId              TINYINT,    
    FieldId             TINYINT,
    NewColumn           NVARCHAR(50)
)

select company, stepid, fieldid, NewColumn from #Results

--Does not work

SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables.

Example:

select
    im.itemid
    ,im.sku as iSku
    ,gm.SKU as GSKU
    ,mm.ManufacturerId as ManuId
    ,mm.ManufacturerName
    ,im.mf_item_number
    ,mm.ManufacturerID
from 
    item_master im, group_master gm, Manufacturer_Master mm 
where
    im.mf_item_number like 'STA%'
    and im.sku=gm.sku
    and gm.ManufacturerID = mm.ManufacturerID
    and gm.manufacturerID=34

I want to update the mf_item_number field values of table item_master with some other value which is joined in the above condition.

How can I do this in MS SQL Server?

Are there any disadvantages to always using nvarchar(MAX)?

In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't able to limit the field length at the database level)

How do you kill all current connections to a SQL Server 2005 database?

I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active.

How can I kill all the connections to the database so that I can rename it?

How do I escape a single quote in SQL Server?

I'm trying to insert some text data into a table in SQL Server 9.

The text includes a single quote.

How do I escape that?

I tried using two single quotes, but it threw me some errors.

eg. insert into my_table values('hi, my name''s tim.');

Why use the INCLUDE clause when creating an index?

While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways.

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

-- OR --

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

The INCLUDE clause is new to me. Why would you use it and what guidelines would you suggest in determining whether to create a covering index with or without the INCLUDE clause?

What represents a double in sql server?

I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float?

This will store latitude and longitude values, so I need the most accurate precision.

Thanks for the responses so far.

How do you specify a different port number in SQL Management Studio?

I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL Management Studio?

Simulating group_concat MySQL function in SQL Server?

I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).

In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently.

group_concat, by the way, does this: given a table of, say, employee names and projects...

SELECT empName, projID FROM project_members;

returns:

ANDY   |  A100
ANDY   |  B391
ANDY   |  X010
TOM    |  A100
TOM    |  A510

... and here's what you get with group_concat:

SELECT 
    empName, group_concat(projID SEPARATOR ' / ') 
FROM 
    project_members 
GROUP BY 
    empName;

returns:

ANDY   |  A100 / B391 / X010
TOM    |  A100 / A510

...So what I'd like to know is: Is it possible to write, say, a user-defined function in SQL Server which emulates the functionality of group_concat? I have almost no experience using UDFs, stored procedures, or anything like that -- just straight-up SQL -- so please err on the side of too much explanation :)

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

• Use a DELETE without a where clause and then RESEED the identity (or)
• Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.