Full name as a Primary Key (PK) is my preferred choice; is there another way? I'm new to DB management and I'm trying to create an ER Schema Diagram to best understand how to build my database for a project.

My project is to list every speaker (i.e. an individual person) that has spoken at specific events.

However, a name is not unique, so how can I make it unique?

Of course, I could just make an AUTO-INCREMENT ID as the PK but that would almost certainly create duplicates...

Any ideas on how I should approach this?

Ideally, I want the speaker's name to be 100% unique because often these speakers speak at multiple events....

All help very much appreciated!

You are absolutely right that a surrogate key (ID or otherwise) won't solve your root problem which is more fundamental than the database schema - "How do you tell speakers apart in the real world?"
That's a business question, and full name may or may not be good enough.
You should ask yourself - If I have 2 speakers named John Doe, how do I tell them apart in the real world, and that will be your answer to the key.

Common attributes used to identify people in these kind of data universes are Email address, User name / Alias (login to your system), SSN, perhaps even name + address may be unique enough for your business needs - what are the odds of 2 people with the exact same name residing at the same address?
Theoretically, you should accommodate every possible case, but in practice this would be much better than having a surrogate key, which will guarantee duplicates as you correctly observed, and not having any way to tell the entities in the database apart.
Using an ID will lead to much worse data consistency problems later on when the 2 John Doe start showing up in the wrong conferences :-)

HTH


SQL - Trigger to update a column as NULL when another column has a fixed value I have a table for Seller. If seller type is 'organization', I want the MidName of Seller to be blank (NULL).
Is it possible to create a trigger that does so after insert?

Column names in table:

SellerID
Type(fixed value - either Org or Ind)
FirstName
MidName
LastName
Address
PhoneNo

If Type is "Org" then MidName must be updated to blank (even if some value is entered during Insert)

I agree with Andriy that a constraint should be used, but to answer your question if this is possible with a trigger, the answer is yes.

Here is the trigger:

CREATE TRIGGER i_Seller
ON Seller
AFTER INSERT
AS
IF EXISTS (SELECT NULL FROM inserted i WHERE Type = 'Org')
BEGIN;
    UPDATE s
    SET MidName = NULL
    FROM Seller s
    JOIN inserted i ON s.SellerID = i.SellerID
    WHERE s.Type = 'Org';
END;


Archiving data from logging db (high writes, no reads) I have a logging database with a few lookup tables and one massive event table that has 79 million rows. On busy days my code inserts over 3 million rows, so the event table would be enormous in production if not for the fact that every half hour I copy the latest data to an archive database on a separate server and then every night delete all rows more than one month old from production.

The problem is that these deletes are now taking over 10 hours to run. I don't think they cause any blocking as long as I put 100 ms delay between each delete statement (script below), but I feel like things could be improved. Should the logging db be in snapshot isolation mode? We typically write the row and then update the EndTime column after the function has completed.  Any suggestions on how to improve the process and/or speed up production would be appreciated.

    DECLARE @CutoffDate datetime = DATEADD(DAY, -30, GETDATE()), @Time datetime, @Stop bit = 0, @RowCount int

WHILE (@Stop = 0)
BEGIN
    SET @Time = GETDATE()

    DELETE TOP (100) af
    FROM ApplicationEvent ae
    JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate

    DELETE TOP (100) ae
    FROM ApplicationEvent ae
    LEFT JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate AND af.ApplicationFaultID IS NULL

    SELECT @RowCount = @@ROWCOUNT
    SELECT @Stop = CASE @RowCount WHEN 0 THEN 1 ELSE 0 END

    --PRINT DATEDIFF(MS, GETDATE(), @Time)

    WAITFOR DELAY '00:00:00:100';
END

Here's the schema.

