How to make indentity column continually?

Hello, everyone:

I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Thanks a lot

ZYTThis works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.|||1. Add a new column
2. Copy data from identity column to new column
3. drop identity column
4. change name of new column to old identity column
5. make new column an identity

BUT!

The fact that you want to do this tells me the column should not be an identity column.

You will forever more be worry about gaps in sequences, ect.

The identity column shouldn't be used for "ordering" data.|||Wow, Brett makes laws...

DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)

And why can't you order by identity column?|||And why can't you order by identity column?

I guess that didn't come out right...

They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.|||A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.

It's not a law. It's a principle, and a good one.|||It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!|||Dude, I can see your veins popping from here. That can't be healthy.

Yes, a clustered index is physically ordered. Duh.

But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.|||That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right? ;)|||I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Read the post...ok...

Dude...they run out of tequila in Texas?sql

How to make Fields dynamic in a table column?

I am developing a single dynamic report in report designer and I have
a small problem. My report has several parameters and I use MDX to
query from a Cube(Analysis Services). Everything works fine until I
want a field in the report to change dynamically when a user changes a
parameter.
Example:
I have one parameter, dimension, which is a drop down list. If the
user chooses 'region' as the dimension, I want the Field!Region.Value
to be my field in the table column. If he chooses 'store' I want
Field!Store.Value to show instead of Region or any other dimension in
the column. Is there any way to make a report dynamic like this in
report designer?
At this moment I have to define fields with the same adress as the
database location: 'Store' has field expression like this -
[Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
the dimension field when the user chooses 'Store' from the drop down
list, I`ll get all the results displayed in the table. This is what I
want to make more dynamic.
My simplyfied MDX query:
SELECT {[Measure].[Units Ordered]} on Columns,
{[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
from Orders
Does anyone have a solution to this problem?You can use the collection-based syntax for referencing field names in the
report layout. E.g.
=Fields(Parameters!DimensionSelection.Value).Value
Based on the actual parameter value, the field name will be determined at
report processing time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Oddvar Brennhovd" <oddvar.brennhovd@.isolutions.no> wrote in message
news:83475cf2.0502230717.48b871f6@.posting.google.com...
>I am developing a single dynamic report in report designer and I have
> a small problem. My report has several parameters and I use MDX to
> query from a Cube(Analysis Services). Everything works fine until I
> want a field in the report to change dynamically when a user changes a
> parameter.
> Example:
> I have one parameter, dimension, which is a drop down list. If the
> user chooses 'region' as the dimension, I want the Field!Region.Value
> to be my field in the table column. If he chooses 'store' I want
> Field!Store.Value to show instead of Region or any other dimension in
> the column. Is there any way to make a report dynamic like this in
> report designer?
> At this moment I have to define fields with the same adress as the
> database location: 'Store' has field expression like this -
> [Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
> the dimension field when the user chooses 'Store' from the drop down
> list, I`ll get all the results displayed in the table. This is what I
> want to make more dynamic.
> My simplyfied MDX query:
> SELECT {[Measure].[Units Ordered]} on Columns,
> {[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
> from Orders
> Does anyone have a solution to this problem?|||Thank you. Now the problem is solved!

how to make column lowercase

I have a table that contains names that are all in upper case, this column is called in many different areas of my web app. I wanted to make the names all lowercase, or with the leading character only capitalized.
How can I make a column within a SQL table lowercase at the SQL server end and not the programming side?

thanks,
Frank

UPDATE
 TableName
SET
 FieldName = LCASE(FieldName)

For all future entries, you create a trigger that will execute on insert.|||If you are talking about the data to be converted in lowercase, then following is the syntax:

select lower(column_name) from table_name

To convert first letter in Capital, you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You can use CHARINDEX to find the spaces, then UPPER the character following each space.|||How can I do this automatically in the Formula field of the Table Design.

thanks

How to make Column headers footer and the image repeat in all pages of report

Can you please help, i want to make the column headers, footer and the image repeat in all the pages of the report.

if my report has 5 pages then in all pages i want the image appear plus all the column names and footer to appear in every page.

Thanky ou very much for the information.

Hi,

If you are using Table in your report,For the table there are two properties

RepeatFooterOnNewPage->True

RepeatHeaderOnNewPage->True

Hope this helps.

|||Perfect Thank you Mahima.|||How is this done for when you export to excel? When I export with page breaks between my top level group, it renders fine on the report server and every page has the table header. But when I export to Excel the table header doesn't show up on the seperate pages/worksheets.
Any ideas?

How to make a column clustered

Here is my create table command

CREATE TABLE EXCLUSIVEITEM(VIEW_ID INTEGER NONCLUSTERED IDENTITY(1,1)PRIMARY KEY,
VIEW_LATEST_REPLY_DATE CLUSTERED INDEX DATETIME)

Is this the way to create one of my columns clustered. The books that I have all use stored procedure I only want to use regular sql commands. Is this possible?Indexes are clustered, columns aren't.

Can you post the assignment as you received it, or give us a URL to it? I'm still not clear on what you're trying to do, so I'm not much help in doing it!

-PatP

How to loop through a table?

Hi,
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to loop through a table?

Hi,
I have a stored procedure, SPUpdateStatus, with a parameter,
InstanceID.
InstanceID is a column in a table, "BatchInstance". I'll need to loop
through table, "BatchInstance", and execute SPUpdateStatus on each
InstanceID. What's the syntax for the loop?
Thanks!On 21 Mar, 15:35, "Curious" <fir5tsi...@.yahoo.com> wrote:
> Hi,
> I have a stored procedure, SPUpdateStatus, with a parameter,
> InstanceID.
> InstanceID is a column in a table, "BatchInstance". I'll need to loop
> through table, "BatchInstance", and execute SPUpdateStatus on each
> InstanceID. What's the syntax for the loop?
> Thanks!
Why not just amend SPUpdateStatus or create a new proc so that it can
perform the same operation on all rows at once? That way you won't
need a slow, complex and cumbersome loop to do the job.
If you aren't willing or able to do that then lookup the DECLARE
CURSOR syntax in Books Online. There are lots of reasons why cursors
are almost always a bad idea for data manipulation operations. You
should carefully consider the alternatives first.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

How to load images to table

I added a column to a table to hold images. How can I easily upload
these images from the disk file? There are only a few so i thought
there would be a command or utility that I could use, but I haven't
been able to find one.In SQL Server 2005 you can use the BULK option of OPENROWSET:
CREATE TABLE Foo ( image_data VARBINARY(MAX));
INSERT INTO Foo (image_data)
SELECT image_data
FROM OPENROWSET(BULK N'C:\image.jpg',
SINGLE_BLOB) AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Also take a look at the TEXTCOPY.exe utility. You can Google for TEXTCOPY,
and find a lot of info. But here's one
http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm
Linchi
"cs_hart@.yahoo.com" wrote:
> I added a column to a table to hold images. How can I easily upload
> these images from the disk file? There are only a few so i thought
> there would be a command or utility that I could use, but I haven't
> been able to find one.
>|||On Jan 22, 2:20 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> In SQL Server 2005 you can use the BULK option of OPENROWSET:
> CREATE TABLE Foo ( image_data VARBINARY(MAX));
> INSERT INTO Foo (image_data)
> SELECT image_data
> FROM OPENROWSET(BULK N'C:\image.jpg',
> SINGLE_BLOB) AS ImageSource(image_data);
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I tried this but get an error Incorrect syntax near the keyword 'BULK'.|||Are you using SQL Server 2005? As I noted this works only on SQL Server
2005. For uploading images to SQL Server 2000, you can see this example by
Erland Sommarskog:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

How to link new primary key column instead of old primary key column on existing

The earlier developer, developed reports very complex formatting reports, may be he was spend lot of time on formatting. Each report is fetching data from 7 to 8 tables. All are working fine. He did very good job. He didnt used Stored Procedures in it. For Complex reports SP are giving better performance as of my knowledge. Recently I joined in the organization they assigned me reports part.

Now the problem is

Recently the database was changed accordingly their convenience. He was developed reports using old database. In the old database one table they added one new column and assigned primary key, old primary key column as set to non-key column. Now I need to link the new primary column instead of old primary key column. I dont know how to change this one, I tried verify database command. But no luck. I check report export. I am confusing on that. Can you please help me to solve this one?



Thanks in advance

NadaI believe you have been answered on another forum.

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

how to knw which column is primary key in a table

hi all

my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table

i dont want to use any stored procedures only sql query

sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using

i only want to use sql query

select o.name as TableName,
c.name as ColumnName
from sysindexes i
inner join sysobjects o ON i.id = o.id and o.xtype='U'
inner join sysobjects o2 ON i.name = o2.name
and o2.parent_obj = i.id
and o2.xtype = 'PK'
inner join sysindexkeys i2 on i.id = i2.id
and i.indid = i2.indid
inner join syscolumns c ON i2.id = c.id
and i2.colid = c.colid
order by o.name,i2.keyno

|||

It is easier to use the information_schema view key_column_usage and the objectproperty function:

select table_schema + '.' + table_name as table_name, column_name
from information_schema.key_column_usage
where objectproperty(object_id(constraint_name),'IsPrimaryKey') = 1
order by table_schema, table_name

how to know space used per column

I know I can use sp_spaceused to check space used per table.
However, how can I know space used per column?SELECT SUM(DATALENGTH(<column_name> )) should get you pretty close to that.
Jacco Schalkwijk
SQL Server MVP
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:eMxBo4RBFHA.608@.TK2MSFTNGP15.phx.gbl...
>I know I can use sp_spaceused to check space used per table.
> However, how can I know space used per column?
>

How to know a column whether indexed or not in sql2k

i'll be appreciated any help !Hi
Lookup COLUMNPROPERTY in the BOL
"joy.net" <luxingyu1@.hotmail.com> wrote in message
news:%233BF4Ff2FHA.472@.TK2MSFTNGP15.phx.gbl...
> i'll be appreciated any help !
>
>

how to know a column was indexed or not ?

hi
iam a csharp developer . the problem is i don't know how to select in
systables of sqlserverThe systable is in the master database. I hope the user you are connecting
through has permission to access the master database.
In case the user does have permission you will be able to query... find a
sample query below
select * from master.dbo.syscolumns
"joy.net" wrote:

> hi
> iam a csharp developer . the problem is i don't know how to select in
> systables of sqlserver
>
>|||sysDatabases is in Master, syscolumns, sysobjects only gives you the
tables,indexes etc that are contained in master, it does not give a 'global'
view of every table.
If you want the columns, tables, indexes for the current database you need
to use the system objects in that database.
select * from TargetDatabase..sysobjects where xtype = 'u'
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:F74BF6BD-22F0-41A5-97D8-35775813157E@.microsoft.com...
> The systable is in the master database. I hope the user you are connecting
> through has permission to access the master database.
> In case the user does have permission you will be able to query... find a
> sample query below
> select * from master.dbo.syscolumns
> "joy.net" wrote:
>

how to know a column was indexed or not ?

hi
iam a csharp developer . the problem is i don't know how to select in
systables of sqlserverHi Joy
Just check this query:
SELECT i.name, o.name
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
WHERE indid > 0 AND indid < 255 AND o.type = 'U'
this might help you.
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"joy.net" wrote:

> hi
> iam a csharp developer . the problem is i don't know how to select in
> systables of sqlserver
>
>
>