SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I have a dynamic SQL query that uses various indexes and views.

When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:

@date is an integer.

begindate is datetime format.

begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))

The above code slows my query performance by at least 400%!

Would it be faster to add a computed column to my table, using the suggested method in the link below?

[URL] ....

Then i could add an indexed view to improve performance, or can this be done another way?

View 9 Replies


ADVERTISEMENT

SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should

Mar 29, 2015

I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:

- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.

To explain wat I'm doing is this:

1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:

SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:

SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'

Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.

View 9 Replies View Related

SQL Server 2014 :: Query For Listing Views In A Database Involving Tables From Other Databases?

Oct 19, 2015

script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014

View 2 Replies View Related

SQL Query Filtering Date Field By Today's Date?

Nov 3, 2006

Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?

View 2 Replies View Related

SQL Server 2014 :: How To Systematically Manage Big List Of Queries And Tables

Sep 12, 2015

Suppose someone has to work on a lot of different SQL Server Databases which have got a lot of Tables and Queries / Views inside them.

After a period of time, it becomes very difficult to remember exactly what kind of columns are present within a given Table and View.

Any method by which one can keep a systematic list of all the Tables and Views that are present within a SQL Server Database, along with the columns that are present within them.

Are there any Add-on products or services etc. available in making this type of work systematic?

Currently I add comments to each queries inside SQL Server to remind me of what this query is doing, but this method is not great.

View 2 Replies View Related

Manage Indexes In Sql 2005

Jun 5, 2007

Hi,
I'm currently migrating to Sql 2005 from 2000 and have a quick question about indexes.
In 2000 i used to click on Table > All Tasks > Manage Indexes... to view and create all my table indexes. There doesn't seem to be anything similar in 2005 Management Studio. I thought it was under Table > Modify but there's nothing there for indexes. I've had to create all my indexes manually through t-sql but this doesn't help when needing to view and manage current indexes.
Can anyone help me on this?

View 2 Replies View Related

SQL Server 2012 :: Using Select Query To Get 2014-08-09 11:13:03 From Original Date Records?

Aug 18, 2014

I have date field in table and data contain 2014-08-09 11:13:03.340

when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03

Why the 03 is trimming in smalldatefield.

how do I use select query to get 2014-08-09 11:13:03 from original Date records

View 1 Replies View Related

SQL Server 2014 :: Inline Syntax For Indexes?

Aug 11, 2014

I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?

CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);

View 9 Replies View Related

SQL 2012 :: Using Partitioned Views In Order To Manage Table Sizes

Oct 13, 2015

I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.

I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:

- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT

Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

[code]....

View 3 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table?

Aug 9, 2014

how to take backup of indexes for a particular table.

View 1 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table

Aug 9, 2014

How to take backup of indexes for a particular table.

View 5 Replies View Related

SQL Server 2014 :: Memory Optimized Tables And Indexes

Feb 18, 2015

I'm just beginning to experiment with memory optimised tables.

I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.

I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.

(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)

The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?

View 1 Replies View Related

SQL Server Admin 2014 :: Indexes With More Writes Than Reads

Jul 17, 2015

I have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point.

Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes?

In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads.

View 9 Replies View Related

SQL Server Admin 2014 :: Rebuild Indexes On Listener

Jul 27, 2015

Do I need to rebuild my indexes on my High Availability listeners?

When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?

View 1 Replies View Related

Views - Filtering On Calculated Field

Sep 12, 2007



Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.

Then I stuffed the self joins into a couple views for the events I'm interested in...

The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.

So how can I speed up the filtering on that calculated column?

Or am I going to have to get really creative with unions?

View 2 Replies View Related

SQL Server Admin 2014 :: Column Store Indexes Ignored When Run Test Queries

Aug 25, 2015

I had an existing table with lots of indexes.

As a test (fro speed) - I added a non clustered column-store index.

When I run test queries it always ignores my new column-store index. Why?

Should I remove the old indexes, leaving just the column store?

View 2 Replies View Related

SQL Server Admin 2014 :: How Do Physical Data Files Grow When Rebuilding Indexes

Feb 2, 2015

I've been trying to get a definitive answer to this question but alas I have conflicting and patchy answers so far from other sources. I have an index that, lets say, requires 10GB of data space to rebuild..This index resides on a filegroup that spans 2 files on two seperate drives (i.e. a mdf and ndf)

When I rebuild this index how will each of these datafiles grow as the rebuild proceeds to completion? Lets for the time being remove the caveats of any other activity hitting the example index/database in question.My tests seem to show that only the mdf will grows (or the file with the lowest id in the that filegroup) provided there is enough space available in that particular file to complete the operation. The secondary ndf dat file doesnt grow at all if the mdf has enough space.

Is expected behavior? i.e. the index will be rebuilt in a contiguous manner relative to the files contained with the filegroup i.e. fileid 1 will grow till limit reached then next fileid grows etc?

