|
Hi,
My question is that,when we can create Clustered and NonClustered Index on tables???
What is the purpose behind it???
Thanks...
|
|
|
|
|
The minimum research will get you this[^] reference
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A clustered index means that when you get to the end the end leaf of the index, you are at the physical location of the data you are looking for. This makes reading data very fast. None clustered indexes by contrast point to a physical location of data - this means that when you do reach the end leaf on a non-clustered index the disk head may have to move to get you your data.
The one downside of a clustered index is that if you have a lot of inserts to the indexed table a clustered index will be disadvantageous. This is because every insert into the table will probably require a reorganisation of the physical location of some rows in the table that are not connected to the rows that you are inserting.
The simple answer is - a clustered index is very useful when you have a lot of reading and not much writing.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Greetings,
I have a database creation script. I've run the script after changed the path within the 'FILENAME' option to create the database on an external hard disk and I was able to see and select data from tables as the script run with no errors. However, when I tried to connect to the database from an application and read data from database tables, an exception thrown for SQL Server error 823 "The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000132000 in file 'Database name'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.". I've tried to run database consistency commands against it but they did not carried out and I got an error message. Also when I tried to drop or recover the database, dropping and recovering process failed. Finally, I had to make the database offline then I dropped it. I need to create such database on my external hard disk not on my primary one. Can anyone help?!!!
|
|
|
|
|
Why on an external HD?
SQL Server does not support plug & play; you may loose data if the drive is removed without detaching the database. If you just want to protect the data then I'd suggest backup up to the external HD and not using it as the servers' datasource.
IIRC, then only network drives are 'disallowed'. If you want to setup the data-files to be on an external disc, you'd be interested in this[^] thread.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
HI,
I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships.
For e.g:-
I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint
code:-
Alter Table dbo.EmployeeLeave
Add Constraint rfkcEmployeeID Foreign Key(EmployeeID)
References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION
So, My question is that i can even use this Constraint at the time of Creating Table
For e.g:-
Create Table dbo.EmployeeLeave
(
EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
Is this code is correct???
Is it will work???
Thanks...
|
|
|
|
|
What happened when you tried it?
|
|
|
|
|
Member 11161625 wrote: Is this code is correct???
Syntactic it is correct, but it will do nothing, as you instructed it. That is the default behaviour.
If you want the "leave" days to be removed from the database when the employee is removed from the database, then you'd want a cascading delete. (ON DELETE CASCADE, not "NO ACTION"). That way you can prevent getting "orphaned" records.
If you still need the leave-data from that person and don't want to wipe the records, I'd recommend "ON DELETE SET NULL", since the key would have become invalid - but then the record isn't deleted, just the key is removed - handy if you need the leave-data for statistics or other stuff.
Last, there is also a "SET DEFAULT" option; if a manager is deleted from the database, then you might want to automatically update all affected workers to some default-manager.
Member 11161625 wrote: Is it will work???
Depends on the specs. Press F5 and see if it does what you expect. Disclaimer; various database-engines may use different keywords and not support them all.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Federal Reserve note and no issued by a private bank in 1944 at the in world war two the Breton Woods Accord was made in world war two United States was recognized as the developing worldwide economy and by then thereto Woods accord pegged all monetary forms to the US dollars that universal exchange could be could be smoothed out and starting there for essentially Cash Camp all merchandise were evaluated in US dollar the US dollar was pegged at the at the it was picked to gold and the peg.
http://t-rexmusclefacts.com/cash-camp/[^]
|
|
|
|
|
create or replace FUNCTION Activation2(v_status OUT varchar,v_status1 OUT varchar)
returns setof sc_register AS $$
declare
begin
SELECT st_value into v_status FROM sc_activation_dtls WHERE si_slno = 2;
select * from sc_register ;
end;
$$
LANGUAGE 'plpgsql' VOLATILE
aftr writng above plsql functiopn in postgresql pane m getting follwing error
function result type must be record because of OUT parameters
doest it mean that we cant use output parameter and return statent togather.
since my requirement is to get some value of table through output parameters along with records of different table ..in one function only.
Thanks
|
|
|
|
|
Hi,
I want to know that while creating suqueries in SQL Server,it is mandatory to have Primary key and Foreign key relationships between the tables???
In short:-I want to know if we create subqueries without p.k and F.k realtionships,then it will work???
What is the main purpose of Subqueries???
Thanks...
|
|
|
|
|
In Short: Yes
The relationship structure is an integral part of the data structure but is not required for sub queries. Sub queries are just another tool for getting your data from the database the way you want it. Like CTEs and the various joins they are all aspects of TSQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have often wondered this myself. Since I can perform a join simply by saying "WHERE a.field = b.field", what is the ultimate purpose of setting up a relationship?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Richard Andrew x64 what is the ultimate purpose of setting up a relationship?
Referential Integrity[^]
Wonde Tadesse
|
|
|
|
|
Wonde nailed it, IMHO referential integrity and constraints are the last line of defense to keep the garbage in. They also help with optimisation of course.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There are number of laptops installed a window application connected with local database and all these machines works in remote area where no internet connectiviy is there. we have another web application connected to a central db.the Idea to synchroze database is that every machines export there data at the end of day and save the exported file in removable disk and place all the exported file to a specific location using FTP.
My requirement is to import all the exported files into central database and vice-versa. there are around 15 table out of 100 on which need to keep the synchronization.
Please suggest.
|
|
|
|
|
There may be a method to do this using replication but I have never explored that avenue, I have used a brute force method by designing my database to used GUIDs for the primary key and therefore the tables should be mergable.
Another way is to add a machine id to each table/record so the records are unique across databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can we achieve this using Microsoft sync framework or some framework?
modified 15-Dec-15 5:07am.
|
|
|
|
|
I doubt it, I have not worked with any of them but I have never heard of of them synching database content. You would still need to identify each record uniquely across all the databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There would be some examples on how to synchronize Sql Server Express/CE with Sql Server, so yes. Then again, these samples are built specifically to show some specific functionality.
Means it is no silver bullet; you may want to print the documentation and set up a test-environment.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm dealing with a strange (for me) situation in SQL Server.
I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal?
I wrote a script and run it twice from different connections.
Here is the script:
begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end
print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
drop table anyTable
commit tran myTran
|
|
|
|
|
Are you executing this exact code twice?
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Not this exact code twice. But similar yes. The only difference is the three lines on top which are commented for the process I would like to be the deadlock victim.
These lines:
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
update anyTable set anyColumn = anyColumn --remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
The only alternative that I can think of is to check if the table is locked by my SPID before try to lock it for the second time.
Thanks for your posts. If can anyone come with any idea I would appreciate it.
|
|
|
|
|
It was much better two post the code you have problem with - including the way you are running it...
The reason I say this, is that I took your code and immediately saw that it can not be run twice as every instance will try to create a table, and that can be a serious problem...
Other thing is the transaction - do not create a transaction if you need not (and from the sample you had here you need not, as you are updating the very same table twice)...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
You are right. The second process should run something like this:
begin tran myTran
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end
print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
commit tran myTran
But the reason that I lock the same table twice is much more complicated that you think. This is not a simple process. The two locks are coming from different updates. I have just tried to make it simple.
Thanks any way.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
