Hi, I want to replace the indexes in my database to a different
filegroup. How can I do that using T-SQL? I only found a way that
uses the EM, but I have a lot of indexes and I hate to do it manually.
Thanks a lot.
Hi, I want to replace the indexes in my database to a different
filegroup. How can I do that using T-SQL? I only found a way that
uses the EM, but I have a lot of indexes and I hate to do it manually.
Thanks a lot.
Hello,
I have an PK Clustered index that I would like to move to another filegroup, which will inturn reside on another drive. Can someone point me to sql tsql or somewhere on bol that has the step by step on how to accomplish this? Thanks in advance.
John
We are using an application that stores it's data in SQL Server 2005. The application manages the creation of all it's own objects, including indexes. I would like to migrate the index data to a new filegroup, but there is no place in the application to specify how it builds it's indexes. I assume the indexes are created without specifying a filegroup so the default Primary is used. So, I'm wondering if there is a database option somewhere that you can set the default filegroup for index data?
If not, the only way I can think of accomplishing this goal is through DDL triggers to capture Create Index and Create Table statements and overwrite the Filegroup there.
Thanks in advance.
-Preston M. Price
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....
Regards
Karen
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
Hi,
Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?
Thank You for any help!
Eric
what happens if the physical location of a box(which had sql server 2000 on it) is chaned.
what happens to the replication and distributed queries.
Thanks.
I have one big db and i heve no place on disk
the log file is big too. how can i delete the log
I am fairly versed with SQL 2005 as I have been using it since it came out. Now I have need to learn about Cubes.
Does anybody have any suggestions of where I need to begin to learn about what cubes are, how to create them and use them?
Ultimately we will be incorporating them into Reporting Services.
Thanks for the information.
Where would i place an orderby my DateCreated field...everywhere i try to place it i get this error...
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Any help with this issue would be greatly appreciated....
CREATE PROCEDURE GetTimeCard
( @LoginID nvarchar(50),
@DateRangeFrom datetime,
@DateRangeTo datetime
)
AS
BEGIN
IF ( @DateRangeFrom = '1/1/1753' ) AND ( @DateRangeTo = '1/1/1753' )
select x.*, x1.TotalExpenses, x2.WorkedCount
from (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
union
select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
ELSE
select x.*, x1.TotalExpenses, x2.WorkedCount
from (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName
union
select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount
from OPS_TimeCards tc
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
End
GO
I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.
Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?
dennist685
I'm rapidly understanding that much more of my application as a whole is in SQL Server that I would have originally thought.
Stored Procedures
Triggers
Constrains
And so on
It generally means that some of the stuff I'd have naturally done in the Business Layer might be best done in SQL - certain issues in the Business Layer might be best being triggers or constraints for example...
One thing that still puzzles me, and I'd like some references or advice now as it's a blank area in my mind is how this interfaces to your asp.net code.
Obviously I call stored procedures and the like from code, and use parameters, etc, not problem, it's more what I do when these stored procedures or associated triggers fail (or a constrain fails - though this should be less likely)?
SQL sends back an error? But what? Then what do you get your page to do, especially if SQL failed midway through a 'big' transaction? Do you have save 'where the user was somehow' so they don't start inputting again?
It's all a bit vague at the moment, some detail would be nice? :)
Hello all,I have an SQL query which retrieves a COUNT number from 2 different tables, and i want to do a division with botht he COUNT data retrieved. Trouble is I can't get it in the format that I want, my SQL query is as below :-SELECT ROUND( ((T1.Present/T2.Total ) * 100), 2) FROM(Select Count(Date) as Present from Attendance WHERE Month(Date)=12 AND Status=1) T1,(Select Count(Date) as Total from Attendance WHERE Month(Date)=12) T2The trouble here is that the result should be as below: T1.Present = 3T2.Total = 5 T1.Present/T2.Total = 3/5 = 0.6The final should be 60 after divided by 100But i am getting a zero as my result, even when I don't multiply the number by 100, the division result is still zero. I am guessing it is a conversion problem. Could anyone please offer me any advise on how to get the final result in the format I want?
View 2 Replies View RelatedOne of our customers has a production and a test database. We are loading tables via a C++ program that works on production, but not on test, which is supposed to be an exact duplicate.
The error we are getting when trying to add columns to one of the table is 80004e21 null That is it.
When we load the exact same records in the production environment we do not get the error. We have spent many hours ensuring that the tables are exactly the same on test as in production.
Does anyone know, is there an environment variable at a database level that says how to handle null values?
Thanks!!!
C
If I have a publisher, distributor, and subscribers all running SQL 6.5, and I wish to upgrade to SQL 7.0, in what order to I upgrade?
View 1 Replies View RelatedHi All!
I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:isl_fs1DBName.bak'
BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\isl_fs1DBName.bak'
it doesn't work please help
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement
For Example: Instead of using the following with each database hardcoded in the SP:
select @dataused = sum(b.reserved)
from DBSglep..sysindexes b
where b.indid in (0, 1, 255) and segment != 2
I would like to loop for every database listed in sysdatabases and do this:
select @dbname = @dataname+'..sysindexes'
select @dataused = sum(b.reserved
from @dbname b
where b.indid in (0, 1, 255) and segment != 2
I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).
Any assistance in resolving this problem would be greatly appreciated! :-)
Many thanks in advance...
Regards,
Wayne
Last 2 nights (at night) my SQL Server has locked up, first night trying to back up MSDB(20 meg) and last night ran out of memory at 6:30 AM. No users on at either time, no jobs running on the second night. I was going to turn perfmon on tonite. Any input on what best to monitor?
View 2 Replies View RelatedHello,I run out of space on the disk, my db log is 130GB, I need to shrink db log. I made a backup through network by UNC of the database, but when I want to backup log, I get a message :BACKUP LOG cannot be performed because there is no current database backup.But the backup is reachable, I can restore from it.Any ideas ?
View 8 Replies View RelatedI have a very simple query that gets a field to use as constraints in another query.
Code:
SELECT A.RIN
FROM apcType T
INNER JOIN apcAttribute a on A.T = T.RIN
WHERE T.Name like '%Sales'
The results of the first query are used in the following query where it is bolded and marked with and <<<<========
Code:
SELECT AP.Arg2, AP.Arg3, M.Parcel,
M.Serial, M.Name, M.Acres, M.District,
V.YearBuilt, V.Code, V.Size,
(SELECT SUM(V1.Acres)
FROM TRValue V1
WHERE V1.Year = V.Year and V1.Parcel = V.Parcel and
SUBSTRING(V1.Code, 1, 1) = 'L'
[code]....
My question is How can I fold the first query into the second?
thanks for reading.
i'm wondering if i can use a stored procedure in place of a UDF in the case where i want a return value based on a simple calculation involving the input parameter.
i'd like to use this inline in a query somewhere else. that's why the UDF.
the UDF would be something like this ...
create function getFiscalYear (@when datetime)
returns int
as
BEGIN
declare @rv int
-- months of Oct, Nov, Dec are rolled into following year
if datepart(month, @when) >= 10
select @rv = datepart(year, @when) + 1
-- whereas all other months stay in this year
else
select @rv = datepart(year, @when)
end
END
so, the only reason i'm not using this UDF (and haven't tested it either) is because i can't find (or can't remember how :( ) to add a UDF to my database. when i run this code in Query Analyzer i get an error on the keyword "function". but that's another question altogether.
thanks in advance. (a) for helping with a stored procedure that does the equivalent or (b) nudging me in the right direction towards getting UDFs to work in my SQL 2000 install.
Hi All!
I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:isl_fs1DBName.bak'
BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\isl_fs1DBName.bak'
it doesn't work please help
I have a table called Register that has the following in it
Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'
I need to identify the record that is out of place..In the example below record number 2.
example
policy_number Policy_date_time Portfolio_set, status
12345 1/1/2011 1 I
12345 1/2/2011 1 A
12345 1/3/2011 1 I
12345 1/4/2011 1 I
I need to identify that the second record is in the wrong place...
I have a stored procedure that starts like this:
.
.
.
UPDATE Employees
set depth=0, hierarchy=NULL
UPDATE Employees
set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength)
where Child = Parent
WHILE EXISTS
(
SELECT *
FROM Employees
WHERE Depth=0
)
.
.
.
I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?
Thanks
i have a table name is HH tableit has two columns 'hhno' and hhname'HH tabelehhno hhname100 suresh101 baba103 rami want to insert a one record(102 , chandra) in HH table between(101,baba) and( 103 ,ram).how can i insert them please help ,me thanks
View 10 Replies View Related
How am I configuring permission for an SQL database, that user within my program should'nt read private information?
as well how can I protect my database with a UN & PWD to allow access only for authenticated users, not within the program, just the *.mdf file?
Hi,
While we choose in-place upgrade for upgrading to SQL Server 2005 from SQL Server 2000, does it actually change the code in Stored Procedures/functions also?
e.g. if I have *= in some old procedure does it change it to INNER JOIN while migrating?
Please help me on this ASAP.
Regards,
Rahul
Hi all of you,
My current dutie is try to obtain for each table their filegroup.
I'm seeing sysobjects table but I can't see nothing related to do with
Thanks a lot for your time,
I am totally new to the BI world. I was just wondering where I should start to begin setting up a BI solution. I started trying to go through the Microsoft Anaylsis Services tutorial line for line in the MSDN website. I couldn't get the sample solution to work quite right. Should I be starting somewhere more simple to start. Please help, so far it looks a bit overwelming, especially with the terminology and examples I have seen so far from various sites about BI and Data Mining.
View 1 Replies View RelatedHi,
I have the following problem... In my table I store calling registries...and when a call is transfered to another branch the last brach have to receive all the time...
Só I have the following table
Code Snippet
CREATE TABLE [BILHETES_PROCESSAR] (
[COD_CLIENT] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DATE_TIME] [datetime] NOT NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[BRANCH] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO_ORIGINAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,-- Tells if is a originate call "2" or received a tranfer "6"
[TRONCO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , -- The port of the router through the call have been made
[TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that is going to receive this call
[ORIGEM_TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that sent the call
[VALOR_TEMPO] [money] NULL ,
[ID_TRANSF] [uniqueidentifier] NULL ,--ID for the branch that originated the call
[ID_REGISTRO] [uniqueidentifier] NOT NULL --ID of the registry
) ON [PRIMARY]
GO
For this table I can have the following data
Code Snippet1002 2007-07-30 17:52:34.000 01191084426 236 2 2 9 210 NULL 15.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4
1002 2007-07-30 17:53:17.000 01191084426 210 2 6 9 214 236 63.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 D680C38D-C217-461A-8207-9FC367FBB51C
1002 2007-07-30 17:54:30.000 01191084426 214 2 6 9 NULL 210 50.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 24B3073B-F45F-4E87-9796-55F37847473D
So I got this results running this query..
Code Snippet
UPDATE P SET P.TRANSF=P2.BRANCH,
P.ID_TRANSF=
CASE
WHEN P.TIPO_ORIGINAL = '2' THEN P.ID_REGISTRO
WHEN P2.ID_TRANSF IS NOT NULL THEN P2.ID_TRANSF
ELSE '1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P.TRANSF IS NULL
UPDATE P2 SET P2.ORIGEM_TRANSF=P.BRANCH, P2.ID_TRANSF=
CASE
WHEN P.ID_TRANSF IS NOT NULL THEN P.ID_TRANSF
ELSE P.ID_REGISTRO
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ORIGEM_TRANSF IS NULL
UPDATE P2 SET P2.ID_TRANSF=P.ID_TRANSF
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ID_TRANSF='1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'
This query has only one problem if the user has made a 4º transfer th2 two last registry will have wrong "transf" and "origem_transf" values... but this is not my big problem...
The big problem is that I need to sum all it "valor_tempo" put it on the last registry... that is the one with the code "24B3073B-F45F-4E87-9796-55F37847473D" and then set the "valor_tempo" to 0 to the other registry...
Did you get the ideia ??
Thanks
Hello,
My english is not the best, but i will try my best.
I want to change the saving folder for the database from my systempartition to my datapartition (from c:labla to d:mydata), but i dont know how. I can see the folder but it is in gray font and unchangeable.
Greetings Thomas