T-SQL (SS2K8) :: Filtered Index With IS NULL Predicate
May 2, 2009
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
[Code] ....
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
View 9 Replies
ADVERTISEMENT
Nov 20, 2006
hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
View 1 Replies
View Related
Jan 26, 2015
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
View 6 Replies
View Related
Apr 14, 2015
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
[Code] ....
View 8 Replies
View Related
Mar 22, 2014
I would like to create an index with a few options but having issues trying to parse and run it successfully. The issue is near the end of the statement. Here's the create statement below:
-- Index PSAPSACTIVITYDEFN on table PSACTIVITYDEFN
IF EXISTS(SELECT 1
FROM sysindexes si
INNER JOIN sysobjects so
ON so.id = si.id
[code]....
View 4 Replies
View Related
Nov 30, 2014
I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.
View 7 Replies
View Related
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
[code]....
So this would be the resultset:
1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27
View 3 Replies
View Related
May 14, 2014
I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.
ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN
[code]....
View 9 Replies
View Related
Dec 1, 2014
I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
[Code] ....
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?
View 5 Replies
View Related
Jun 24, 2015
I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?
View 5 Replies
View Related
Aug 13, 2015
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
View 5 Replies
View Related
Dec 17, 2006
Hello !
for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL
CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]
i can have 100 rows with a NULL Serial
thank you
View 9 Replies
View Related
Nov 2, 2010
I am trying to create a temp table with a non-clustered index.
Originally I tried to create the index after I created the table.
This seemed to work fine, so I added my stored procedure to our Production environment.
However, when two users called the stored procedure at once I got the following error:
There is already an object named 'IX_tmpTableName' in the database. Could not create constraint. See previous errors.
I then found that SQL Server does generate unique names for the temp table but not all the objects associated with the temp table if they are explicitly named.
This is easy enough to solve for a PRIMAY KEY or UNIQUE constraint because the do not have to be named.
Is there a way to create an non-clustered index on a temp table without naming it?
View 9 Replies
View Related
Jul 28, 2014
I am trying to create an index for a specific query that is used a lot.
Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)
Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.
None of which gave me a clear answer.
How can I make an index for this query?
Here's the code setup
------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,
[code]....
View 3 Replies
View Related
Nov 12, 2014
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
View 9 Replies
View Related
Dec 28, 2014
This is my table:
use tempdb
go
if object_id('Data', 'u') is not null drop table Data
go
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2
[code]....
What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.
This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).
Can this be optimized (maybe to use Index Seek method)?
View 7 Replies
View Related
Jan 10, 2015
I'm running SQL Server 2008 R2 with latest patch. I'm performing all index maintenance online. How long the final phase of the index operation takes? Does the size of the index matter and if any blocking occurs, does the duration increase because the size of indexes is larger? I've been told by management, we can not have any downtime, its my understanding even with online index there's a chance blocking can occur in the final phase of the index operation. My database and index size is over 1.5 tb and the number of transaction per second are in the 100's.
View 2 Replies
View Related
Mar 23, 2015
I have data like below
Potter, James J
Williams, Ted R
Allen, Gary G
I want to remove Middle Name from the output
Potter, James
Williams, Ted
Allen, Gary
My Query:
SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
View 3 Replies
View Related
Aug 27, 2015
I try to create script which Rebuild one table index after transaction log backup.
In my script first i check or backup is complete in 10 minutes if i find record i do it index rebuild if i don't find i need go back and check again.
The problem is that query finish if don't find a backup record, How i can loop that script that he checking until find the record and then start rebuild index.
Use DB
GO
DECLARE @database_name NVARCHAR(50)
SELECT @database_name='DB'`
START:
WHILE EXISTS
(SELECT msdb.dbo.backupset.database_name,
[Code] .....
View 1 Replies
View Related
Apr 30, 2014
I am creating web application for state and dist wise map. I've the tables like
create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')
[Code] .....
My output is depends on the dist selection so made procedure
alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0
[Code] ....
Myself doubt is when @dist_id is null or "0" how to show default value ?
Otherwise above my code is correct?
View 4 Replies
View Related
Sep 9, 2014
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.
View 9 Replies
View Related
Feb 24, 2015
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
View 4 Replies
View Related
Dec 22, 2005
Table DDL below:The tables I have contain Timesheet information. Each row in thetblTSCollected table contains an entry for an employee into thetimesheet system, specifically by scanning the barcode on their badge.A whole bunch of business logic periodically attempts to "pair" theseinto logically matched scans. For example, some employees will scan inand out of a single place of work. For these there will be a rowwritten to the tblTSRuleApplied table which contains, inter alia andsome redundant data, the fldCollectedID for the two rows. The earlierwill be put into the fldStartTimeCollectedID, and the later into thefldEndTimeCollectedID. Some employees will clock on at their base,then perform sub-duties at different locations during the day, andclock off at their home base at the end of their shift. For these, thesystem would identify the outer records as a matching pair, and thenpair up inner records by location.However, if the employee fails to enter a valid "clocking in and out"pair (for example, if they clock in at the wrong location) the systemneeds to generate a "dummy" "clocking in and out" record for thepayroll department. Ideally, this would have NULL values in thefldStartTimeCollectedID and fldEndTimeCollectedID columns. This wouldalert a user in a different part of the system, where missingtimesheets were being arbitrated, that an employee appeared to havefailed to clock in for that day. Of course, the user could seeon-screen that they had clocked in, but at an incorrect location.Unfortunately, the database designer is not here for the moment (he wasknocked off his bicycle recently), but he put a unique index on thetblTSRuleApplied table that prevents the same value being entered intothe fldStartTimeCollectedID and fldEndTimeCollectedID columns. This isgenerally A Good Thing, since we don't want the same timesheet scan toform both a "clocking on" event and a "clocking off" event.So, is there any way of retaining the requirement that thefldStartTimeCollectedID and the fldEndTimeCollectedID columns may notcontain the same value in a single row, UNLESS that value is NULL inwhich case all is hunky dory. I should add that the clients don't muchcare for Triggers (and neither do I for that matter).Many thanks if you are able to help.Edwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollectedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollected1GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINTFK_tblTSArbAccept_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINTFK_tblTSCollected_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSRuleApplied]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldProcessed] [smallint] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblTSRuleApplied] ([fldEmpRuleID] [int] NOT NULL ,[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,[fldStartTime] [datetime] NULL ,[fldEndTime] [datetime] NULL ,[fldStartTimeCollectedID] [int] NULL ,[fldEndTimeCollectedID] [int] NULL ,[fldStartArbStatus] [smallint] NULL ,[fldEndArbStatus] [smallint] NULL ,[fldDurationArbStatus] [smallint] NULL ,[fldPrimary] [smallint] NOT NULL ,[fldDateEntered] [datetime] NULL ,[fldEnteredBy] [int] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADDCONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR[fldProcessed],CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED([fldCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADDCONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR[fldPrimary],CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED([fldRuleAppliedID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED([fldStartTimeCollectedID],[fldEndTimeCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] ADDCONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY([fldEmployeeID]) REFERENCES [dbo].[tblEmployee] ([fldEmployeeID]),CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY([fldLocationCode]) REFERENCES [dbo].[tblLocation] ([fldLocationCode]),CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY([fldRuleAppliedID]) REFERENCES [dbo].[tblTSRuleApplied] ([fldRuleAppliedID])GOALTER TABLE [dbo].[tblTSRuleApplied] ADDCONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY([fldStartTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY([fldEndTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY([fldDurationArbStatus]) REFERENCES [dbo].[tblTSDurationStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY([fldEmpRuleID]) REFERENCES [dbo].[tblTSEmpRules] ([fldEmpRuleID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY([fldStartArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY([fldEndArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus])GO
View 7 Replies
View Related
Dec 1, 2014
Using a full search index with the following query works with just one parameter.
declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)
How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?
declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)
If there weren't parameters, you put single quotes around the '@P0 AND @P1' to get this query to work.
In addition, while "where contains((Col1,Col2),@P0) and contains((Col1,Col2),@P1)" works, it appears to increase the execution time.
View 0 Replies
View Related
Mar 7, 2015
I have three sprocs and three tables. I was told to use a clustered index in the first table and a unique clustered index on the second table. I never asked about the third table and the person I need to ask is on vacation. Most of the contents of the first table will be joined with all of the contents of the second table into the third table. Do I need to have a unique clustered index on the third table too?
The clustered index in the first sproc is on a unique key that I had created using by concatenating several columns together.
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY);
CREATE NONCLUSTERED INDEX IX_DATE ON MRP.Margin_Optimization_Data (PERIOD);
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.Margin_Optimization_Data
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
The second sproc with the unique clustered index is on the unique key from the first table and a date attribute.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.[MGN_OPT_KPI_SOURCE] (UNIQUE_KEY, PERIOD);
In the third sproc, I'll have a nonclusted index on the ODS_ID attributes, but I'm unsure of how to go about the clustered index situation.
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
View 4 Replies
View Related
Aug 22, 2012
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
View 9 Replies
View Related
Mar 21, 2014
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
View 2 Replies
View Related
Apr 11, 2014
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
View 5 Replies
View Related
Mar 9, 2015
I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
View 1 Replies
View Related
Aug 17, 2015
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,
[Code]...
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
View 9 Replies
View Related
Oct 10, 2014
On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.
Is it ok to defrag them using the standard Alter Index command?
View 7 Replies
View Related
Jan 20, 2008
I run statement down,result is "go". why goes,gone,going is not in the result?
SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ')
information table [contain-1]
ID firstname
1 go
2 goes
3 gone
4 going
View 2 Replies
View Related
Jan 20, 2008
what result this query?
SELECT productname
FROM product
WHERE CONTAINS(productname,'spread NEAR boysenberry' )
explain word "NEAR".
Thanks,mohsen
View 1 Replies
View Related
