I have following simple code in my stored proc. even I have hard coded OFFSET to non zero, but it always return result from starting point 0. End limit "Fetch Next" is working perfect.Only problem is with start.
SELECT
*
FROM #invoices
ORDER BY #invoices.InvoiceDateTime ASC
OFFSET @StartRow ROWS Fetch NEXT @EndRow ROWS ONLY;
Hi,
I need the equivalent for below mysql query :
select *from test limit 5, 20
I know that the below is possible in SQL Server :
select top 20 * from test where column1 not in (select top 5 column1 from test).
But my problem is I want a generic solution wherein I will not be aware of the columns available within the table.
Please advice,
- Mira
I need to select records in column that start with '% ' -- %space e.g. column A = % mytest
I tried select column name with like operator but it select records who start with % and ignore space
select columnA from table where columna like '% %';
Has experienced this error when trying to open SSMS? Was working fine earlier. I have tried uninstall/reinstall, a few reboots,repairing the install, etc.
View 5 Replies View Relatedi m not able to start the SSIS service on my laptop . IT gives error saying SQL server integretion service 11.0 service on local computer started and then stopped . some services stop automatically if they are not in use by other services or program
i am not able to start SSDT . it gives error
microsoft visual studio is unabble to load this document to desigen integration service package in ssdt , ssdt has to be installed by one of these edition of sql server ; std enterprise,dev,or evloution
i hav installed sql 2012 evolution verison on my local desktop.
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
[Code] .....
Two tables:
GeoIpLocation (Id, City, Country, Latitude, Longitude; PK: Id; 2m rows)
GeoIpBlock (IpFrom, IpTo, GeoIpLocation_Id; PK: IdFrom, IdTo; 100k rows)
Simple concept: get location for given IP. So:
SELECTTOP 1
GeoIpLocation.Country,
GeoIpLocation.City
FROMGeoIPLocation
INNER JOIN GeoIPBlock
ON GeoIPBlock.GeoIpLocation_Id = GeoIPLocation.Id
WHERE@nIpNumber BETWEEN GeoIPBlock.IpFrom AND GeoIPBlock.IpTo
Result: disaster.
The between operator uses the index on PK on GeoIpBlock, which results on half the table for first part of between and half the table for the second part of between. A bit better query is with FORCESCAN on GeoIPBlock, but it still runs slow, as it scans a lot of records.
Question: Is there a better way to index this kind of data?
Dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Is it correct way to remove index if dead lock is coming because of index?
View 3 Replies View Relatedi have add maintenance plan rebuild index in sql server 2014 log file increased very tremendous space.
View 6 Replies View RelatedWe have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
View 6 Replies View RelatedI am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4
I created columnstore index on the table with 20 columns and about 1000 000 000 rows
every day added about 5M rows
"select" queries became faster because of batch mode and table demand less disk space then before
I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index
server has 128 G RAM
What pitfalls I could face if I will have so many columnstore indexes on one server?
How a could see problems in DMV?
I have a situation where I need to rebuild indexes on a large DB (500G).
When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.
When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.
What can I do without removing my DB from H/A to rebuild the indexes?
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC)
WITH (DATA_COMPRESSION = PAGE)
GO
however later when i add CLUSTERED COLUMNSTORE INDEXES :
CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales
ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167 You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
I want to create a lot of index for my database for performance.
But I need find memory usage by indexes.
How to find memory usage by index in sql server?
We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.
My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.
I want know can I use different index on main DB and mirror Database?
Because some index are used in mirror DB not used in main database.
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
I need to restore a SQL 2008 db on SQL 2014 instance with out upgrading the database(changing compatibility level).
This database has full text enabled. I see one full text catalog an 2 full text indexes.
Do I need to worry about anything or can I perform a clean restore from the backup with full text import?
Do I need to rebuild the catalog in this case?
I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.
View 7 Replies View RelatedWe are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
View 1 Replies View RelatedI've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
I have 10 Gb index and disk space only left 5gb .
How can i rebuild index ?
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
What does this mean and how can I fix this?
View 1 Replies View RelatedHi there,
This is probably a really obvious question but I have been struggling with answering it for about two days now. (I may even be in the wrong forum) I work in a company with an internal network set up. I have been trying to connect to our website via FTP and it has worked ... well it did in the beginning. I think the server that our website is hosted on is a Unix server - this is a guess from reading the FTP transcripts of the various programs I've downloaded and tried out in an attempt to connect to it.
What happened:
I was working on an .ASP page in Dreamweaver, hit save without thinking, realised I wanted to make a few additional changes before uploading it onto the server, cancelled the transaction and voila. I haven't been able to connect to the remote server since.
Depending on the FTP program that I am using, I get a connection error along these lines:
- "Connection to the server was reset" "The server may temporarily be down or unavailable or not accepting connections"
I am a big newbie at this. I have turned off the windows firewall and tried turning on and off passive IP. I can connect from home but not from inside the building which has me think this has to do with a stray setting somewhere. My housemate suggested I try using the "telnet" command from DOS and see can I get any sort of connection from work. If I can't, he said, it's probably a firewall on the internal network itself.
Any other suggestions?
Help would be welcome!
Thanks.
Quizzy
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
View 3 Replies View RelatedI'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
The question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
So I can easily use DelimitedSplit8K to definitively find the first thee values, but how do I dynamically get X number of groups of four; eventually i need the first three fields combined with each quadgroup.
So a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
Here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[Code] ....
I am getting an intermittent problem with executing queries on my workstation. Whenever I execute a couple of store procedures that return a large numbers of rows (200,000 to 500,000 rows) I receive an intermittent error message when running the same sp with the same parameters sometimes the procedure runs fine and returns the result, sometimes I get an error message
Error message is: Index was outside the bounds of the array.
Sometimes
Error message is: Internal connection fatal error.
Sometimes I get a datetime overflow
I also occasionally get these intermittent messages when I execute the native query that the SP wraps.
I have SSMS 2015 SP1 installed on my workstation, the servers are a range of server from 2008 R2 SP2 to 2014 SP1. It feels like a client problem as there are applications connecting to these servers and running the stored procedures without issue...
How can we get the list of clustered columnstore index in a database in sql server 2014
View 3 Replies View RelatedI have a unbound text box that gets its date from the system (date.now).
The problem is that when I write it to SQL (via a SQL Insert Command) It throws an error.
It transpires that the format is wrong. It accepts the date such as 07/17/2007 just fine but not 17/07/2007 which is automatically generated.
My IIS has a locale setting with is correct for the UK.
How can I change SQL 2005 so that it accepts DMY for a date/time field
Thanks.
Hi,
I am working on a DTS package in which one part is executing a SQL Task that's output is being stored in a global variable..or better yet, it WAS storing it. In the beginning it was working and I had an Active X task that would loop through the record set and write the contents in HTML to a flat file.
NOW, out of nowhere, when I press on the parameters button, I recieve the following error:
"The SQL Statement does not contain any parameters"
I made no changes and the funny thing about it is that I can still run the package and it still writes the output to the file, with different data if it applies ( it is calling a stored procedure ), I just cannot change it!
Has anybody ran into this? I have searched other discussion boards and there have been people with the same issue but I have not gotten a clear answer.
Any help would be very appreciative.
Jason...