Hello
I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?
Thanking You
Navaneeth
I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance
View 1 Replies View RelatedHi,
I am declaring a table with 2 column Id, and Name.
Id is a integer column but Name is a Varchar.
I am declaring Name as VARCHAR(100) you can say that varchar(100) is more than enougf for a name column. But lets suppose ifn futuer user will enter data only till 100 characters but lets suppose i put or declare it as NAME VARCHAR(4000), That means a column requires only 100 memory space but i am giving 4000 to that column
So in case of SQL any drwbacks are there for this approach. Meaning my 3900 space will not be used so will it a wrong thing or we can declare it as ,,, it will put no effect???
or what are the drawback??
I have a huge table in it own tablespace (250 GB). I just deleted more thanhalf of the columns in that table, but I didn't see any change in allocatedspace. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also saysthat CLEANTABLE only reclaims space for columns with text, char datatypes.How can I get space back for integer, decimal, number datatypes? I'm justall out of ideas.
View 7 Replies View Related
I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.
I have a table in which there is a column called 'Forecast' of type 'int', that is null for about 25% of the rows in this table.
Will null value for this column consume storage bytes?
I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only  466,031MB !
That means the Table with SPARSE column need more storage, Why?Â
By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)
hi,
This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):
2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0
[Column 12] == "0" ? [Column 12] : ?????????
TIA
I want to alter my all tables to change the name of all columns.
Â
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?
Thanks.
All,
I've converted datatypes in the past to something more appropriate in order to save space, and speed up the database, to great success. I'm in the middle of another such change, this one table in particular I am converting from [text] columns to varchar(max)'s, and varchar(1)'s to char(1)'s. I've made a duplicate of the table in question (which has about 25 text columns, and 20 varchar(1) columns), ported over the data, and ran 'sp_spaceused' and got the following results:
-- new table
name | rows | reserved | data | index_size | unused
Response | 39920 | 15384 KB | 15168 KB | 208 KB | 8 KB
-- old table
name | rows | reserved | data | index_size | unused
Response | 39920 | 124128 KB | 123696 KB | 200 KB | 232 KB
I didn't expect results quite this dramatic. Now to my question: Did I miss something measuring the difference? Is there something else, another method I should employ to determine the size differentials?
If this is right, it's outstanding, just unexpected. I would have been happy losing 10 or 20 MB.
I appreciate any insight anyone might be able to provide.
Best,
B.
Just fyi - here are the table schemas:
-- old table
CREATE TABLE [dbo].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [varchar](1) NULL,
[ItemComments1] [text] NULL,
[ItemAnswer2] [varchar](1) NULL,
[ItemComments2] [text] NULL,
[ItemAnswer3] [varchar](1) NULL,
[ItemComments3] [text] NULL,
[ItemAnswer4] [varchar](1) NULL,
[ItemComments4] [text] NULL,
[ItemAnswer5] [varchar](1) NULL,
[ItemComments5] [text] NULL,
[ItemAnswer6] [varchar](1) NULL,
[ItemComments6] [text] NULL,
[ItemAnswer7] [varchar](1) NULL,
[ItemComments7] [text] NULL,
[ItemAnswer8] [varchar](1) NULL,
[ItemComments8] [text] NULL,
[ItemAnswer9] [varchar](1) NULL,
[ItemComments9] [text] NULL,
[ItemAnswer10] [varchar](1) NULL,
[ItemComments10] [text] NULL,
[ItemAnswer11] [varchar](1) NULL,
[ItemComments11] [text] NULL,
[ItemAnswer12] [varchar](1) NULL,
[ItemComments12] [text] NULL,
[ItemAnswer13] [varchar](1) NULL,
[ItemComments13] [text] NULL,
[ItemAnswer14] [varchar](1) NULL,
[ItemComments14] [text] NULL,
[ItemAnswer15] [varchar](1) NULL,
[ItemComments15] [text] NULL,
[ItemAnswer16] [varchar](1) NULL,
[ItemComments16] [text] NULL,
[ItemAnswer17] [varchar](1) NULL,
[ItemComments17] [text] NULL,
[ItemAnswer18] [varchar](1) NULL,
[ItemComments18] [text] NULL,
[ItemAnswer19] [varchar](1) NULL,
[ItemComments19] [text] NULL,
[ItemAnswer20] [varchar](1) NULL,
[ItemComments20] [text] NULL,
[EssayQuestionAnswer1] [text] NULL,
[EssayQuestionAnswer2] [text] NULL,
[EssayQuestionAnswer3] [text] NULL,
[EssayQuestionAnswer4] [text] NULL,
[EssayQuestionAnswer5] [text] NULL,
CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- new table
CREATE TABLE [Test].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [char](1) NULL,
[ItemComments1] [varchar](max) NULL,
[ItemAnswer2] [char](1) NULL,
[ItemComments2] [varchar](max) NULL,
[ItemAnswer3] [char](1) NULL,
[ItemComments3] [varchar](max) NULL,
[ItemAnswer4] [char](1) NULL,
[ItemComments4] [varchar](max) NULL,
[ItemAnswer5] [char](1) NULL,
[ItemComments5] [varchar](max) NULL,
[ItemAnswer6] [char](1) NULL,
[ItemComments6] [varchar](max) NULL,
[ItemAnswer7] [char](1) NULL,
[ItemComments7] [varchar](max) NULL,
[ItemAnswer8] [char](1) NULL,
[ItemComments8] [varchar](max) NULL,
[ItemAnswer9] [char](1) NULL,
[ItemComments9] [varchar](max) NULL,
[ItemAnswer10] [char](1) NULL,
[ItemComments10] [varchar](max) NULL,
[ItemAnswer11] [char](1) NULL,
[ItemComments11] [varchar](max) NULL,
[ItemAnswer12] [char](1) NULL,
[ItemComments12] [varchar](max) NULL,
[ItemAnswer13] [char](1) NULL,
[ItemComments13] [varchar](max) NULL,
[ItemAnswer14] [char](1) NULL,
[ItemComments14] [varchar](max) NULL,
[ItemAnswer15] [char](1) NULL,
[ItemComments15] [varchar](max) NULL,
[ItemAnswer16] [char](1) NULL,
[ItemComments16] [varchar](max) NULL,
[ItemAnswer17] [char](1) NULL,
[ItemComments17] [varchar](max) NULL,
[ItemAnswer18] [char](1) NULL,
[ItemComments18] [varchar](max) NULL,
[ItemAnswer19] [char](1) NULL,
[ItemComments19] [varchar](max) NULL,
[ItemAnswer20] [char](1) NULL,
[ItemComments20] [varchar](max) NULL,
[EssayQuestionAnswer1] [varchar](max) NULL,
[EssayQuestionAnswer2] [varchar](max) NULL,
[EssayQuestionAnswer3] [varchar](max) NULL,
[EssayQuestionAnswer4] [varchar](max) NULL,
[EssayQuestionAnswer5] [varchar](max) NULL,
CONSTRAINT [PK_Test_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.
View 5 Replies View RelatedHi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedHi All,
We have two production boxes running different ERP Apps on each boxes, We just added 2 more cpu's to one box and 1 cpu to other. The Currect CPUS config as stands is
Box 1 = 4 CPU's
Box 2 = 2 CPU's
My boss whats some stats to see if performace was any better and if yes how much !!
How can i find such information from my box ?
Can anyonw help with this stat's ? !!
Thanks in advance
Girish
Hi all,
I have a problem in trying to find out why only one CPU in a 2 CPU H-T utilized. Using task manager, I can see 4 processors windows but only 1 actually utilized. I select +boost sql server and select all CPU for use. Queries and all other command ie dbcheck.. appear to use only single cpu.
Any help would be nice.
thanks
Andrew
Hi All,
I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.
But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.
Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?
Any help is appreciated.
Thanks.
We have a SQL 7.0 Standard Server running on a Windows 2000 Servermachine with 2 800mhz Pentium III with 2GB memory. Our front end isAccess 97 and 2000 with most ADO connections for the scripts but someDAO for forms and reports. We recently "released" a new version ofthe "database" that caused a catastrophic event to start happeningwith our SQL server.Using PerfMon we monitored the CPU utilization on the server andnoticed that the CPU load would drop to 0 for approx 5-10 seconds andthen jump back up to our average 60-70% utilization. During thisdrop, there is NO disk activity no new connections being made, etc.We then took the process a step further and loaded a "stress" programthat put about 30% load on the server to start with. Then wemonitored each processes load. SQL Server process would drop to 0%while the stress process continued at 30%.The problem is that the SQL does absolutely NOTHING for 5-10 seconds.You cannot connect, any querys that are running stop, their is no diskactivity (logs, data drives), and you cannot even get sp_who2 to runfrom Query Analyser. We thought maybe blocking (we have built an"app" that monitors this), but we don't see any blocking before itlocks and nothing after it locks.Out of despiration we "rolled back" to our previous version to getpeople working again. After business hours, we have tried toduplicate the problem on machines (2 or 3 at a time) but cannot get itto duplicate the problem.The only experience we had previous to this was using DNS to resolvethe server name which caused a problem EXTREMELY familar to thisproblem. However, we have double checked every machine we have, andnone of them are using DNS to resolve.Any idea's would be most appreciated.Patrick Moore
View 2 Replies View RelatedSQL 7 SP2
Hi.
True or false on these:
1. A table has a PK of EmployeeID (non-clustered). The sql statement where clause uses something like "WHERE E.Action > 1 AND E.User = 1001 AND E.EmployeeID = 12345
Question: Will the PK index be used in determining the result set ?
2. A table has an index of EmployeeID + Company + State (clustered). The sql statement where clause is "Where EmployeeID = 1001".
Question: Will the index be used in determining the result set ?
Thanks,
Craig
I installed 4GB of memory and I have never seen SQL memory utilization go beyond 2GB. I have SQL server set up to use as much memory as it needs. Does anyone no if SQL server can make use of more than 2GB.
View 1 Replies View RelatedOurs is a SQL 7.0 Enterprise edition with NT 4.0 Enterprise Edition. SQL Server has been configured with the default, 'Dynamic memory Allocation'. The system has 4GB of RAM. This is a dedicated SQL Server machine. But SQL Server seems to use only 1.8GB RAM(Counter: Total Server Memory) The page faults seem to be a max. of 600 and an avg. of 100. The processor utilization has suddenly increased to 90%. Is there anything wrong with the way SQL server is using memory? Is is not true that SQL Server 7.0 Enterprise edition can use upto 3GB RAM in a 4GB system?
Are there any links that can help troubleshoot this problem?
Thank you.
-Praveena
After a fresh install of SQL 6.5 with SP5a(or without), the cpu is running at anywhere from 50%-80%. It is loaded on a PDC, but when I stop the sql service cpu utilization drops to 0-2%. When I start the sql service it's right back up there, does anyone have a suggestion as to how to fix this or why the service would be doing this?
Thanks, Christel
I am getting high CPU utilization on the SQL Server process (>90%).
However the overall utilization (NT -- entire box) always seems to be under 50%.
Can someone explain why this is happening. The server is a quad; the SQL server process seems to be using only two CPUs at a time (not the same ones all the time).
Lightweitht pooling has been turned on and the maximum worker thread size has been left at the default value (255).
How can I configure SQL options to spread the load across all four CPUs ??????
I have windows 2003 server with SQL Server installed on it for live calls billing but the CPU utilization is reaching the maximam and it's average above 60% which is causing lot of problems specially for the live environment. I have enough memory and free hard disk space is more than 40GB,
so where is the problem?!
What do people think is normal for memory utilization? I know that's toobroad, so here are some basics.MS SQL Server 2000, Windows 2000 Server, 2GB RAMDb 1, size = 2.0 GBDb 2, size = 300MBDb 3, size = 50MBDb 4, size = 30MBDb 5, size = 30MBTypically 4-6 users, moderate usage 8-hrs/day. Performance has not slowed.Reboot on Sunday. sqlservr.exe in the Task Manager reports the followingSun 61MBMon 200MBTues 800MBWed 1,124MBThu 1,424MBFri 1,303MBI was getting srv 2020 errors when I had just 1 GB RAM: "The server wasunable to allocate from the system paged pool because the pool was empty."Then I did several updates to address this and got more RAM. I haven't seenthe errors since, but I haven't waited for them to happen: I'm rebootingevery week now. The memory numbers make me suspect SQL Server.Scratching my head. Not sure if my problem is gone, and this is normal SQLServer 2000 behavior, or if my problem is still lurking and I've only mutedit a bit.Any thoughts greatly appreciated.Tom
View 3 Replies View RelatedI have windows 2003 server with SQL Server installed on it for live calls billing but the CPU utilization is reaching the maximam and it's average above 60% which is causing lot of problems specially for the live environment. I have enough memory and free hard disk space is more than 40GB,
so where is the problem?!
Dear all,
One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.
I have to migrate few databases on the same server.
With high IO Operations.
I know server require more RAM, but how can i prove that server needs more RAM ?
Regards
Mohd Sufian