View 0 Replies View Related

SQL Server Admin 2014 :: Script To Find Nested Views In A Database

Oct 7, 2015

Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...

View 1 Replies View Related

SQL Server 2014 :: Find Views Which Has More Than 16 Columns For Unique Index / Constraint

Oct 27, 2015

We 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 Related

SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding

Apr 1, 2015

when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way

View 1 Replies View Related

SQL Server Admin 2014 :: How To Grant User Permission To View Specific Views

Aug 5, 2015

I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....

View 3 Replies View Related

How To List Tables,indexes,views Etc...

Jun 16, 2003

Hi,

I want to list the table names in a database "mydev_db".What would be the query ?.

I want to run a similar query to find out the indexes,views,stored_procs etc.



Regards,
Copernicus

View 3 Replies View Related

EAV Performance And Clustered Indexes On Views

Jan 25, 2008

OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

Before you say "redesign", I need funding allocated for that. See my first statement.

Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?

View 10 Replies View Related

Should I Create Indexes On Views Or Tables

May 22, 2007

I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.

Thanks
Ran

View 4 Replies View Related

Filtering Rows In Server Query

Sep 10, 2015

I am new to SQL,I wrting a query which I will at the later stage create a report but my query is creating duplicate records,I have tried the group by and the sub selects but this still bring all uneccessary columns I do not need, 

I have a query that goes 

Select 
name
Date from 
date from
date to
summary 
Estimated value

I know in this database i only have 1 record containing that information but immediately  when i ass the estimated value field a lot of duplicate,I have tried using group by the same thing happens,or even when i tried having count then all the columns disappear.

View 3 Replies View Related

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

Aug 28, 2015

The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.

query to find list of indexes on the tables on which the views has been created.

View 4 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

SQL Server 2008 :: MDX Query Filtering - Return Sales For Each Customer For Past 6 Weeks

Feb 4, 2015

I'm having problems filtering in my MDX query.

My requirements are to return the sales for each customer for each store, for the past 6 weeks.

The catch is that I only want those customers which have had sales over 10,000 within the last week.

This is my query:

WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]

[Code] ....

The dates are hard-coded for testing purposes.

View 0 Replies View Related

Filtering By Date

Mar 19, 2007

Please can someone assist me which this simple query:

Im using MSSQL Server 2005:

select * from ex_messagelog where DateCreated = '2007-03-19'

The query above does not return any results, but my data looks as follow: Note, the DateCreated is a DATETIME Datatype field.

239test 2007-03-19 08:42:19.00000
240Hallo Frank2007-03-19 08:45:43.00000
241spring 123 2007-03-19 08:49:41.00000
242testing 1232007-03-19 08:51:15.00000
243testing 1232007-03-19 08:52:39.00000
244as 2007-03-19 08:55:30.00040

View 4 Replies View Related

Filtering By Date

Jun 13, 2008

Hello I am trying to gilter a table by getdate() (i also tried now()) but I cannot seem to be able to do it I place my code below if anyone can help. am grateful, my db is sql 2005.



<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = getdate()
If (Request("MM_EmptyValue") <> "") Then
Recordset1__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_connpeepeek_STRING
Recordset1_cmd.CommandText = "SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__MMColParam) ' adDBTimeStamp

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

if any one can help I would be grateful

k

View 11 Replies View Related

Filtering By Date

Dec 12, 2007

I have a View with a structure like this

VisitTimes Company
2007-07-10 14:24:38.000 Microsoft
2007-03-10 11:14:38.000 Microsoft
2007-12-01 13:04:56.000 SQLTeam
2007-12-13 12:54:52.000 GoldMan Sac
2007-08-11 02:15:38.000 Oracle
2007-02-11 12:45:04.000 SAP Ltd

I am asked to write a stored procedure that get a count of each count of visit on a START and END date
I wrote the below SP but am not getting the right result
I think the **where VisitTime >= @Start AND VisitTime <= @End)** is not being evaluated. Help pls

CREATE procedure dbo.GetVisits
@Start varchar(50),
@End varchar(50)
as
SELECT TOP 100 PERCENT COUNT(company) AS VisitCount, company
FROM visits.dbo.IViewVisits where EXISTS
(SELECT * FROM VISITS.dbo.IViewVisits where VisitTime >= @Start AND VisitTime <= @End)
GROUP BY company ORDER BY COMPANY ASC

View 6 Replies View Related

SQL Server 2014 :: Comparing String With Date

Jan 28, 2014

I have to write a where condition where i need to compare string with date

I have 2 columns FROMDATE and TODATE with datatype varchar(9)

The strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM

i.e., 201401011 or 201401012

So I need to chop off last character before using them in WHERE condition.

Now I need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.

if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01

The query i wrote is

select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()

AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)

but it shows no data...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved