SQL Server 2008 :: Usage Of Built-in Functions On Columns Ignores / Excludes Indexes On That Column?
May 25, 2015
Somewhere i read..that in SQL Server...usage of Built-in Functions on Columns, makes query optimizer to ignore indexes on that column...!
So lets say we have table EMP with Emp_id and Emp_Name....
Also we have Non-Clustered index on Emp_Name.
So following query would NOT use Non-Clustered index on Emp_Name column.
SELECTLEFT(emp_name, 3) as emp3
FROMdbo.EMP
Is this true? i am using SQL Server 2008.
View 4 Replies
ADVERTISEMENT
Oct 4, 2006
Hi
I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.
I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.
Is there a way to get these programmatically? I would like to avoid typing them out by hand.
Thanks
Chris
View 6 Replies
View Related
May 17, 2007
Hi everybody,
I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)
Thanks in advance!
JorgeHG.
View 5 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of
values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throw some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 1 Replies
View Related
Apr 17, 2015
I am using MS SQL Server Management Studio version 10.50. Some built-in functions are missing, such as LAST_VALUE and IIF. Where can I get those?
View 4 Replies
View Related
Sep 10, 2015
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,
[Code]....
View 4 Replies
View Related
Oct 12, 2015
SSIS 2008 when I develop and debug in BIDS sometimes ignores debug break point.
The script component is in the main control flow and at some point the breakpoint did work.
If, for example, I create a new project and copy my script component there the debug breakpoint will work.So it's absolutely *random* when it works and when it does not.
Below is my BIDS detail:
Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: IDE Standard
Enterprise Library v5 Configuration Editor 4.0
[code]....
View 2 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
Apr 12, 2007
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
View 1 Replies
View Related
Apr 16, 2007
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
thanks,
Ran Kizi
View 3 Replies
View Related
Oct 31, 2007
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the
ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
and
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
View 1 Replies
View Related
Feb 26, 2015
I am looking for the script to find the CPU usage for each database like this
Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
May 26, 2015
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
View 5 Replies
View Related
Sep 5, 2001
For a given table:
Data 11.91 MB (37.76%)
Indexes 19.41 MB (61.57%)
Unused 0.21 MB (0.67%)
Total 31.53 MB
# Rows 299474
You can see that the indexes are taking up
more space than data there are a total of four
indexes on the table is shouldn't the data(MB) be
higher than the indexes(MB) ?
View 1 Replies
View Related
Jul 14, 2015
here's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
View 7 Replies
View Related
May 1, 2015
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
View 9 Replies
View Related
May 21, 2015
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
View 2 Replies
View Related
Aug 24, 2015
I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.
View 8 Replies
View Related
Jun 16, 2015
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
View 1 Replies
View Related
Jul 15, 2015
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
View 5 Replies
View Related
Mar 17, 2015
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies
View Related
Jul 14, 2015
I am creating mateialized view but it is failing with error that it can't be schema bound.
The query I am working to create materialized view are having joins with different tables and function.
Is it possible to create Indexed views on user defined functions?
View 2 Replies
View Related
Sep 13, 2004
Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.
Any ideas would be appreciated.
Thanks,
Rob
View 3 Replies
View Related
Aug 12, 2009
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
View 6 Replies
View Related
Apr 6, 2015
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
View 2 Replies
View Related
Nov 10, 2006
How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot
View 3 Replies
View Related
Nov 5, 2015
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
View 3 Replies
View Related
Aug 16, 2007
We have an Access application using Jet. I added some new indexes yesterday and now they are being blamed for poor Access application performance. I then dropped the new indexes. The poor performance continued until the Access application was re-linked to the SS2000 database. Then things returned to normal.
Question, does Access/Jet persist SqlServer schema info in its MDB (or elsewhere?) I am told that the MDB is copied from a share to the local PC where it grows during its use. Some people are saying the MDB persists schema info about the SS2000 schema which influences how Jet accesses the SS2000 database. Is that true? Is there a link where I can read about this? I am a dba and am not an Access developer . . .
Thanks!
Michael
View 4 Replies
View Related
Jul 20, 2005
I am having a problem restricting write access to tables in my database.In my database I have a table called, for the sake of argument, 'TableX'.In my SQL Server Logins, I have set up a login for 'Domain Users' using NTauthentication, and a login called 'FullTableX', using SQL Serverauthentication.I have added two users to my database relating to the above logins.I have added a role to my database called 'ReadTableX' with 'Domain Users'as a member of this role. 'ReadTableX' has SELECT permission only on arestricted set of tables.The only other role that 'Domain Users' is a member of is 'public', and'public' has no permissions on any of my tables.The user 'FullTableX' is a member of 'public', 'db_datareader' and'db_datawriter'With the above settings, I would expect user 'FullTableX' to have fullaccess (Select, Insert, Update and Delete) on all my database tables (so farso good), but any user connecting to the database with NT authentication(via ODBC System DSN) should only have read access to the limited set oftables. However, what is happening is that NT authenticated users also havefull access to all of the tables.What am I missing here??
View 3 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Mar 26, 2010
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies
View Related