CREATE TABLE [dbo].[ApplicationEvent](
    [ApplicationEventID] [int] IDENTITY(1,1) NOT NULL,
    [LogTime] [datetime] NOT NULL,
    [TransactionID] [int] NULL,
    [TransactionType] [int] NULL,
    [EventID] [int] NOT NULL,
    [EventLevelID] [smallint] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NULL,
    [IPAddress] nvarchar NULL,
    [UserName] nvarchar NULL,
    [ApplicationID] [smallint] NOT NULL,
    [EventInformation] nvarchar NULL,
    [HostName] nvarchar NULL,
    [ApplicationSourceId] [smallint] NULL,
 CONSTRAINT [PK_ApplicationEvent_1] PRIMARY KEY CLUSTERED
(
    [ApplicationEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The problem is that these deletes are now taking over 10 hours to run.

if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log.

Founded the link written by SQL CAT team about  Fast ordered delete technique of using a view or a CTE.

e.g.

-- tested to delete 27 million and it took 24 mins to delete

CREATE VIEW vw_TABLE_TO_DELETE
as
select top (100000) * from TABLE_TO_DELETE
where CONDITION
ORDER BY LogTime

----  Caution : perform actual deletes now !!
--- start an Explicit transaction to avoid Log flushes.

BEGIN TRANSACTION

DELETE
FROM vw_TABLE_TO_DELETE

---- if FULL Recovery then do T-log backup
---- if SIMPLE Recovery then perform a CHECKPOINT

COMMIT TRANSACTION  --- in case if we want to cancel in between, the deletes are committed in the log already

  Should the logging db be in snapshot isolation mode?

You have to test it out depending on your workload. Read committed Snapshot isolation (RCSI) has a tempdb overhead as it has to do a lot of row-versoning. The Database engine will store a version of the previously committed image of the row in tempdb.

ON a highlevel - RCSI - selects do not lock data during a read operation and Read transactions do not block write transactions and vice versa.


Why does the CBO eliminate `distinct` and not `group by` in this query? The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by.

My questions are:

1) Why does it choose to do so in this case - I can't see a reason from the costings and predicted cardinalities in the plans

2) If it chooses to eliminate the distinct, why not apply the same logic and eliminate the group by?

testbed:

create table t1 as
select rownum product_id, mod(rownum,3)+1 company_id
from dual
connect by rownum<=500;

create table t2 as
select t1.product_id from t1 t1 cross join t1 t12;

create table t3 as
select distinct company_id from t1;

analyze table t1 compute statistics;
analyze table t2 compute statistics;
analyze table t3 compute statistics;

fast (55ms):

select company_id
from t1
     join t2 using(product_id)
     join ( select company_id
           from (select company_id from t1 group by company_id)
                join t3 using(company_id) ) using(company_id)
group by company_id;

slow (5240ms):

select company_id
from t1
     join t2 using(product_id)
     join ( select company_id
            from (select distinct company_id from t1)
                 join t3 using(company_id) ) using(company_id)
group by company_id;

SQLFiddle here.


XML Parsing Illegal XML character error in SQL 2008 I was running this query in SQL Server 2008 (10.0.5890),
Collation: SQL_Latin1_General_CP1_CI_AS

SELECT
   CAST(event_data.value('(event/data/value)[1]',
   'NVARCHAR(max)') AS XML) AS DeadlockGraph
FROM
   ( SELECT
      XEvent.query('.') AS event_data
   FROM
      ( -- Cast the target_data to XML SELECT
         CAST(target_data AS XML) AS TargetData
      FROM
         sys.dm_xe_session_targets st
      JOIN
         sys.dm_xe_sessions s
            ON s.address = st.event_session_address
      WHERE
         name = 'system_health'
         AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data )

But it was always ended with the following error:

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 22203, character 102, illegal xml character

Even if I changed it to VARCHAR(max) it remains the same.
Though this same script was running fine and without any issue in another server with the same version & collation.

Sample XML result from the working database:

Proc [Database Id = 5 Object Id = 2033194789]

    </process>
    <process id="process55d288" taskpriority="0" logused="584" waitresource="KEY: 5:72057594332250112 (6b0281c999d7)" waittime="1281" ownerId="1679" transactionname="EventGetQueueTopSp" lasttranstarted="2016-07-02T20:08:56.350" XDES="0x8209f990" lockMode="U" schedulerid="4" kpid="2880" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-02T20:09:12.370" lastbatchcompleted="2016-07-02T20:08:59.090" lastattention="1900-01-01T00:00:00.090" clientapp="EventService Queue Check" hostname="WVERPDB" hostpid="3148" loginname="sa" isolationlevel="read uncommitted (1)" xactid="1679" currentdb="5" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
      <executionStack>
        <frame procname="" line="96" stmtstart="8066" stmtend="8714" sqlhandle="0x0300050009a8007d9eadc300449e00000100000000000000" />
      </executionStack>
      <inputbuf>
</deadlock>

Here is the SQL version for both DB

Microsoft SQL Server 2008 (SP3) - 10.0.5890.0 (X64)   Apr  2 2015 16:07:19   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

Here is how to find the "illegal" character:

There are two places in your query that cast to XML, so start with confirming which one is throwing the error. I doubt it is the outer SELECT since that is just taking a subset of the data that is already valid XML. And, if it was the outer CAST, that would mean that an individual deadlock graph would have at least 22,203 lines it. Most likely the error line number that is leading you to the outer select is simply referencing the query, not a particular part of the query.

Remove the CAST( and AS XML) parts of the outer SELECT. If the error still happens, it is coming from the CAST(target_data part, else it really is the outer part. OR, you can just highlight and run the inner-most SELECT (i.e. the one that is the derived table, Data).
Next, we need to see the string that you are trying to CAST into XML. This is tricky as you can't display more than 64k characters in the Grid in SSMS, and using SQLCMD will get 1 million characters, but this data is likely well over even that limit.

However, we can break it up into smaller pieces. As far as my testing shows, the fact that you have a line # in the XML that is above 1 indicates that there are newlines in the source value. So, we can capture the incoming target_data value from sys.dm_xe_session_targets and print it to the Messages tab of SSMS. Yes, the PRINT statement is limited to 8000 VARCHAR characters or 4000 NVARCHAR characters, but we can split up the original value on the newline characters and call PRINT on each chunk. I have the code for a Stored Procedure that does just this on PasteBin at T-SQL Stored Proc to PRINT NVARCHAR(MAX) values, which is based on my DBA.StackExchange answer How to output more than 4000 characters in sqlcmd.

Create the DisplayN Stored Procedure and then run the following query:

DECLARE @ShouldBeXml NVARCHAR(MAX);

SELECT @ShouldBeXml = st.[target_data]
FROM   sys.dm_xe_session_targets st
INNER JOIN   sys.dm_xe_sessions s
        ON   s.[address] = st.[event_session_address]
WHERE  s.[name] = 'system_health'
AND    st.[target_name] = 'ring_buffer';

EXEC dbo.DisplayN @ShouldBeXml;
PRINT N'-------------------------------------';

SELECT CONVERT(XML, @ShouldBeXml);

Scroll on down to whatever line # is noted in the "XML parsing: line XXXXX" error message that is below the ------------ line in the "Messages" tab.

If there is no obviously invalid character on that line (and you can try copying and pasting that line into a SELECT CONVERT(XML, N'{that_bad_line}'); statement to test out to confirm), then we can modify the DisplayN Stored Procedure to also output the VARBINARY hex values of each line so we can find hidden / control characters.


SQL Server Transaction Timeout Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to perform a rollback or commit. This then causes other sessions to hang waiting for the transaction to complete.

Extending Mark's answer...

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.

Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so

This detritus from the abort will block other processes.

The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON.

You can verify this be opening 2 query windows in SSMS:

Window 1:

In menu Query..Query Options set a timeout of 5 seconds then run this

BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout

Window 2, this will wait forever (or hit your timeout)

SELECT * FROM sometable

SET XACT_ABORT ON has interesting side effects too:

@@TRANCOUNT is set to zero on the implicit rollback but error 266 is suppressed (this happens if @@TRANCOUNT is different on entry and exit from a stored proc)
XACT_STATE will be -1 (it's "doomed")

The combination of this means that you can't use SAVEPOINTS (although, I can't recall exact behaviour) for partial commits/rollbacks. Which suits me

SO links on SET XACT_ABORT:

https://stackoverflow.com/a/1539686/27535 (me)
https://stackoverflow.com/a/919279/27535 (me)
https://stackoverflow.com/a/1150350/27535 (Remus Rusanu)

On nested stored procs:

https://stackoverflow.com/a/2074139/27535 (me)

On sp_reset_connection:

https://stackoverflow.com/a/2924456/27535


How can I increase the length of the string returned by object_definition in sqlcmd? I have an sql script that uses select object_defintion(object_id(...)) to extract the definition from a view.

This script is run with sqlcmd: sqlcmd -i extract.sql -o %oudir%\extracted.dat.

Unfortunately, the length of the returned string seems to be truncated so that not the entire view definition is extracted.

Is there a possibility to extend the length of the returned string?

Specify the -y SQLCMD option to avoid truncating the value to the 256 character default. -y0 will not truncate the value but should be used with caution as mentioned in the doc.

Example:

sqlcmd -i extract.sql -y0 -o %oudir%\extracted.dat


Autogrowth in SQL Server I ran into some trouble with my disk space on server because my DB size increase. Auto Growth is enable on my database, But file growth is in percentage and not in MB. I want to change in to MB.

But before changing that I want to know can we do this change in working hours or do we require down time for it.

I don't want to take any risk in working hours so I want to be sure before doing this.

But before changing that I want to know can we do this change in working hours or do we require down time for it

Yes you can change the autogrowth setting when database is being accessed, for next autogrowth instead of percentage the value in MB would be taken into account. Its almost always a good idea to keep autogrowth in MB. You can get tentative value for autogrowth from This Article use the query mentioned to set the tentative value. Keep monitoring using the query in future this value may change then accordingly you have to change yours

I suggest you to read Considerations for the "autogrow" and "autoshrink" settings in SQL Server


Memory allocation in PostgreSQL My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.

@Arun Raut, As you said that you want to know that memory (RAM) allocation in PostgreSQL.

What processes use much RAM and why?

Shared memory is accessible by all postgres server processes.

Normally the most part of it is shared_buffers. As per PostgreSQL BOL Here  PostgreSQL suggest to use 25% of RAM, though often less values are used.

The wal_buffer are normally much smaller, 1/32 of shared_buffer is default. Anyway, you are allowed to set it to arbitrarily large value.

The amount of memory used for table and advisory locks is

About 270 * max_locks_per_transaction
* (max_connections + max_prepared_transactions) bytes

you are probably safe, unless you are doing something tricky using lots advisory locks and increase
max_locks_per_transaction to really large values

Same for max_pred_locks_per_transaction - predicate locks are used only for non-default transaction isolation levels, make sure not to increase this setting too much.

No more than autovaccum_max_workers workers, each uses
maintenance_work_mem or autovaccum_work_mem of RAM.

Ideally, your tables are not too large and your RAM is not too small , so you can afford setting autovaccum_work_mem to reflect your smallest table size.

Practically, you will autovaccum_work_mem to cover all the small tables in your DB, whatever that means.

Backends and their bgworkers are the most important , as there might be quite a few of them, namely max_connections and max_workers.

The work_mem parameter limits the amount of RAM used per operation, i.e. per execution plan node, not per statement.

For your further ref Here, Here and https://stackoverflow.com/questions/30328861/most-impactful-postgres-settings-to-tweak-when-host-has-lots-of-free-ram


SQL Server Max memory limit I have my SQL Server 2008 R2 installed running on 32GB ram. I need to decrease the limit SQL Server uses to 28 GB and I have set the same to 28GB but it is still taking more. I have seen posts saying max memory is for buffer pool only. Ultimately what I require is the OS utilization should not go above 90% which is why I kept the max memory to 28GB hoping the OS utilization would come down.. but its not happening. What are the solutions?

I have run this query:

SELECT
    type, virtual_memory_committed_kb, multi_pages_kb
FROM
    sys.dm_os_memory_clerks
WHERE
    virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

Output:

type                        virtual_memory_committed_kb  multi_pages_kb
MEMORYCLERK_SQLBUFFERPOOL   29169592                     416
OBJECTSTORE_LOCK_MANAGER    131072                       0
MEMORYCLERK_SQLSTORENG      53312                        17744
MEMORYCLERK_SQLCLR          10612                        14792
MEMORYCLERK_XE_BUFFER       4224                         0
MEMORYCLERK_SQLUTILITIES    120                          0

Query:

select * from sys.dm_os_process_memory;

Output:

physical_memory_in_use_kb   30599208 ~ 29G

large_page_allocations_kb 0

locked_page_allocations_kb 0

total_virtual_address_space_kb 8589934464

virtual_address_space_reserved_kb 39439648

virtual_address_space_committed_kb 31066216 ~ 30G

virtual_address_space_available_kb 8550494816

page_fault_count 16996946

memory_utilization_percentage 99

available_commit_limit_kb 32705384

process_physical_memory_low 0

process_virtual_memory_low 0

SQL Server's memory manager is designed to keep as much data cached in memory as it can in order to speed up access for incoming queries.

In order to work successfully with other processes on the machine,
SQL Server will release memory when a low- memory notification is
sent by Windows.
The memory may be committed by the SQL Server process, but it will be
released if another application requests enough to trip the
low-memory notification.

Before you proceed with setting up the MAX and MIN server memory config setting would recommend you to go through this great write up by Jonathan How much memory does my SQL Server actually need?

  Ultimately what I require is the OS utilization should not go above
  90% which is why I kept the max memory to 28GB

If you are changing above just to settle down the threshold, sounds may not be the perfect reason you should be cutting down the memory what SQL needs.

However, the first thing you will need to analyse what process that are running at OS level and for how long that needs the amount of memory you are looking for, because SQL will use what has been assigned.

Baseline the server accordingly and see if you really face OS memory pressure:

Also see here  and here.

In addition there is this excellent article from Shanky : SQL Server Memory and Troubleshooting which should get you going.


Cannot Start SQL Server 2017? I have been trying to start SQL Server 2017 and been getting
 'Not enough storage is available to complete this operation. '

Full Error:

  TDSSNIClient initialization failed with error 0xe, status code 0x1.
  Reason: Error: 19101, Severity: -1, State: 0. (Params:). The error is
  printed in terse mode because there was error during formatting.
  Tracing, ETW, notifications etc are skipped.  Not enough storage is
  available to complete this operation.

on the System Event:

  The SQL Server (MSSQLSERVER) service terminated with the following
  service-specific error:  Not enough storage is available to complete
  this operation.

I do have enough space on all the drives, any help is appreciated.

Normally when I see: The error is printed in terse mode because there was error during formatting it means there isn't enough memory.

This is further correlated by:

Error 0xE which is...
14 in decimal which is...
net helpmsg 14 which is...
ERROR_OUTOFMEMORY = Not enough memory resources are available to complete this operation. which is...

  Not enough storage is available to complete this operation.

Which is what is stated in the error:

  The SQL Server (MSSQLSERVER) service terminated with the following service-specific error: Not enough storage is available to complete this operation.

Make sure enough memory exists, the page file has space, and/or the max memory setting is set high enough.

To change the memory setting, start SQL Server from the command line or edit the startup flags using SQL Server Configuration Manager. Add -f for minimal configuration startup.


Page Life Expectancy Wildly Fluctuating For months now, the PLE on one of our servers has hovered around the 2 million seconds. If varied slightly from day to day but was pretty stable there.
This last weekend, we added 12 GB of virtual RAM and 1 virtual CPU core to the server.  We did not change the maximum used RAM in SQL Server to match the new RAM nor did we allocate the new CPU core to SQL Server.
Since this was done, our PLE has fluctuated wildly, going between 50 and 4 million seconds every 10-30 minutes. The changes are not a slow rise or fall. The metrics go straight from very low to very high and vice versa in less than a minute.
Our overall wait times for the server are fine. Latches are normal. Buffer and plan cache sizes haven't changed. There doesn't seem to be any consistent pattern of a specific query or type of query draining the resources.
I've never seen PLE do this before. Can someone point me to what I may be missing or need to look deeper into?

Additional information from comments:

We are at 5 CPUs total but only using 3 (we were at 4 using 3).
Our total memory is 49GB and SQL's max is 28GB.
We are using VMWare with an x64 OS (Windows 2008).
There are 14 User databases on the server with the primary one being around 250GB.
Buffer Cache Hit Ratio has stayed around 98+% since this all began.
The server power plan is set to Balanced (not High Performance); however, that has not changed in several years. With that said, I completely agree it should be High Performance.
Neither the SQL Server Error nor Windows Event Logs are showing anything out of the ordinary.
The activity on the server has not changed in the last several weeks.
The server is NUMA aware. The MAXDOP is 4, with a cost threshold of 10.

We bumped the memory from 28GB (the original amount) to 40GB, leaving 8GB of memory for the OS and other processes. Immediately afterward everything returned to normal and has stayed stable. One of our DBAs speculated that SQL Server was confused about how much memory it really had available. I had checked the Total Server Memory both before and after and the numbers were consistent with I see in Server Properties but I find that assertion difficult to argue against


Same Enteries in mysqld.log I see my mysql log file and found approx 90% of below mention entries

2018-03-06 10:42:37 14950 [ERROR] Slave I/O: error connecting to master 'ad@16.8.2.3:3306' - retry-time: 60  retries: 399,
 Error_code: 2003

Unfortunately, i dont have access on above machine and i have dropped the user ad@16.8.2.3 but still its showing log entries. I want mysqld.log file must ignore this entry. Any idea

It seems that your MySQL server is configured as a slave server in a master - slave replication setup. If all you want is for the error messages to go away, then you can simply do:

STOP SLAVE;

And to remove the slave configuration:

RESET SLAVE; (Use RESET SLAVE ALL; on MySQL 5.5.16 and later versions.)

Also edit /etc/my.cnf (and any included .cnf files inside it) to remove any lines starting with master- and replicate-.

While the above is sufficient to prevent the server from attempting further replication, you can also restart mysqld to remove slave status information available from commands such as SHOW SLAVE STATUS\G.


Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems? I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition.

Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I am wondering what features will be lost by going from Enterprise down to Standard, if any.

I've tried searching around for this but there are only comparisons between editions on the same version (i.e. 2008 Standard vs Enterprise) not comparasions between different editions across different versions (i.e. 2005 Enterprise vs 2008 Standard).

Anyone got any advice or has any resource they can point me at?

thanks in advance.

Very few SQL language features are different between editions.

Some language constructs (CREATE PARTITION for example and related) are additional (as opposed to different) and won't work because these are Enterprise features.

However, for day to day code, nothing special, things are almost identical.

The only thing I can recall that is different is how indexed views are used


Boolean as measure in fact table or dimension/attribute value My fact table consists of complaints with typical dimensions of who, what and when. We have a target that the complaint should be responded to within a certain time period.

I'm not sure on how I best to model whether the complaint had a response within that time period.

I can store both the target value and the result in the fact table as integers with the result as a measure. Or I could use a dimension which has values of yes/no instead of representing this as a measure, I could then use a calculated measure in my cube. Or I could use a combination of both.

Are there any pros, cons and gotchas modelling the facts in the ways described above?

I anticipate that this fact table will be used to get the total number of complaints, the number of complaints responded to in time, the percentage responded in time and also identify individual complaints which were not responded to in time.

Here is a flexible way to store it that should support changes in requirements in the future.

Store the target in a dimension like dim_complaint_type. That way if in the future you have different types of complaints – e.g. Billing, Technical, ... – each of them can be categorized and have different targets. By applying slowly changing dimensions type 2 on the target attribute, you can also change your target from one year to another without changing the result for past complaints. You can also do "what-if" analysis by changing the target in one dimensions and see how much more/less are now on-time.
Store the response time in the fact table, as the elapsed_time (integer) or as the registration_date and closing_date as suggested in the comments. My preference goes for registration_date and closing_date with a calculated column elapsed_time which is registration_date - closing_date.

For the result, I wouldn't store it as a flag (yes/no) but as a number. If you call the column on_time and put a 1 for each complaint that meets the target, you can easily SUM on that column to have the total of on_time complaints over a period.

I see three ways of getting the result :

Not storing it, but compute it on the fly in your reporting by having the same formula everywhere to check if fact_complaint.elapsed_time is lower than or equal to dim_complaint_type.target or not. It might be dangerous if different persons use different formulas. If you have a tool with a metadata layer (e.g. OBIEE, SAP BO, ...), you can define that result measure once for all.
Create a view that joins fact_complaint and dim_complaint_type and add that result measure in the select list. It would be defined once for all. But you need to be careful with that approach as some reporting tool might not act in the same way with a table or a view. The database optimizer might also not be able to do all its magic the same way.
Actually store it in your fact table (i.e. compute it at ETL time). This is probably the best approach in term of performance. But it requires to update that column if you change the target for some past complaints.


Permissions for sys.database_mirroring in SQL Server I need to assign permission to use sys.database_mirroring and of course I want to give the minimum amount of privilege. When I looked at microsofts homepage it says:

  To see the row for a database other than master or tempdb, you must
  either be the database owner or have at least ALTER ANY DATABASE or
  VIEW ANY DATABASE server-level permission or CREATE DATABASE
  permission in the master database. To see non-NULL values on a mirror
  database, you must be a member of the sysadmin fixed server role.

After trying on a test server I can't figure out what the last sentence mean. Anyone know in which cases sysadmin privilege is needed?

The comment: "To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role" is found in the discussion of sys.database_mirroring dynamic management view at:

https://msdn.microsoft.com/en-us/library/ms178655.aspx

Try running the following statement:

select * from sys.database_mirroring

Using my sysadmin account, I get a row for every database. Most rows are all NULL because they are not mirrored databases, but the two mirrors report their status. If you only want to check running mirrors you could use:

select * from sys.database_mirroring
where mirroring_guid is not null

Using my non-sysadmin account, I get nothing. No rows returned, no ROWCOUNT, et cetera, just a delayed 'ding' sound.

So, that note is just trying to tell you not to be surprised when you get no information back. (Except, perhaps, a 'ding' sound.)


How to delete foreign key with lots of references Table B have several thousand records that references table A with a foreign key.

The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts.

If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate).

Assuming that it could lead to a long deletion operation, what could be done as an alternative?

I've though perhaps to delete the record as deleted and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted column.

Any ideas?

If the only concern is timeout there are probably 3 ways to get around that.

Like referenced by Lennart, proper indexing should allow even the deletion of 1000's of rows to happen fairly quickly.  Assuming you can run a query like below fairly quickly, a DELETE would only be negatively impacted by the number of indexes on [Table B] that have to be updated for each row removed.

SELECT [Table B].ID
FROM [Table B]
    INNER JOIN [Table A]
        ON [Table B].[Table A ID] = [Table A].ID
WHERE [Table A].ID = @Value

Extend out the command timeout on the operation that triggers this delete.  The user would need to wait for it to complete, but they would be notified of a failure if there was one and would know for sure that the row (and all its child data) was removed.
(Full-disclosure, I dislike this option but it make serve your purposes if the above two don't help enough) You can also create a Delete Log table.  Where you would store whatever records from [Table A] need to be removed.  Your Delete operation on the end user application would be a simple Insert into this Delete Log table.  You would then have a background process (through either an Automation tool or SSIS/Sql Agent) that takes that log and removes the associated Table B and Table A records.  This would then have all the time it needs to remove the records in question.  The main downside is all the records exist until the background routine Runs and then completes so the end user would need to understand that their records are not removed immediately.

Hopefully this helps.


Storage size of a bigint table I am working with a table that has this format :

CREATE TABLE dbo.ID_STORE
( WORKING_ID bigint PRIMARY KEY CLUSTERED )

The table stores around 2 millions rows, but the stored ids are not consecutive,  MAX(WORKING_ID)-MIN(WORKING_ID) is around 24 millions .

When I look at  used space, I find around 57 Megabytes, when I expected slightly above  2 10^6 x 8 = 16 Megabytes. Can anyone explain the difference?

EDIT : these figures were obtained from the very first import into said table. This table is also TRUNCATEd before it is filled.

Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered index. Optimally stored, and disregarding the upper index levels, 2 million rows would require just over:

(7 + 8 bytes) * 2,000,000 = 28.61MB.

More importantly, pages are likely to have split (unless the data was loaded in clustered key order), so the current pages will probably be less than 100% full. When a page is split, to accommodate new rows in key order, roughly 50% of the existing rows are moved to a new page, lowering the average density. Also, any deleted rows will only result in space being reclaimed if the whole page from which rows were removed becomes empty. In addition, each 8KB data page has a 96-byte header, and 2 bytes per row on the page for the row offset array.

The following example loads 2,000,000 rows with approximately the same distribution as your data, compacted as much as reasonably possible:

CREATE TABLE dbo.ID_STORE
(
    WORKING_ID bigint NOT NULL PRIMARY KEY CLUSTERED
);

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 2000000;

The output of:

EXECUTE sys.sp_spaceused
    @objname = N'dbo.ID_STORE',
    @updateusage = 'true';

...shows 33,800 KB space reserved for this object.

Truncated and loaded so page splitting occurs:

TRUNCATE TABLE dbo.ID_STORE;

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 1000000;

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12 + 1
FROM Nums
WHERE Nums.n <= 1000000;

The output of:

EXECUTE sys.sp_spaceused
    @objname = N'dbo.ID_STORE',
    @updateusage = 'true';

...now shows 50,632 KB KB space reserved.

Rebuilding the clustered index:

ALTER INDEX ALL
ON dbo.ID_STORE
REBUILD
WITH
(
    MAXDOP = 1,
    SORT_IN_TEMPDB = ON
);

...reduced the space reserved to 33,800 KB again.

Depending on the version and edition of SQL Server you have, this table can be stored even more compactly using row or page compression, clustered columnstore, or clustered columnstore archival storage. In the latter case (SQL Server 2014 Enterprise required), the 2,000,000 rows reserve just 2,960 KB of space.


Listener.ora problem when I copy OVA I deployed a new VM Oracle machine using a copy of my existing Oracle VM. As a result, the new VM inherited the same host name.  This is redhat linux 6.5, Oracle11g. I changed hostname everywhere as far as I know. However, when I start the listener, it still points to old host name!

I can not find listener.ora under the usual $../network/admin.  I created one but still it ignores it.

Any suggestions?

Thanks in advance

You may need to update /etc/hosts to map the local IP with the new hostname.


How to configure SSIS in SQL Server AlwaysOn I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it's difficult to understand this article:

AlwaysOn Availability Groups and SQL Server Jobs, Part 23: Maintenance Plan Backups and SSIS Packages

Can someone guide me on basic setup of SSIS in an Availability Group and explain how synch up happens between nodes?

Update

I am referring to the SSIS Catalog database (SSISDB)

Can I use listener in my connection managers?

In SQL Server 2014 & 2012, adding the SSISDB as an Availability Database in an Availability Group is not supported. My understanding is that with some workarounds, adding the SSISDB to an AG is possible, but not supported. The workarounds sound error-prone, and I've not attempted it personally.

Rather than adding the SSISDB to the AG, I'd suggest evaluating the business needs (High Availability and/or Disaster Recovery), and see what other options are the best choice.

Depending on your HA/DR needs, consider maintaining two parallel SSISDB catalogs, one on each AG server. From an operational perspective, you will need to ensure that SSIS packages are deployed to both servers, and that they are kept identical (packages, permissions, etc). This would give you the ability to use the locally-installed SSIS service on either machine to run packages for HA/DR. You would also need to make sure that you do not have SSIS packages running on both servers.

In SQL Server 2016, adding the SSISDB to an Availability Group is supported & documented. If upgrading is an option, you may consider that as well.

Within your SSIS package connections, you can use the AG listener to connect to the active node of the AG for data access. SSIS does not need to run on the same server that is hosting your data.


MySQL Use an In Clause With a Comma Delimited Column I am trying to use a column that has a comma delimited list of values in an in clause.  I thought about doing a contains but it would not be correct when searching for single digit values.
Example:  1,5,12,14,16,15,18,19,23,24,25,26,27,28,29,31,32,33,34,35,36,37,38,40,41,43,44,45,47,48,49,51,53,54,55,56,57,59,61,62,63,64,65
This is the error that I am getting:

[MySql.Data]
ErrorCode: -2147467259, Number: 1064
ErrorMessage: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'cpei5.value' at line 12

Here is my query:
SELECT soi.*
FROM   sales_order_item soi
       INNER JOIN sales_order so
            ON  so.entity_id = soi.order_id
       INNER JOIN sales_order_address soa
            ON  soa.parent_id = so.entity_id
       INNER JOIN catalog_product_entity cpe
            ON  cpe.sku = soi.sku
       INNER JOIN catalog_product_entity_text cpei5
            ON  cpei5.row_id = cpe.entity_id
       INNER JOIN eav_attribute ea5
            ON  ea5.attribute_code LIKE 'state_not_allowed'
            AND ea5.attribute_id = cpei5.attribute_id
WHERE  soa.address_type = 'shipping'
       AND date_add(so.created_at, interval 365 DAY) >= CURRENT_DATE()
       AND date_add(so.created_at, interval 365 DAY) <= date_add(CURRENT_DATE(), interval 30 DAY)
       AND cpe.type_id = 'simple'
       AND soa.region_id IN cpei5.value

col IN (1,2,34)        -- A
col IN ("1,2,34")      -- B
col IN ("1","2","34")  -- C

Binding tends to produce B, but...
A and C have 3 choices, B has only one choice (a 6-character string)
Both A and C work if col is INT; C works if col is VARCHAR.
A, B, and C, may be able to use an INDEX.
FIND_IN_SET(col, "1,2,34")  -- D

Find_in_set has these characteristics:

Easy to 'bind'
Won't use an INDEX
Works with strings or numbers.


MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk directory. I keep getting this error after running the following statement taken from mysql page directly

CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
     DATA DIRECTORY = '/home/egonzalez/Documentos';

  ERROR 1030 (HY000): Got error 168 from storage engine

As far as I know, is a permission problem that mysql doesn't have access to the directory, but after changing permission in the directory I don't know how to proceed.

If you want the table created in that data directory with .ibd file, do this

chown -R mysql:mysql /home/egonzalez/Documentos

Then, run this

CREATE TABLE t2 (c1 INT PRIMARY KEY)
ENGINE=InnoDB DATA DIRECTORY = '/home/egonzalez/Documentos';

The option innodb_file_per_table is enabled by default in MySQL 5.7

The reason for the error in the first place ?

You need to run CREATE TABLESPACE like this

mysql> CREATE TABLESPACE `ts1`
    ->     ADD DATAFILE '/home/egonzalez/Documentos/mytblspc.ibd'
    ->     ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

Then, you could create any table into be included in the one tablespace like

CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE=InnoDB TABLESPACE ts1;


Who has SHOWPLAN permissions on a database Is there a way I can see all users in a database who currently have access to view execution plans? That is, who has SHOWPLAN permissions

You asked "in a database", so setting aside sysadmins and people who have been granted permission at the server level, you can see people who were granted or denied that permission at the database level by running this query in the database you're curious about:

SELECT p.name, perm.*
  FROM sys.database_permissions perm
  INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id
  WHERE perm.permission_name = 'SHOWPLAN';

Note that I'm not filtering for state_desc (grant or deny) there because if you're interested in who has access, you're probably also interested in who's been denied access.


How do I select a string valued field against a number less than X? There are two field formats. One is SOMENAME1234 and another is SOMENAME1234_SE. What I need to do is select all of the records where the number 1234 is less than X. The application is a test database where Jenkins generates unique schema names to keep our developer's tests from crashing into each other. The value '1234' represents a build number.

What I'm trying to write is the cleanup script. Problem is that I need to make sure that I'm not removing any schemata that are currently in use. So I found a way to pragmatically hit Jenkins for what is currently building. Such that now I have a value X that represents the lowest build number currently building. I can delete any schemata with a value lower than this. Problem is that I don't know how to write that SQL. Looking at REGEXP_LIKE, I see that there might be a regular expression involved.

Currently I have:

SELECT username
FROM dba_users
where username NOT LIKE '%"+upperBound+"%'
AND username LIKE 'BLDXYZPDQ%'

This is the best I've been able to do so far.

What I intend is to select the list and then drop each schema in that list one at a time. That way I need not worry about new schemata created during the process.

Since the format is not specified exactly, I will just assume the usernames follow the above pattern without any special cases. Find the number:

select to_number(replace(replace('SOMENAME1234_SE', 'SOMENAME', ''), '_SE', '')) as build_number from dual;

BUILD_NUMBER
------------
        1234

Generate commands for dropping users:

select 'drop user ' || username || ' cascade;' from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < :X;

Or execute them in PL/SQL:

declare
  x number := 1234;
begin
  for cmd in (select 'drop user ' || username || ' cascade' as text from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < x)
  loop
    execute immediate cmd.text;
  end loop;
end;
/


How to make conditional join when containing Nulls in some columns use for join Is there a way to make the following join more efficient? It needs to join the following:

Always on dset_cob_dt and tlsn_trd_id
In addition if the meas_data.tlsn_leg_id is not null it needs to join on tlsn_leg_id as well
and in addition to that also on tlsn_vrsn_num if similar to the last one meas_data.tlsn_vrsn_num is not null.

The below query seems to be incorrect. What would be the correct way to do it?

Select...
     left outer join tlea
      on (dset_cob_dt = tlea_cob_dt
          and
          ((meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num
          and meas_data.tlsn_leg_id = tlea.tlsn_leg_id)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id
          and meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num)
          or (meas_data.tlsn_trd_id = tlea.tlsn_trd_id))
          )

One possibility would be the use of nvl in oracle. But I'm not sure how I can achieve the same result as the above. It is the data on the meas_data side which sometimes contains Null for tlsn_vrsn_num and/or tlsn_leg_id, in which case it should ignore those columns for the join.

First of all, your description does not match the ON condition. Your description would fit an ON like this:

Select ...
from ...
     meas_data  m
     left outer join  tlea  t
       on  m.dset_cob_dt = t.tlea_cob_dt
       and m.tlsn_trd_id = t.tlsn_trd_id
       and (
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id   = t.tlsn_leg_id
           or
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id is null
           or
                 m.tlsn_vrsn_num is null
           )

As for efficiency, I'd suggest a composite index on the 4 columns, on each table (but I'm nowhere near a Oracle expert, so take this suggestion with caution).


Options and best practices for Multiple Result Sets in Postgres11 I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database.  SQL Server allows a stored procedure to contain multiple select statements.

SELECT u.username, ... FROM User u WHERE u.userId = p_userID;
SELECT r.roleName, ... FROM Role r WHERE r.userId = p_userID;

When retrieving data from each result set, the client does something like:

using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        result1.Add(reader.Get<int>("username"));
        ...
    }
    reader.NextResult();
    while (reader.Read())
    {
        result2.Add(reader.Get<string>("roleName"));
        ...
    }
}

What's the recommended way to do this in PostgreSQL 11?  (I realize many variations of this question have already been asked and answered regarding multiple result sets, but I would like a concise list of options and a recommended best practice if it exists.)  Here are the options I've found so far:

Use a Function that RETURNS SETOF refcursor.
This lengthy debate is confusing and I'm not sure what the final status is on the issue.  Requires that the function call be wrapped in a transaction(?) and then execute a Fetch on each. (another link).  I'm hoping there is a more straightforward way to do this that doesn't require a transaction.
Don't use a function or procedure, just use inline sql and batch statements.
(This works with my client code, but then I don't get the advantage of precompilation.  We can't have multiple statements in a single PreparedStatement):

SELECT ... FROM TableA a; SELECT ... FROM TableB b;

Don't use cursors, create 2 functions, each returning a Table type that defines the structure of the result.
(Requires a trip to the database for each function call.)
Use the new StoreProcedure in PostgreSQL 11 that were going to support multiple result sets.
(Sounds like this feature didn't make it into version 11)
Combine the data from each result set in a join.
Gives the benefit of precompilation and 1 round trip, but it just feels gross when I consume the data in code.  In my case, I want to get user data and a list of roles for the user.  I suppose I could get data like
userA columns..., role1 columns
userA columns..., role2 columns

Which of these (or some other?) is the recommended way to get multiple result sets?  i.e., is there a way to return 2 table types in a function or procedure?

The third solution is the best one if you want to save round trips and want to use prepared statements.

If you use cursors, you'll get a round trip per fetch.
When sending multiple statements in one call, you cannot have prepared statements.
You cannot use a stored procedure.
Putting two result sets into one with a join: that way lies madness.

Is is good to avoid excessive client-server round trips, but trying too hard to optimize can result in the opposite.


Multiple counts in a SQL statement Is it possible to have more than one count in a SQL statement such as:

SELECT
    student_id,
    (
        COUNT (ATTENDANCE_ID)
        from attendance
        where attendance_id IN ('ABSENT')
    ),
    (
        COUNT (ATTENDANCE_ID)
        from attendance
        where attendance_id IN ('PRESENT')
    )
    FROM attendance;

COUNT counts values that are not NULL. You can transform the unneeded values into NULL for example with DECODE. Combining the two as below:

select
    student_id,
    count(decode(attendance_id, 'ABSENT', 1, null)) as absent_count,
    count(decode(attendance_id, 'PRESENT', 1, null)) as present_count,
from
  attendance
group by
  student_id
;

Or you could put 0 instead of null and use SUM.


Simple access query based on many to many relationship I have a simple many to many relationship set up on access with "List", "DayJunction" (Junction Table) and "Days". I am trying to query for records in "List" that have no related records in "Days".

I have tried the following SELECT query:

and the following COUNT query:

But neither is giving me the desired result. Is it possible to extract these results using just one query? Also why did the previous two not give me the intended results?

I'm going to assume for the purpose of this answer that these records refer to days in which a particular company attended something.  If that's correct you can do this with two tables instead of three, and it's a one-to-many join if you don't have duplicate company names.  It is perfectly ok to have a many-to-many join if your tables are something like this:

List of companies, Companies

Company

Dell
Apple
Google

List of attendance by company, Attendance

Company       Day_attending

Dell          08/01/2016
Dell          08/31/2016
Apple         08/05/2016
Apple         08/08/2016

So in this case you'd be looking for Google as the output since Google has no attendance records.

Access doesn't know how to do this with the query designer, so you'll have to go into SQL mode and enter the code yourself.  If you were in normal SQL you'd use EXCEPT, but Access doesn't support it.  Instead, you can do a funky join by Company value where Company is null.  Like this:

SELECT c.Company
FROM Companies as c
LEFT JOIN
Attendance as a
    ON c.Company = a.Company
    WHERE a.Company is null


why no partition elimination I have a temp table #termin which contains 3 rows

When I execute the following query

SELECT t.termin, ttw.tourid, twt.va_nummer_int
  FROM #term AS t
 INNER JOIN plinfo.t_touren_werbeflaechentermine AS ttw
     ON ttw.termin = t.termin
  INNER JOIN wtv.t_werbeflaechentermine AS twt
     ON twt.jahr   = t.jahr
    AND twt.termin = t.termin
    AND twt.ID_Wt  = ttw.id_wt
    GROUP BY t.termin, ttw.tourid, twt.va_nummer_int

I'll get the following execution plan:

Each table is joined using a matching index. Both tables are partioned by ps_termin(termin).

For the first table (t_touren_werbeflaechentermine) it does an partition elimination and read only a subset of row, while for the second table (t_werbeflaechentermine) it scans the whole index (jahr, termin, id_wt include (va_nummer_int)).

So my question: why does it an index scan (and no seek) and why does it not eliminate the partitions for the second table.

PS: when using WITH (FORCESEEK) on the second table, it switchs both tables in the execution plan and does the full index scan on the first one...

PPS: Execution plan can be found here

Why an index scan?

It's likely that SQL Server estimated a lower cost for scanning the data (only 1.7MM rows, so a relatively small table) as compared to a loop-seek approach.

Scanning the t_werbeflaechentermine table will process all 1,708,658 rows, and your plan shows that this performs 7,347 logical reads.

A loop-seek is estimated to perform ~70K seeks (the cardinality estimate for the outer side of the loop). If we assume a seek is a binary search, we might therefore estimate that this will have a complexity of 70109.4 * LOG(1708658,2), or 1,451,575.

This is slightly lower than the 1,708,658 rows processed by the scan, but the number of logical reads will be much higher since each of the (estimated) ~70K seeks will perform several logical reads, yielding many more logical reads then the scan that was chosen. This might be the reason that a loop-seek plan yields a higher estimated cost and was not selected.

Viewing the plan without scans

If you'd like to compare this plan to the execution plan that uses a loop-seek is used for both joins, you can try adding the OPTION (LOOP JOIN) query hint to your query so that both joins use a loop join. It would be informative to post this actual execution plan for comparison.

Why no partition elimination?

I believe that partition elimination does not occur for t_werbeflaechentermine because SQL Server does not have a hash join algorithm that performs partition elimination based upon the observed partitions on the build side of a hash join. This would be a nice optimization in some cases, but to my knowledge is not available in the current optimizer: Partition elimination is available for the inner side of a loop join, but not for the probe side of a hash join (unless the query contains an explicit predicate on the partition column).

For further reading, SQL Server does have the concept of a collocated join, in which the hash join is applied independently for each partition of two tables that are partitioned the same way. However, this optimization is only available 2-way joins, so your query does not qualify. Paul White describes this and other partitioned table join considerations in much more detail in Improving Partitioned Table Join Performance.


how to get overdue complaints using join two tables First table

Complaint_id         Completetion_date        Templateid
53035                 2018-11-16 00:00:00        5
47956                2018-11-03 00:00:00        13
3953                 2017-11-27 00:00:00        17

Second table

Template_month                        id
8                                      5
6                                      13
4                                      15
2                                      17

In first table completion date is 16 Nov 2018 and tempalteid is 5 and in the second table id 5 have 8 month means need to calculate 8 months from 16 nov this is not overdue complaint. In the second row of first table completion date 2018-11-03 00:00:00   having templateid 13 and in the second table there is 6 month means periods is overdue then in the result 3953 is overdue id

I am using this query

select complaint_id
from complaints as comp
INNER JOIN template ON comp.templateid=template.id
where comp.completion_date::date + ('interval ' || template.template_month::text || ' month')

You can't create an interval like that. To have a variable number of months,  create a 1 month interval and multiply it with your number of months.

You also need to compare the result of adding the interval to the completion date to something. I assume you want to compare it to "today":

select comp.complaint_id
from complaints as comp
  JOIN template as tmpl ON comp.templateid = tmpl.id
where comp.completion_date + interval '1 month' * tmpl.template_month < current_date;

Online example: https://rextester.com/JGNX75589


How do I use "Not Equal to" in SSRS? How do I show a message if none of the rows returned have a specific value?
If all of the results in a report have a status of "OK", I want to return a message indicating that there were no warnings.
How can I do that?
Steve

So, the answer was simple, I was using "!=", I needed to use "<>".
Problem solved.
Sc


Fastest query for distinct IDs in a many-to-many relationship I have this table in PostgreSQL 9.4:

CREATE TABLE user_operations(
    id SERIAL PRIMARY KEY,
    operation_id integer,
    user_id integer )

The table consists of ~1000-2000 different operations each of which corresponds to some subset (consisting of approximately 80000-120000 elements each) of the set S of all users:

S = {1, 2, 3, ... , 122655}

Parameters:

work_mem = 128MB
table_size = 880MB

I also have an index on the operation_id.

QUESTION: What would be the most optimal plan for querying all distinct user_id for a significant part of the operation_id set (20%-60%) like:

SELECT DISTINCT user_id FROM user_operation WHERE operation_id < 500

It's possible to create more indexes on the table. Currently, the plan for the query is:

HashAggregate  (cost=196173.56..196347.14 rows=17358 width=4) (actual time=1227.408..1359.947 rows=598336 loops=1)
  ->  Bitmap Heap Scan on user_operation  (cost=46392.24..189978.17 rows=2478155 width=4) (actual time=233.163..611.182 rows=2518122 loops=1)
        Recheck Cond: (operation_id < 500)
        ->  Bitmap Index Scan on idx  (cost=0.00..45772.70 rows=2478155 width=0) (actual time=230.432..230.432 rows=2518122 loops=1)
              Index Cond: (operation_id < 500)

Is such query plan really optimal in such circumstances? I mean, I'm not sure about correctness of using Bitmap Heap Scan. I'll appreciate any references to relevant articles.

What would be the most optimal plan for querying all distinct user_id
  for a significant part of the operation_id set (20%-60%).

Use a recursive query:

WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT user_id
   FROM   user_operations
   WHERE  operation_id < 500
   ORDER  BY user_id
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id
   FROM   cte, LATERAL (
      SELECT user_id
      FROM   user_operations
      WHERE  operation_id < 500
      AND    user_id > cte.user_id  -- lateral reference
      ORDER  BY user_id
      LIMIT  1
      ) u
   )
