What is the difference between Heaps and Balanced Trees? Is it true that if a table has no clustered index it is a heap?
If so , if the table has only Non clustered index, is it still a Heap?
Hi all,
a question that has been in my mind for a while:
A HEAP is a table that has no clustered index.
Now consider a table that has a unique clustered index (primary key) on an identity column ?
For example:
create table Test
(
TestId int identity(1,1) not null,
...,
constraint PK_Test primary key clustered (TestId)
)
Is there a recognised term for such a table ?
Thanks
Graham
Have a 1TB of heaped tables being used 24/7 and performance is degrading over time, and the vendors dynamic SQL won't include clustered indexes! (and they won't let me add them)
I can reorganise the heap with an ALTER TABLE statement, add a column,
clustered index, drop column etc. However this is intrusive and I would require an entire day to perform this piece-meal, and a blanket script for 2000+ tables would kill performance all together.
Would a back-up, then remove the 1TB DB followed by a restore placing the data back onto disk unfragmented. (with LS this would only require a 3 hour down-time window) In theory it should work?
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where carid not
in(selectRentalOrders.CarRef
from
RentalOrders)
when I wrote this above-query for sofiacarrental_v2.2 it shows 30 in the result but when I changed it this query to that:
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where not exists
(select
RentalOrders.CarRef
from
RentalOrders)
I replaced not in with not exists it showed 0 in the result.there is any point in term of using them or I made a mistake in the second query?
I have a SQL table like this
Events time endTime
Tram 2014-11-28 12:35:50.390 2014-11-28 12:43:19.120
Re-Entry 2014-11-28 12:43:19.120 2014-11-28 12:56:07.040
Tram 2014-11-28 12:56:07.040 2014-11-28 13:15:25.060 // EndDate Before dump
Dump 2014-11-28 13:15:25.060 2014-11-28 13:50:07.233
Tram 2014-11-28 13:50:07.233 2014-11-28 13:55:17.473
Load 2014-11-28 13:55:17.473 2014-11-28 14:06:55.063
Tram 2014-11-28 14:06:55.063 2014-11-28 14:37:12.100
Dump 2014-11-28 14:37:12.100 2014-11-28 14:37:12.100
I want to calculate the Difference between 2 dates like endtime before Dump -time..I am expecting output like this
ROW1 ROW2
1 00:39:34
2 23:12:55
You can find the details in SQL Fiddle here.
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID Branch Group Date Value
1 A C 2015-06-01 10
2 A C 2015-06-02 15
3 A C 2015-06-03 25
4 A C 2015-06-04 20
5 B D 2015-06-01 20
6 B D 2015-06-02 25
7 B D 2015-06-03 10
8 B D 2015-06-04 20
I want the Output like below with a Running value difference in comparison to previous day.
ID Branch Group Date Value Running Value
1 A C 2015-06-01 10 10
2 A C 2015-06-02 15 05
3 A C 2015-06-03 25 10
4 A C 2015-06-04 20 -5
5 B D 2015-06-01 20 20
6 B D 2015-06-02 25 05
7 B D 2015-06-03 10 -15
8 B D 2015-06-04 20 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
What is the difference between the Index and the Primary Key?
View 14 Replies View RelatedI want a difference in days
Select datediff(dd,Target_Date,Achv_Date)
Now , checks are
1] when target date greater than achv_Date the difference should be greater than 0
means for FileID 77608
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%
What is the difference between Batch and Stored Procedure?
View 5 Replies View RelatedCurrently my script is using the below mentioned query to find the time difference.
DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))
If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?
how to measure a change in inventory over various stores. My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. When the new stock arrives it is added to the "OnHand" count. I want to measure the delta change per day,per store. I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
TimeStamp Store
OnHand OnOrder
2015/04/22 18 1 - Concord
12
0
2015/04/23 11 1 - Concord
11
[code]....
I have the below query
DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')
[code]....
the result is like below
i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).
i am using SQLServer 2008 R2
I have a table data like below
id type timestamp
1001 start1 10:34:23:545
1001 start2 10:34:24:545
1001 end2 10:34:24:845
1001 end1 10:34:25:545
1002 start1 10:34:25:645
1002 start2 10:34:25:745
1002 end2 10:34:25:945
1002 end1 10:34:25:965
I need the result as follows
id millisecond diff start1end1 millisecond diff start2end2
1001 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
1002 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
SQL Server 2008 R2
I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
just see same kind of two sql statement
BEGIN TRAN
SELECT *
FROM authors AU
WITH (HOLDLOCK, ROWLOCK)
WHERE AU.au_id = '274-80-9391'
COMMIT TRAN
BEGIN TRANSELECT *
FROM authors AU
WITH (UPDLOCK, ROWLOCK)
WHERE AU.au_id = '274-80-9391'COMMIT TRAN
i like to know what is the difference between HOLDLOCK and UPDLOCK in sql server. explain with example to better understand.
Here is the table:
empid lastname firstname title titleofcourtesy ModifiedOROriginal
1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original
We need an output like this:
empid
1
1
lastname
Davis1
Davis
firstname
Sara
Sara
title
CEO
CEO
titleofcourtesy
Ms.
Ms.
ModifiedOROriginal
Modified
Original
Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.
Type Sn Timestamp Name Value
A 1 2015-08-15 12:34 Snick 5
A 1 2015-08-15 12:34 Mars 10
A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1
B 2 2015-08-15 15:31 Mars 9
B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11
A 1 2015-08-21 09:12 Mars 18
[code]...
So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.
declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'
i want output like this 2.18 means 2day 18hours difference how using query
In sql we have a built in function
DATEDIFF(second, startdate, enddate)
How can we achieve the same using Log parser query for date time in format (2015-09-22 10:25:30.5220)
Example:
startdate = 2015-09-22 10:25:30.5220.
Enddate = 2015-09-22 10:26:30.5220
After DateDiff I need to get 6 seconds.
I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.
This is the selcet statement:
SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')
Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...
Hi,
I have two 2003 servers which are load balanced - not for high demand but for resiliance. The application that is being run on both servers shares data on a NAS raid 5 device.
Can I install SQL Express on each of these servers and have them share the databases on the NAS device?
Help please anybody?
Paul
Do you think that data mining has a role within the balanced scorecard? If yes, what kind of models can you think of?
View 1 Replies View RelatedI am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
Is the any issues running SQL 2005 Reporting Services on Load balanced web servers?
View 1 Replies View RelatedI installed the Feature Pack Balanced Data Distributor control on my PC to use with SQL Server 2014 64 bit. I have used the control with SQL Server 2014 and SSDT so I was familiar with the process. Unfortunately, I cannot get the control to appear in the toolbox. No error messages appear, BDD just doesn't appear in the toolbox. I have tried un-installing, reinstalling, installing SQL Server 2014 SP1, installing again, rebooting a number and nothing works. The control just does not appear in the toolbox. It doesn't not appear when I go to choose items either. What does it take to get BBD to appear in the SSIS Toolbox for VS?
View 2 Replies View RelatedI've used the Balanced Data Distributor with Visual Studio 2010 and SQL Server 2012 successfully. I've now installed BIDS 2013 and I'm trying to get the Balanced Data Distributor to appear again in the SSIS Toolbox.
I've installed the x86 and x64 version and it won't appear. I've tried the new version which is supposed to have a fix for some SQL Server 2008 issues but I get the message:
The installation is not successful. Check the following prerequisites: 1. Either Integration Services or BIDS has to be installed. 2. The version of these components has to be either SQL Server 2008 SP2 (or future SPs) or SQL Server 2008 R2 (or future SPs).
That seems this version only works for SQL Server 2008.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies View RelatedQuestion is in the subject.
Thanks in advance
-Jamie
I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?
View 6 Replies View Related