TABLE cte;

In combination with an index on (user_id, operation_id) - columns in that order. I expect index scans that filter on the second column. Reasonably accurate table statistics are important so Postgres knows it will only have to skip a few rows in the index to find the next user_id. Generally, one might want to increase the statistics target for operation_id in particular:

ALTER TABLE user_operations ALTER operation_id SET STATISTICS 1000;

Since there are only ~1000-2000 different operations, this may not even be necessary, but it's a small price to pay.

Details:

Optimizing queries on a range of timestamps (two columns)

If the predicate operation_id < 500 is stable (always the same), make that a partial index on just (user_id) instead:

CREATE INDEX foo ON user_operations (user_id) WHERE operation_id < 500;

Then statistics on operation_id are not relevant to this query any more.

Even if the predicate is not stable, there may be ways to optimize - depending on the full range of possible conditions and value frequencies.

Performance should be ... delicious.

I optimized the technique in this related answer on SO (with detailed explanation):

Optimize GROUP BY query to retrieve latest record per user

If you have a separate users table and a large part of all users can be found in your sample, even faster query styles are possible. Details in the linked answer.


How can I reuse a table name after deletion (in DynamoDB)? I am working through a tutorial which requires creating a DynamoDB table with a specific name. On my first pass at creating the table I did something out sync with the tutorial, so I deleted the table with the intention of starting over again. However now when I try to create a table with the same name I get the error "Table with that name already exists."

I could use a different name, but the tutorial is particularly long and complex, and I would prefer to stay exactly in sync.

Is it possible to reuse a table name? Do I just need to wait for the original name to be recyclable?

Answering my own question:

I waited about ten minutes and then the error went away. It appears that the name is in a cache which is periodically refreshed.


Event 17058 - initerrlog: Could not open error log file (SQL Server) I'm new to SQL Server and encountering an error when running sqlservr.exe
(Microsoft® SQL Server® 2008 R2 SP2 - Express Edition)

  Error 17058, Severity: 16, State: 1.
  initerrlog: Could not open error log file ''.
  Operating System Error = 3 (The system cannot find the path specified.).

Here are my startup parameters:

-dc:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;
-ec:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lc:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

I've also made sure that all users have full control access and removed read-only permission from folders

Thanks!

I face the same problem after windows update on our server. Resolved with these steps.

rename or delete ERRORLOG file found in below directory

c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

start service

I think the file corrupted. When I change the name, it created a new file.


How can I use RMAN to show if there are archive logs that need to backed up? My main concern is knowing if the Oracle Archive Logs haven't been backed up in a while because something is broken that I may not know about.

When I query v$log it appears to be good with First_Time being very recent.  I would think v$log wouuld have lots of rows if the logs had not been backing up.

SELECT * FROM v$log;

Shows only a few, two say current (four returned).

select * from v$log_history order by FIRST_TIME desc;

Shows lots.

You can do this from RMAN.

list archivelog all backed up 0 times to disk;

or

list archivelog all backed up 0 times to sbt_tape;

Depending on the backup method you use.


Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:

I need to run updates on the entire database to find things like phone numbers in a non-standard format, and convert them to a standardized format.
I need to be able to run an analysis on things like, give me all the records which share the same phone number with another record and similar queries.

All fields I will primarily querying have been setup as indexes, but the only unique index is the primary key.
The powerful desktop machine I have is a Ryzen processor with 4 CPUS with 4 cores per CPU, and it has 64GB of RAM. There is also a dedicated 2TB SSD drive for the database (OS is running on a different drive).
I currently am operating off of a default Xampp Configuration on Windows 10 Pro running MariaDB 10.4.17.
What changes do you recommend to the MariaDB or computer setup in order to get the best performance out of this scenario and hopefully make things go faster. I've had some update queries take over 16 hrs. I know I could likely optimize those queries more; but I also imagine there are a lot of settings in the default Xampp config that could be significantly tweaked.

Update
I'm kind of surprised by the responses. I know I can make queries even better, etc; but there isn't one database setting I can tweak to make things run a little bit smoother or make it more geared towards the size and type of data I have?

Yes, data should be cleansed before inserting.  (Apparently, you are belatedly doing the cleansing.)
This may help:  DROP INDEX on phone_num while cleaning up the numbers.  ADD INDEX after finished.
Do the UPDATEs in chunks.  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks  Chunk based on the PRIMARY KEY; check for errors; chunksize only 1000.
It would take some code, but you might benefit by having 16 (a la 16 cores) connections, each working on chunks of the UPDATE.  (Don't expect more than 8x improvement; might not get even that much.)
Please provide more details if you want to discuss further.


loading a csv file which is on local system in to Mysql DB which is on remote server Can we directly load a CSV file ( which is on the local system) on MYSQL DB ( which is installed on the Remote server ) ?

'load data infile into table name' command can only be used for loading in local system only.

Are you looking for LOAD DATA LOCAL INFILE?

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Update: The original question exhibits a significant misunderstanding about LOAD DATA INFILE that originally escaped my attention:

'load data infile into table name' command can only be used for loading in local system only.

The client was referred to as being "local" and the server was referred to as being "remote," which makes that statement 100% incorrect.
LOAD DATA INFILE requires that the file already be on the server's filesystem, and adding LOCAL means it must be on the client's filesystem.
From the documentation:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server.
If LOCAL is not specified, the file must be located on the server host and is read directly by the server.


How can I Estimate Table Sizes within Schema (Oracle) I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far:

SELECT table_name, owner, last_analyzed
FROM all_tables

I'm fairly new to SQL so I have no idea how I would go around doing this.

Thank you.

Look at the "dba_segments" view (or user_segments if you don't have dba rights). The following query should give you what you're looking for:

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;


Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11 This question is related to the following 2 SQL errors (line breaks added by me)

MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;
this is incompatible with DISTINCT

MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

My problem is simple to describe, even without posting the original SQL. I have a Mysql 5.7.11-log server running on Windows in which I can SELECT A,B GROUP BY A and SELECT DISTINCT A,B ORDER BY C without any problem. Yes, this is wrong from the SQL point of view.

But it works and customers are happy. It will never be fixed be fixed in the future.

We just installed a fresh standard Mysql 5.7.13-log on Linux using Yum. Now all queries containing such errors break.

I have looked around and it seems that Mysql team did some work about improper GROUP BYs. I have tried to disable full group bys but then I am left with the distinct issue.

I have also tried to replicate part of the Mysql configuration from the Windows box but returned again to both issues.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I just want to know how do I tell the new Mysql 5.7.13 to behave exactly like the (bugged?) version 5.7.11. We will try to install the exact 5.7.11 from Yum but it could take a considerable while. So to be more clear: how do I get rid of those two errors, humbly given that the SQL statements need to be eventually fixed?

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.


If not all ids are present return nothing I have the following query

SELECT ATV.ATTRIBUTEVALUE_ID,
       ATV.ATTRIBUTE_ID,
       ASS.ASSET_ID
FROM   CLOTH.ATTRIBUTEVALUE ATV
JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
JOIN   CLOTH.ASSET ASS
ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )

which returns

ATTRIBUTEVALUE_ID, ATTRIBUTE_ID, ASSET_ID,
7                      2          9103354
8                      2          7829028
9                      2          7829028
10                     2          7829028

It should  return nothing, because 13057020 is not returned.

What this means is, when I have ATTRIBUTE_ID # and all ASSET_ID are not present (like now, 13057020 is missing) nothing should be returned.

One way:

WITH
  qry AS
  (  -- your query
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  ),
  cnt AS
  ( -- count condition to be satisfied
    SELECT 1
    FROM qry
    HAVING COUNT(DISTINCT ASSET_ID) = 3
  )
SELECT qry.*
FROM qry, cnt ;

The cnt will have 0 rows and so will the cross join (FROM qry, cnt) if the condition is not met.

You could write the final query like this, perhaps it's more readable:

WITH
---

  cnt AS
  ( -- count condition to be satisfied
    SELECT COUNT(DISTINCT ASSET_ID) AS assets
    FROM qry
  )
SELECT qry.*
FROM qry
WHERE (SELECT assets FROM cnt) = 3 ;

Another way would be a window function:

WITH
  qry AS
  (  -- your query with one more column
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID,
           COUNT(DISTINCT ASS.ASSET_ID) OVER () AS assets
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 )
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  )
SELECT qry.ATTRIBUTEVALUE_ID,
       qry.ATTRIBUTE_ID,
       qry.ASSET_ID
FROM qry
WHERE qry.assets = 3 ;


Recovery from datafiles Thanks to the recent weather/floods, we lost many of our servers. This included our central database server as well as the server that holds our backups. The database server was running Oracle 12c enterprise on RHEL6. We offload our backups to an offsite facility once a month. Long story short, our most usable backup is over 3 weeks old. The database server was sent to a data recovery place, who managed to get us partial files, some of which we believe contain our datafiles. But these files are incomplete and lack any / all header information. Is there any way of salvaging any data out of these files at all? Opening up the files with a hex editor displays the data we need, but we are taking about a 50 GB database and mining through the datafiles manually would take forever. We have pretty much given up recovering anything but I thought I would just ask the question here in case someone else has had a similar experience. I am aware of Oracle DUL tool (already opened an SR with Oracle in this regard and this was their advice) but the fees they charge are exorbitant. We are small company - we have already paid a tidy sum for the data recovery service. We simply cannot afford to pay what Oracle are asking for, as much as we would like to. Can anyone suggest a possible alternative please? And we are currently reviewing our data backup policies but as I mentioned - we are a small business with limited resources - and we didn't plan for natural disasters like we should have - we are going to be more careful in the future though.

UPDATE : Right turns out the hard drives themselves are fine though the LVMs are corrupt. I managed to rebuild the partitions with the help of testdisk. But I can't seem to mount the drives. I get an error "NTFS Signature Missing". Looking at dmesg it looks like mount has looked for etx3 and ext4 super blocks and didn't find them. So I am assuming the partitions are corrupt.

Thanks in advance.

Alex

Right,

For anyone else who might come across this post - I am just adding this information for the sake of completeness.

It took me a long time to get this sorted. I booted off of a SystemRescueCD and ran testdisk. It managed to recover my LVM partitions, which I couldn't mount due to damage to the disks. I tried numerous file recovery tools. R-Studio was probably the most helpful, it managed to recover file fragments.
I also ran Scalpel version 2.0. This version allows for specification of minimum file size, which was important in my case since I knew how big the files were. Scalpel took roughly 3 days, but we managed to recover our 5 data files. However without any of the other files (controlfiles, archived redo logs etc) recovery was still a challenge. I managed to create new controlfiles and rebuild the spfile from a previous copy of the pfile. I ended up starting the database with resetlogs. So no far we haven't had any reports of missing data.

We were incredibly lucky. While I would say that even in the worst imaginable scenario there might be hope for recovery, it is in no way an excuse to eschew prescribed backup methods. I cannot overstate how lucky we were that we managed to salvage our data, after having to deal with a malfunctioning RAID controller, corrupt partitions and missing files. My employers have now taken a more serious and pragmatic view towards offsite backups. but I wouldn't want to see anyone in this position. Like they say - prevention is better than cure.

For reference :
Oracle Data File Headers in Hex : 00 A2 00 00 FF C0 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 7A 7B 7C 7D
Oracle Data File Footer : 00 00 00 01
The question marks represent characters which will be different for every data file. Scalpel allows you to specify wildcards.
R-Studio also allows defining a specific file type and searching for that file type, but I didn't try it.

Thanks to everyone who took time to share their advice. I am grateful to you all.

Regards

Alex


Performance issue with sp_executesql and VARCHAR parameter Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)).

Execution time of the first query is 34 seconds

SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = 'DEP345'
     AND  seg.BDPID       = c.BDPID

When I moved the parameter for DEPARTMENT to a variable, execution time became 1 second. Execution plan #2 (fast)

DECLARE @dd VARCHAR(10)
SET @dd = 'DEP345'
SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = @dd
     AND  seg.BDPID       = c.BDPID

But I have to use dynamic sql. And when I moved query to sp_execitesql the execution time again became 34 seconds. Execution plan #3 (slow)

EXECUTE sp_executesql
     'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID',
     '@dd VARCHAR(10)',
     @dd = 'DEP345'

How can I get performance of the second query using dynamic sql?

What you're running into is the curse of local variables.

The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value.

It sometimes uses magic numbers depending on how the variable is used (there are different guesses for BETWEEN, >, >= , <, <=, <>.

For equality searches, the density vector is used, (though the column being defined as unique matters here) but the bottom line is that the cardinality estimate is usually way off. Lots more information here.

This is your plan for the fast query:

For parameterized dynamic SQL, the value can be sniffed, and you end up with a totally different plan with totally different estimates.

If you want a quick and dirty option, you can use OPTIMIZE FOR UNKNOWN

EXECUTE sp_executesql
    @stmt =  N'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID
          OPTION (OPTIMIZE FOR (@dd UNKNOWN));',
          @params N'@dd VARCHAR(10)', @dd = 'DEP345'

Which will get you the density vector estimate, but that's a hacky solution and I sort of hate it. This of course goes out the window when you remove the TOP 10 from your query, which you mentioned in a comment is just for this post.

What I'd much rather see you do is some index tuning. I'm willing to bet the Segment table is crying for a clustered index -- that many rows in a HEAP is usually a sign of trouble (forwarded fetches can ruin your day).

The nonclustered index that gets used here isn't covering, which is also dragging this query down badly.

You could get rid of the RID Lookup by adding FINAL_SEGMENT as an included column.


Differences between CMS and MSX/TSX? What is the differences between Central Management Servers and Master Server (MSX) / Targer Server (TSX) concepts in SQL Server? I know that MSX/TSX is related to SQL Agent and the basic idea is to run jobs from MSX on TSX (possibly many), but I thought this is also possible in CMS scenario (by using scheduled stored procedures). Is that so?

The MSX/TSX is indeed related to the SQL Agent, allowing you to schedule jobs on a master server and have those jobs pushed out to slave servers.

CMS is an extension on the registered servers concept and allows you register servers into the MSDB database of a single instance through the SSMS GUI.  Once the servers are registered in the CMS you can logically group them by business unit, version, etc.  Once you have them registered any DBA/user with permission to the instance can connect to the CMS and see the same grouping.

CMS does not allow you to schedule agent jobs like MSX does, but you are able to run a script through SSMS on servers grouped in the same folder in CMS.  For example, if you highligh a subfolder in CMS and hit New Query it will connect to all the instances registered in that folder and run the script on each one.  Below is an example script you could run; CMS will return the instance name and the user running the script as columns by default.  You can also use CMS for grouping Policy Based Management evaluations and policies.

  select @@VERSION as Verison


The Database Session is automatically Getting Killed I am running a java program which connects to the oracle db and retrieves some information. The program contains 12 threads, each thread interns execute sql select commands with multiple join conditions on the tables.

Some times the application runs fine and giving the exact result but sometimes it is not. Finally what we have found is the database session is getting killed.
But there are no scripts running at the db side to kill the idle sessions, no session time out parameter is set with the db profile for that particular user. No locks on the tables. And i have handled all the exceptions in front end application, but it is not throwing any exceptions. the program is keep on running even the session is killed.....
please suggest.............

Thanks
ABCReddy

I would hope you have some session pooling going on, or at least one database connection per thread.  This is not clear from your description.  In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread.  Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run.  If they take minutes, to run you need to ensure you have keep-alive on the network connections.  Otherwise, the network connection may get dropped.

EDIT:  Check the resource limits applicable to the userid you are connecting with.  These might get your connections closed.  You may also be getting killed off due to exceeding resource limits.  I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries.  Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially.  You may be forcing data out of the buffer pool with this many long queries running in parallel.


Hash index and where like clause From Mysql documentation it is clearly said that Hash indexes  are used only for equality comparisons that use the = or <=> operators.

I have a small test table:

`CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1;`

And query: explain select * from test where test.name like 'sun%'
And it shows that it uses index:
And query: explain extended select * from test where test.name like 'sun%'

But it can not be according to documantion. Mysql version: 5.6.32-78.0.
Any ideas?

Because the table has InnoDB engine and only 2 columns, the one in the index and the PRIMARY KEY column, the index is a "covering" index for the query, so it is used. It's not used as effectively as a B-tree would be used but it still covers the query. Note that any secondary index of an InnoDB table carries the PRIMARY KEY columns data as well, so in this case it's actually (name, id) internally.

Consider that the optimizer had 2 options, either scan the whole table or scan the whole index. It chose the index because it is less wide (InnoDB tables have a extra "baggage" of 13 bytes per row. For the secondary indexes this baggage is smaller.)

You can check by doing some tests:

Test A: add a third column in the table, without adding any indexes and check the execution plan. It would probably do a table scan and not use the index. The index carries the name and id (primary key) data but not any other column data, so it would not be covering the SELECT * query.
Test B: convert the table to use MyISAM engine and check the execution plan again. Indexes for MyISAM tables do not carry the primary columns, so the plan should again probably be a table scan and not use the index.
Test C/D: same as A or B (add a 3rd column or convert the table to MyISAM) but run the query SELECT name FROM test WHERE name LIKE 'sun%';. The index should be used again as it is covering the query and is cheaper to use than a table scan.


SQL Server Restore Disk Response Time So I have a new SQL Server in place.  I have a 200GB database I am trying to restore to this new server and the restore time is taking longer than my current server.  My new server has a RAID 10 for the database files, a RAID 1 for the log files and a RAID 5 (where the backup files are located).  I can't seem to figure this out. When I try and copy files directly to the RAID 10, I am getting copying speeds to 1.2GB/sec, but when restoring through SSMS, (looking at the resource monitor), it is only moving at like 50MB/sec.  I just can't figure what is happening here.  Anyone have any tips?

Thanks in advance,

So the answer ended up being bad raid controller.  I had a Dell tech come out and replace the raid controller and drives.  Then there was a setting in the Dell configuration to allow the dual raid controllers to work independently.


How can you tell if a table in DB2 has a Primary Key? We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined.

Is there a way using a select statement on the DB to see if a given table has one?

Thanks.

Disclaimer: I do not know DB2.

I simply googled these with "db2 table definition".

Source:

SELECT *
FROM SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
WHERE TAB.CREATOR = COL.TBCREATOR
AND TAB.CREATOR = 'xxxx'
AND TAB.NAME = 'xxxxxxxxxxxxx'
AND TAB.NAME = COL.TBNAME
AND TAB.TYPE = 'V' ( OR 'T' )
ORDER BY 1,2;

Source:

SELECT * FROM syscat.tabconst WHERE type = 'P';


PostgresSQL authentication error I recently forgot my PostgreSQL 9.1 password on my windows 8 and it asks for password on pgAdmin III console.

Can I retrieve the password or reset it?

After researching, I found that we need to find the pg_hba.conf file under

C:\Program Files\PostgreSQL\9.3\data

Run the notepad++ as administrator (or the editor you usually use to edit the files)
Open the file and at the very bottom, look for

# TYPE  DATABASE        USER            ADDRESS                 METHOD

For all the entries for TYPE host, change the METHOD from md5 to trust. Save the file. (Make sure there are no interactions with the database at this moment.)
Press Windows+R to get the run dialog, type services.msc and press enter.
Once you see the services(Local) window, choose a service named
"postgresql-x64-9.3 - PostgreSQL Server 9.3" and on the left hand side, restart the service.
Now go back to the pgAdminIII and run the server using default password.
Once you could connect to the server, flip back the settings on the pb_hba.cfg file and save it. Remember before quitting pgAdminIII, do not forget to change the password.

Choose File->Change password to change the password (old password would be a default of postgres).


Issues installing patches on SQLserver 2008 There are multiple issues with a server I manage, we cannot start management studio on it and when we try to install patches to SQL Server the setup never shows up.

I run the patch as an administrator, it shows me that some files are being extracted to a temp folder, then a CMD window opens for a split second and closes immediately after.

I have looked around a bit to find a solution but can't quite decide where to start from.

Here is a link of someone saying he ran caspol.exe to fix a somewhat similar issue

What's caspol anyways? Having never used it I don't really want to create more issues by not knowing what it should be used for.0

Here is another link saying I need to add registry keys for my installation to begin:

Here is my server's select @@version query result :

  Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29
  2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation
  Standard Edition on Windows NT 6.0  (Build 6002: Service Pack 2)
  (VM)

The server is a windows server 2008 32-bit

The patch setup creates the following log when it fails :

03/17/2016 13:58:13.308 ======================================================================
03/17/2016 13:58:13.309 Setup launched
03/17/2016 13:58:13.310 Attempting to determine media source
03/17/2016 13:58:13.311 Media source value not specified on command line argument.
03/17/2016 13:58:13.390 Setup is launched from media directly so default the value to the current folder.
03/17/2016 13:58:13.391 Media source: g:\e89cdf338f18c1307f\
03/17/2016 13:58:13.391 Attempt to determine media layout based on file 'g:\e89cdf338f18c1307f\mediainfo.xml'.
03/17/2016 13:58:13.433 The folder 'g:\e89cdf338f18c1307f\' does not contain a valid media info file 'mediainfo.xml'.
03/17/2016 13:58:13.434 Setup closed with exit code: 0x84C4001E
03/17/2016 13:58:13.484 ======================================================================

It might be good to add that the windows search is not working, as you may observe in the following image :

Maybe the setup is trying to look for files but fails as it cannot search for them ?

If this was your server, what would be the first step of troubleshooting you'd go through ?

Here is a good place to start: https://support.microsoft.com/en-us/kb/969052

I ran into a similar issue as you and this allowed me to fix it. The script on that page ended up showing me all the items that needed to be fixed. In my case, the original install happened over a network share, which no longer existed. Using the output from the script, I manually copied the items to the local Windows installer cache.

Here is example of output of what it found that I did not have to fix:

================================================================================
PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio
================================================================================
  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}
  Version     : 10.53.6000.34
  Most Current Install Date: 20151113
  Target Install Location:
  Registry Path:
   HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList
     Package    : sql_bids.msi
  Install Source: \x64\setup\
  LastUsedSource: n;1;\\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\

 !!!! sql_bids.msi DOES NOT exist on the path in the path \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\ !!!!

 Action needed, re-establish the path to \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\

Installer Cache File: C:\Windows\Installer\12b7423e.msi

    Package exists in the Installer cache, no actions needed.
    Any missing packages will update automatically if needed assuming that
    the LastUsedSource exists.

    Should you get errors about C:\Windows\Installer\12b7423e.msi or \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\sql_bids.msi then you
    may need to manually copy the file, if file exists replace the problem file,
    Copy and paste the following command line into an administrative command prompt:

     Copy "\\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\sql_bids.msi" C:\Windows\Installer\12b7423e.msi

For that item, I did nothing. However, here is an example of something that it needed:

SQL Server 2008 R2 SP2 Database Engine Shared Patches Installed
--------------------------------------------------------------------------------
 Display Name:    GDR 1617 for SQL Server Database Services 2008 Core Shared (64-bit) (KB2494088)
 KB Article URL:  http://support.microsoft.com/?kbid=2494088
 Install Date:    20120227
   Uninstallable:   1
 Patch Details:
   HKEY_CLASSES_ROOT\Installer\Patches\17817DD65FBE36A47AA93B8ADF0CE829
   PackageName:   sql_engine_core_shared.msp
    Patch LastUsedSource: n;1;c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\
   Installer Cache File Path:     c:\Windows\Installer\f696075.msp
     Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\17817DD65FBE36A47AA93B8ADF0CE829\LocalPackage

!!!! c:\Windows\Installer\f696075.msp package DOES NOT exist in the Installer cache. !!!!

     Action needed, recreate or re-establish path to the directory:
       c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\ then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy missing files or manually
     replace the problem file,

     Copy "c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\sql_engine_core_shared.msp" c:\Windows\Installer\f696075.msp

     Replace the existing file if prompted to do so.

     Use the following URL to assist with downloading the patch:
      http://support.microsoft.com/?kbid=2494088

As you can see, it tells you what to copy and where to copy it to. In my case, I ignored the source location and just copied the listed files from a known good installation location (iso, installation media, local folder, or whatever has the installation files for that version of SQL Server). After that, I was able to finish patching SQL Server to the point where I could then install the latest service pack.

The difference between the two items above is the first one, even though it cannot find the file in the original location, the file is in the local Windows Installer cache where as in the second one, the original location does not exist AND the file is not in the windows installer cache.


PostgreSQL: Reference whole-table I have a project where data is being updated by 14 different users that will run over the course of a few months, collection approximately 5.5 thousand rows.. To avoid giving write access to each other's db I have given each their own DB with r/w access. At the end of the collection period I plan to collect them all to a single DB where they all will have read access only, still as individual tables.
My question comes to the following problem. In addition to 14 user tables brought into the read only DB, a last table will be created that will geo-reference each of the other tables as data collected at a specific location for use in POSTGIS.
All of the tutorials and docs I've looked at talk about referencing specific column values in another table, but never the whole table. Roughly the GPS geo-referencing table would look something like this:
COORDINATES    TABLE
x_lat,y_long   table_l
z_lat,w_long   table_m
a_lat,b_long   table_n

However from what I've been able to find online the REFERENCES column type is limited to something resembling the following:
COORDINATES    TABLE(col_z)
x_lat,y_long   col_z_value

Is the only solution to, when bring all the tables from different the individual DB's, to simply bring them into a single table where each row can be referenced by the individual device ID or user ID? I wanted to avoid having to do it that because 5500 rows x 14 tables will be rather large.
Based on the above context I am open to other solutions as well.
Thanks very much for any input!

Yes, there is no way to have a foreign key that points to different tables.
I think you should import the 14 tables into a single table, perhaps adding a column that indicated from which original table the data came (if you need that information).
Tables under 100000 rows are considered small these days, so I wouldn't worry on that account.