Does loading data to temp table improve performance? When I write stored procedure I use views that have calculation inside them and so if I have to reuse the view multiple times in my procedure, I load it to a temp table and then use the temp table throughout the procedure to avoid the same calculation begin done everytime the view is accessed.

But does this thing applies for straight table's data too?

So if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

Does it improve performance in any way? Since the data is then local to the procedure?

I am not sure because for procedure table data is also local in that sense.

Can any please help?

if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

It sometimes is.  It is a common performance optimization to "materialize" or "spool" intermediate results into a temp table, if putting the logic for returning the intermediate results in the main query turns out to be too complex or expensive.

This is not something you should do always.  Just if you discover that putting the logic in a single query is not running acceptably.

A good way to organize this is with Common Table Expressions, where you can have a "pipeline" of query logic.  eg:

with
q1 as ( select ...),
q2 as ( select ...),
q3 as ( select ...),
select ...
from q3;

Then if you want to spool one of the subqueries, it's as easy as

select ...
into #q1
from ...;

with
q1 as ( select * from #q1 ),
q2 as ( select ...),
q3 as ( select ...),
select ...
from q3;

"Lifting" the query logic from the CTE into a temp table load, and referencing the temp table in the CTE.


Aggregating date in one column For a purpose of reporting I need to present data from table:
table A (column1, column2, date1, date2, date3,...,dateN)

My query need to present all dates in one column separated with # .

(YYYY-MM-DD# YYYY-MM-DD#..)

But problem is that number of date columns is not fixed, because from product to product can be different number of dates.

Any idea ?

I'm was thinking about some ugly query such as:

select isnull(convert(varchar(8), date1, 121),'')
        + isnull('#'+convert(varchar(8), date2, 121),'')
        + isnull('#'+convert(varchar(8), date3, 121),'')
        ...
        + isnull('#'+convert(varchar(8), dateN, 121),'')
from A

But we could do better using something like:

select cast((
       select (
              select '#'+T2.N.value('./text()[1]',  'varchar(max)')
              from (
                   select T.*
                   for xml path(''), type
                   ) as T1(N)
                cross apply T1.N.nodes('/*') as T2(N)
              for xml path(''), type
              ).value('substring(./text()[1], 2)',  'varchar(max)')
       for xml path(''), type
       ) as varchar(max))
from A as T


How long is "too long" for MySQL Connections to sleep? I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.

What duration should the connection time limit be before the connection should be killed?

How do I set the time limit in MySQL 5.0.51a-3ubuntu5.4?

Note:

I am using PHP 5.2.x and symfony framework 1.2.

mysqld will timeout database connections based on two server options:

interactive_timeout
wait_timeout

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.


Compatibility of queries written in SQL Server 2012 with SQL Server 2016 Can queries designed and written for SQL Server 2012 be used as is on SQL Server 2016. We are planning an upgrade of SQL Server and want minimal manual intervention in order to transition our work

SQL Server is always backwards compatible, with the exception of items listed on the deprecated features list.

The query optimizer has changes that may result in performance increases, and in some circumstances, performance decreases.

Microsoft's Data Migration Assistant, provides a way to find deprecated functionality in use by your database.

  The Data Migration Assistant helps discover issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues and are organized in the following categories:

  Breaking changes
  Behavior changes
  Deprecated features

Microsoft also provides this list of breaking changes to database engine features.

Ensure you test the upgrade first in a non-production environment.

The following script can be used to create two Extended Events sessions that log the use of deprecated features and final-support features:

IF EXISTS (
    SELECT 1
    FROM sys.dm_xe_sessions xes
    WHERE xes.name = 'DeprecationFinalSupportEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationFinalSupportEvents ON SERVER;
END
IF EXISTS (
    SELECT 1
    FROM sys.dm_xe_sessions xes
    WHERE xes.name = 'DeprecationAnnouncmentEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationAnnouncmentEvents ON SERVER;
END
GO

DECLARE @LogPath VARCHAR(260);
DECLARE @FinalSupportPathETL VARCHAR(260);
DECLARE @FinalSupportPathMTA VARCHAR(260);
DECLARE @DepPathETL VARCHAR(260);
DECLARE @DepPathMTA VARCHAR(260);

SELECT @LogPath = REVERSE(SUBSTRING(REVERSE(path)
    , CHARINDEX('\', REVERSE(path)) + 1, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1))
FROM sys.traces;

SET @FinalSupportPathETL = @LogPath + '\DeprecationFinalSupportEvents.etl';
SET @FinalSupportPathMTA = @LogPath + '\DeprecationFinalSupportEvents.mta';
SET @DepPathETL = @LogPath + 'DeprecationAnnouncmentEvents.etl';
SET @DepPathMTA = @LogPath + 'DeprecationAnnouncmentEvents.mta';

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
CREATE EVENT SESSION DeprecationFinalSupportEvents ON SERVER
ADD EVENT sqlserver.deprecation_final_support (
    ACTION  (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
    )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @FinalSupportPathETL + '''
        , METADATAFILE = ''' + @FinalSupportPathMTA + '''
);

ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);

SET @cmd = '
CREATE EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
        , sqlserver.username
        , sqlserver.client_hostname
        , sqlserver.database_name
        )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @DepPathETL + '''
        , METADATAFILE = ''' + @DepPathMTA + '''
);

ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);
GO


Databases: are there universal datatypes? While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in the future?  While switching, I would like to avoid changing datatypes of the columns that are NOT available in the new database to appropriate datatypes that are available in the new database.

In other words, are there any ANSI datatypes that will work in all the databases with the same size?  That is, similar to "ANSI SQL", are there anything like "ANSI SQL Data types"?

For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.

The following statement:

create table products
(
   product_id    integer not null primary key,
   product_name  varchar(100),
   price         decimal(10,2)
);

works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g. integer will be changed to number in Oracle, or varchar to varchar2).

When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).

The basic data types as defined by the ANSI standard are:

CHARACTER
CHARACTER VARYING (or VARCHAR)
CHARACTER LARGE OBJECT
NCHAR
NCHAR VARYING
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL

Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have nchar - that list could go on and on). But the most common ones usually work quite well.

From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)

VARCHAR
INTEGER
DECIMAL
DATE (with surprises: Oracle has it but includes a time)
TIMESTAMP (does something different than expected on SQL Server and MySQL)

If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.


char vs varchar in mysql I was reading the book MySQL high performance, and it mentioned that:

  CHAR is also better than VARCHAR for data that’s changed
  frequently, because a fixed-length row is not prone to fragmentation.

But the thing I could not understand is how VARCHAR is subjected to fragmentation in the first place?

To elaborate on ypercube's correct comment, the problem is not that there is fragmentation on the VARCHAR, but in rows that contain variable length data.

If I were to have a table with these values:

+---+-------+
| i | v     |
+---+-------+
| 1 | some  |
| 2 | small |
| 3 | text  |
+---+-------+

And then issue:

UPDATE my_table SET v = 'dinosaur' WHERE i = 2

Then, depending on the storage engine, I might find that there's just no room for the word 'dinosaur' within the existing row location, since rows were written sequentially and compactly to disk. That might mean I would need to change the location of row #2, or place my 'dinosaur' text on an external location, or what have you.

On the other hand, if I were to issue:

UPDATE my_table SET v = 'a'

There would now be a lot of wasted space; I might be interested in reclaiming that space.

This is the fragmentation problem within rows.


SQL MS ACCESS: Split row into multiple based on column value I have table (SQL MS ACCESS)

COUNTRY   NO_OF_PAGES
FRANCE    2
UK        4
GERMANY   1

I want output

COUNTRY   NO_OF_PAGES
FRANCE    1
FRANCE    2
UK        1
UK        2
UK        3
UK        4
GERMANY   1

I create number table which contain value from 1 to 10,you may populate it with more as per your requirement.

Table name tblNumber (RowNumber number)

SELECT country, rownumber
FROM tblNumber AS N INNER JOIN table3 AS t ON n.rownumber<=t.noofpages;

here table3 is your table.


How does SQL Server reduce index fragmentation? If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes?

MSDN suggests that if an index is over 30% fragmented, it is recommended to run REBUILD instead of REORGANIZE. Would running REORGANIZE multiple times do the same things as REBUILD?

I wonder about this because I have a client that has a highly fragmented index. They run REORGANIZE against that index every weekend, and over time it seems like their index becomes defragmented.

Does this make sense?

No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to REBUILD or REORGANIZE.

Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Lock durations are short and will cause minimal blocking of queries.

Rebuild drops an index and builds a new one. With Enterprise edition this can be done as an online operation if the index doesn't include any LOB types. With Standard edition or where LOB types exist, it will cause blocking. See How Online Index Operations Work for an explanation of how online rebuilds can occur concurrently with user operations.

The Reorganize vs Rebuild recommendation is roughly the threshold at which the amount of work required to defragment by reorganizing is comparable with a rebuild i.e. at >30% fragmentation it will require more resources and greater elapsed time to complete a reorganize than a rebuild.

Multiple reorganize runs would not further defragment the index.


generate_series for multiple record types in postgresql I have two tables that I want to query: pest_counts and pests which look like:

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

I want to use postgres' generate_series to show the number of each type of pest that was found for the date series:

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | 0
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...

I know I'll need something like the following but I'm not exactly sure how to do the rest:

SELECT date FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') date

I usually solve such problems by setting up a table for all the possible data points (here the pests and dates).  This is easily achieved by a CROSS JOIN, see the WITH query below.

Then, as the finishing step, I just (outer) join the existing measurements, based on the pest ID and date - optionally giving a default for the missing values via COALESCE().

So, the whole query is:

WITH data_points AS (
    SELECT id, name, i::date
    FROM pests
    CROSS JOIN generate_series('2015-01-01'::date, '2015-01-05', '1 day') t(i)
)
SELECT d.name, d.i, COALESCE(p.cnt, 0)
FROM data_points AS d
LEFT JOIN pest_counts AS p
    ON d.id = p.pest_id
    AND d.i = p.count_date;

Check it at work on SQLFiddle.

Note: when either the table(s) or the generated series are big, doing the CROSS JOIN inside a CTE might be a bad idea. (It has to materialize all the rows, regardless of there is data for a given day or not).  In this case one should do the same in the FROM clause, as a parenthesized sub-join instead of the current reference to data_points.  This way the planner has a better understanding about the rows affected and the possibilities for using indexes.  I use the CTE in the example because it looks cleaner for the sake of the example.


Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as platform. They had this server crashing once in a while, so they increased the resources but they still have high CPU usage. From my understanding, high CPU usage itself does not imply an issue, but when I drilled down a bit, it got more interesting:

There was a query which had a significant amount of elapsed time per each execution. As they used Oracle Standard edition, I was unable to use the benefits of AWR but, as I checked the query, I saw something very strange.

The query is as follows, which uses function foo:

update LIST_JOURNAL
   set STATUS       = '4',
       END_DATE     = :b1,
       END_TIME     = :b2,
       END_TYPE     = '1',
       USER_ID  = 'SYSTEM',
       ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
 where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
       STATUS < '4');

They used only CHAR, VARCHAR2 and NUMBER as their datatypes (don't know the reason, maybe due to some migration from another database) and there is this FOO_FUNCTION in this query that calculates the interval between two timestamps, say one of them is a record in database and one of them is sysdate, (date and time are stored with CHAR and NUMBER datatypes, not DATE or TIMESTAMP) the function converts sysdate to string using TO_CHAR, then concatenate with TIME field, then do a TO_DATE, then subtract and then multiply to seconds in a day (60*60*24) and return the results. (For contract reasons, I cannot disclose the function code)

The function looks very strange to me and I want to flag it as a very important CPU bottleneck my report for database performance boost. I haven't seen the rest of the functions but I'm pretty sure wherever DATE is needed, something similar happens.

But I'm a bit reluctant to do so, as I've not seen any evidence in Oracle docs or somewhere else that this might be a problem. (I know, the function is so oddly written that my reluctance looks ridiculous) Also there is a good chance that software vendor does not accept this as a change.

My questions are :

Is the above function can be a CPU intense one? Can refactoring and using DATE and TIMESTAMP data types help boost the performance? Why? I can deduct some reasons, but as I need to present this, if there are some documents that have discussed the issue, I would be most grateful to see the links.
The table itself does not have any indexes - except for primary key - If we are stuck with the current code and situation, can an index help boosting the performance? How effective and sustainable can that be? I know I cannot expect detailed answers, but the table contains about 3.7M rows and I think for an update query, some indexes can help.

And my general questions:

Is there a performance downside for using CHAR and NUMBER instead of DATE and TIMESTAMP?
What would you recommend to my client?

Update:

I finally got the results. We added the following composite index :

create index I_LIST_JOURNAL ON LIST_JOURNAL (TERM_ID, NODE_CD, STATUS);

We instantly got some good results, CPU got down to 80 percent and execution time of a couple of queries decreased substantially (The query in question had average execution time of 15 million seconds, now it's about 2400 seconds average) but again, after a couple of days, it went up to 99 percent again..
I am looking to do another iteration of stat gathering and explain plan. But I'm not sure whether this helps reducing the load or not. I am thinking of adding a couple of other composite or simple indexes on the tables which have long running queries to eliminate "Table access full" in explain plan, but given the situation above and the tools I have, is there any other method I can use to get better results?

You want to flag the function for the reason of CPU bottleneck, based on what?

You could say you found the sql_id, and checked v$sql, compared elapsed_time to plsql_exec_time and found that a huge portion of elapsed_time comes from plsql_exec_time and cpu_time is also high.

Verify this with:

select elapsed_time, plsql_exec_time, cpu_time from v$sql where sql_id = '...';

Or even better, you can check where the time is actually spent with:

alter session set statistics_level=all;

Run the statement:

update LIST_JOURNAL
   set STATUS       = '4',
       END_DATE     = :b1,
       END_TIME     = :b2,
       END_TYPE     = '1',
       USER_ID  = 'SYSTEM',
       ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
 where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
       STATUS < '4');

Then view the actual statistics of this execution with:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

The above gives you exact execution count, timing, cardinality, buffer gets, disk reads, temp, memory, etc. information about each step of SQL execution. This works in any edition of the database and does not require any licensed option. There are various ways of achieving the above

There is no index on the LIST_JOURNAL table, but it has 3.7 million rows and the above output indicates that your statement performed a TABLE ACCESS FULL on LIST_JOURNAL with 100000 buffer gets but 0 reads because the table is in cache, but this step actually returned only 5 rows (A-Rows) and this step took 10 seconds, while the whole execution took 10.2 seconds? Sure, you are very likely to have a missing index problem. What index to create? We do not know, because the distribution of the data is unknown to us. You may need a composite index with all columns in the predicates or just 3 of them or a simple index on only the TERM_ID column may suffice.

Or the time spent in most steps is insignificant, but the time spent in the UPDATE step is high, combined with plsql_exec_time being relatively high compared to elapsed_time and a relatively low amount of affected rows? Sure, check the function.

The above is the absolute minimum that I would check, before even thinking about the datatypes used in the function. Collect the facts first before theorizing without the slightest idea where the problem originates from.

Some answers to the original questions:

Yes, it can be. Even with the proper datatypes.
Yes, it can. Depends on the data distribution, number of affected rows.
Depends. Typically yes, when storing dates.


csv engine and mysql start my question is about speed when perform service mysql start.

with many tables (some not often used).

what engines consumes more or less start time ?

example: innodb --> the more tables, the more time it takes for mysql to start because it has to "load" indexes at start...

but how is it with csv engine (no indexes) ; how mysql with "deal" with those tables during a mysql start ?

Thanks

(from Comment)  I don't use innodb. I have some aria tables, tokudb tables and rocksdb tables.

Innodb because its transactional does do recovery/rollback during startup. If it was an unclean shutdown this can take some time depending on tuning / data volume.

As other tables don't have extensive checks they are quick to start up.

Debian does script in myisam checks in the service start meaning the availability of MyISAM tables could be delayed.


Most cost efficient way to page through a poorly ordered table? I have a table that has three columns:
HashUID1, HashUID2, Address_Name (which is a textual email address, and the previous two hash colunms are of some crazy creation to link event participant tables to email addresses. its ugly, it barely works its out of my control. Focus on the address_name index)

It has 78 million rows. Not properly sorted. Regardless, this index is split onto a lot of fast LUN's and performs REALLY fast index seeks.

I need to create a series of queries to extract only 20,000 "rows per page" at a time, but avoid conflicts or dupes. Since there is no identity column, or easily ordered column, is there an easy way to select all, and page through it?

Am I correct in saying that if I do a select * from hugetablewithemails into a temp table, then select through it by row_number that the table remains in memory for the duration of the transaction, which, to me, is an excessive amount of memory resources?
This seems the preferred method of paging. I'd rather page by statistical percentages. :(

There is one index which maintains the address_name email address in order, and is well maintained. For the past week I have been meaning to help this other developer by spending some time on looking into building a proc that spits out ranges based on windowing functions based on statistics (which I am not great at, but this query really interested me) to provide a range of characters 1 through (variable) LEFT LIKE chars of the index, that meets 20,000 rows--But I have not had time to even start the query...

Couple questions:

Any suggestions? Not looking for actual code, just some hints or suggestions based on experiences, maybe caveats. I want to avoid additional index scans after the initial scan.
Is this the right approach?
I'm thinking of breaking the sum of the index of all email addresses, gathering rowcount(*), /20,000, and usinng that as a windowing function to group min/max substring(1,5) values based on percentages of total rowcount to build grouping ranges. Thoughts?

This is for an ETL process that cannot modify source databases.

I am hoping with one full index scan I can do a:

Query to get a histograph based on index usage (alphabetically sorted) and break it out (windowed) using min/max to create some ranges like this, so to easily seek the needed index:
A-> AAAX, (20k rows for example) AAA-Z, B-> (another 20k), B->BAAR -> BAAR-> CDEFG -> CDEFH > FAAH, etc.

We run read committed in these databases for this ETL process. We are only attempting to batch it out in scores of 20k rows because the DBA's say we are using too much network resources by grabbing tables in full. If the data has changed (which is a concern) we update our DW and staging tables on the fly.

I would love to use temp tables, but if I did, I'd spill into tempdb and get lashings via e-mail from the DBAs regarding it, and that the database is too big.

Essentially, you are asking if you can perform a single ordered scan through the data overall, while making no copies of the data, and returning 'x' disjoint sets of rows from the full set on each call. This is exactly the behaviour of an appropriately-configured API cursor.

For example, using the AdventureWorks table Person.EmailAddress to return sets of 1,000 rows:

DECLARE
    @cur integer,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt integer = 16 | 8192 | 16384,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536,
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT *
    FROM AdventureWorks2012.Person.EmailAddress
        WITH (INDEX([IX_EmailAddress_EmailAddress]))
    ORDER BY EmailAddress;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur,
        2,
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

Each fetch operation returns a maximum of 1,000 rows, remembering the position of the scan from the previous call.


Setting up relationships properly with the Access UI I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI.

I have to create a relationship between two fields in one table with one field in the other - have I done this correctly (please see the screen capture below)? If not, what would I do to correct my errors?

Thanks in advance.

I'm with Michael, you should reference the IP ID, not the IP. If you don't link to the primary key, whenever you reference [IP used when creating] or [IP last signin], you are going to get the first record found. e.g. you have multiple records with the same IP in you ips table, you are going to always get the result of the first one. This is why primary keys are so important, they ensure you get the same record every time. If you can assume that a particular IP will always have the same Hostname, Location, Region, etc., then you can sue IP as your reference.

On a side note, if you have to run any SQL or VBA against your database, it's a lot easier if you avoid spaces in your names, use Camel case or something else. e.g. "IP used when creating" should be IPusedWhenCreating. This makes any type of programming a lot easier. I'm very oldschool and I like to keep variables to under 8 characters whenever possible. FirstIP and SecondIP, it just means a LOT less typing.


You can use the rank function to get the last two terminalid's:

select terminalid, date, toolid, toolname, workcenterid
from (
    select terminalid, date, toolid, toolname, workcenterid
         , dense_rank() over ( order by terminalid desc ) as rnk
    from T
    where date <= now() -- this will be dependent of your DBMS
) as X
where rnk <= 2;

Note that date is a reserved word so it is wise to name the column otherwise. Beside that it is a date of some kind (hard to tell from the question), say transaction_date in lack of a better understanding of your domain.


how to clean huge Dbwarden QueryHistory table dbwarden is Comprehensive database monitoring solution for SQL Server 2005+ which i am using.

i found that dba_LongRunningQueries – Runs every 5 minutes. This will run usp_LongRunningQueries to determine if any queries on a database are running too long. It also stores data into the QueryHistory table.

what should we do when QueryHistory table is getting very large?. i just want to have the latest logs and old data to be removed by some job.

also please note that i clean up 2 GB after running following query, but it does not seems the correct way to do it.

DELETE [dbWarden].[dbo].[QueryHistory] WHERE DateStamp < '2014-5-28'

This script will help: http://www.sqlservercentral.com/scripts/dbWarden+monitoring+maintenance/118935/

As per description, "This script creates/modifies all objects necessary for a cleanup of dbWarden History."

Note it is behind a login to SQLServerCentral.Com but if you are using dbWarden I expect you have a login to that site already.

Below is the script (just incase if SCC is down or the article gets removed):

-- Ref: http://www.sqlservercentral.com/scripts/dbWarden+monitoring+maintenance/118935/
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc4HistoryTableCleanup]'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[Proc4HistoryTableCleanup] ( ' +
            '    @TableOwner    varchar(64),' +
            '    @TableName     varchar(512), ' +
            '    @DateFieldName varchar(50), ' +
            '    @retentionDays INT = 0,' +
            '    @Debug         TINYINT = 0' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')
END
GO

ALTER PROCEDURE [dbo].[Proc4HistoryTableCleanup] (
    @TableOwner     varchar(64),
    @TableName      varchar(64),
    @DateFieldName  varchar(64),
    @retentionDays  INT,
    @maxRowCnt      INT = 10000,
    @debug          TINYINT = 0
)
AS
/*
 ===================================================================================
  DESCRIPTION:
    This procedure takes in charge the clean-up for a given table based on a date field
    given as parameter and a retention expressed as a number of days.
    If the number of records to delete is more than a configurable limit, a dichotomic
    delete will be performed so that there is no matter on big open transaction without
    commit.

  ARGUMENTS :
    @TableOwner     name of the owner of the table to clean up
    @TableName      name of the table to clean up
    @DateFieldName  name of the date field to use for clean up
    @retentionDays  number of days to keep track
    @maxRowCnt      maximal number of rows to consider in an open transaction.
                    If the cleanup needs more than that number, the query will be cut
                    into pieces implying maximum @maxRowCnt
    @debug          if set to 1, this enables the debug mode

  REQUIREMENTS:

  ==================================================================================
  BUGS:

    BUGID       Fixed   Description
    ==========  =====   ==========================================================
    ----------------------------------------------------------------------------------
  ==================================================================================
  NOTES:
  AUTHORS:
       .   VBO     Vincent Bouquette   (vincent.bouquette@chu.ulg.ac.be)
       .   BBO     Bernard Bozert      (bernard.bozet@chu.ulg.ac.be)
       .   JEL     Jefferson Elias     (jelias@chu.ulg.ac.be)

  COMPANY: CHU Liege
  ==================================================================================
  Revision History

    Date        Nom         Description
    ==========  =====       ==========================================================
    18/11/2014  JEL         Version 0.1.0
    ----------------------------------------------------------------------------------
 ===================================================================================
*/

BEGIN

    --SET NOCOUNT ON;

    DECLARE @versionNb        varchar(16) = '0.1.0';
    DECLARE @tsql             nvarchar(max);            -- text to execute via dynamic SQL
    DECLARE @tmpRetentionDays INT;                      -- number of retention days to remove
    DECLARE @retentionInc     INT;                      -- increment for tmpRetentionDays

    BEGIN TRY
        --
        -- check parameter :
        --      retention days
        --
        if @retentionDays < 0
        BEGIN
            RAISERROR ('Parameter "retentionDays" is negative. Avorting execution', 10,1,@retentionDays);
        END

        if @retentionDays = 0
            return

        --
        -- check parameters
        --      table-related parameters are usable ?
        --
        IF(NOT EXISTS (
            SELECT 1
            FROM
                information_schema.COLUMNS
            where
                TABLE_CATALOG= DB_NAME(DB_id()) -- in current database
            AND TABLE_SCHEMA = @TableOwner
            AND TABLE_NAME   = @TableName
            and COLUMN_NAME  = @DateFieldName
          ))

        BEGIN
            RAISERROR('Parameters about the table are unusable in current database ', 10,1,@TableOwner, @TableName ,@DateFieldName )
        END

        if @debug = 1
        BEGIN
            PRINT '----------------------------------------------------'
            PRINT OBJECT_NAME(@@PROCID)
            PRINT '===================================================='
            PRINT 'Owner      = ' + @TableOwner
            PRINT 'TableName  = ' + @TableName
            PRINT 'Column     = ' + @DateFieldName
            PRINT 'Retention  = ' + CONVERT (VARCHAR ,@retentionDays)
            PRINT 'Max Row Nb = ' + CONVERT (VARCHAR ,@maxRowCnt)
            PRINT '----------------------------------------------------'
            PRINT CHAR(10)
        END

        --
        -- Iteration number evaluation
        --
        DECLARE @dateThresh DATETIME
        SET @dateThresh = GETDATE() - @retentionDays

        DECLARE @dateThreshStr   VARCHAR(100)
        SELECT @dateThreshStr = convert(varchar(100),@dateThresh,112)

        DECLARE @totalRecordNb   BIGINT
        DECLARE @MinDateFieldVal DATETIME

        SET @tsql =  N'SELECT '                             + CHAR(10) +
                    '    @totalRecNb = COUNT_BIG(*),'               + CHAR(10) +
                    '    @minDate    = MIN([' + @DateFieldName + '])'   + CHAR(10) +
                    'FROM'  + CHAR(10) +
                    '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                    'WHERE' + CHAR(10) +
                    '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

        execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

        if @debug = 1
        BEGIN
            PRINT 'Min Date in table : ' + CONVERT (VARCHAR ,@MinDateFieldVal,112)
            PRINT 'Number of records : ' + CONVERT (VARCHAR ,@totalRecordNb)
        END

        if(@totalRecordNb > @maxRowCnt)
        BEGIN

            -- divide by 2 the number of days between min date and the retention period
            DECLARE @nbOfDaysToAdd INT
            SELECT @nbOfDaysToAdd = (DATEDIFF(day,@MinDateFieldVal,@dateThresh) / 2)

            if @debug = 1
            BEGIN
                PRINT 'Too many records to take care at once !'
                PRINT 'Days to play with : ' + CONVERT (VARCHAR ,@nbOfDaysToAdd)
            END

            -- TODO : if days is not enough => play with hours !

            SET @tmpRetentionDays = @retentionDays+@nbOfDaysToAdd
            if @debug = 1
            BEGIN
                PRINT 'New retention : ' + convert(varchar,@tmpRetentionDays)
            END

            exec [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@tmpRetentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug

            if @debug = 1
            BEGIN
                PRINT 'Restarting procedure with the previous retention :' + CONVERT (varchar,@retentionDays)
            END

            execute [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@retentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug
        END
        ELSE
        BEGIN
            if @debug = 1
            BEGIN
                PRINT 'Deleting ' + CONVERT (varchar,@totalRecordNb) + ' records'
            END

            SET @tsql = 'DELETE'    + CHAR(10) +
                        'FROM'  + CHAR(10) +
                        '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                        'WHERE' + CHAR(10) +
                        '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

            execute sp_executesql @tsql, N'@dateThresh VARCHAR(100)', @dateThresh = @dateThreshStr

            if @debug = 1
            BEGIN
                SET @tsql =  N'SELECT '                             + CHAR(10) +
                        '    @totalRecNb = COUNT_BIG(*),'               + CHAR(10) +
                        '    @minDate    = MIN([' + @DateFieldName + '])'   + CHAR(10) +
                        'FROM'  + CHAR(10) +
                        '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                        'WHERE' + CHAR(10) +
                        '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

                execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

                PRINT 'Nb of records after delete : ' + CONVERT (varchar,@totalRecordNb)
                PRINT 'Minimum date after delete  : ' + CONVERT(varchar,@MinDateFieldVal,112)
            END

        END

    END TRY

    BEGIN CATCH
        PRINT 'ErrorNumber    : ' + CONVERT (VARCHAR , ERROR_NUMBER())
        PRINT 'ErrorSeverity  : ' + CONVERT (VARCHAR , ERROR_SEVERITY())
        PRINT 'ErrorState     : ' + CONVERT (VARCHAR , ERROR_STATE())
        PRINT 'ErrorProcedure : ' + CONVERT (VARCHAR , ERROR_PROCEDURE())
        PRINT 'ErrorLine      : ' + CONVERT (VARCHAR , ERROR_LINE())
        PRINT 'ErrorMessage   : ' + CONVERT (VARCHAR , ERROR_MESSAGE())
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN --RollBack in case of Error

        RAISERROR ('Unable to proceed !', 10,1,@retentionDays);

    END CATCH
END

/**

Usage example :
==============
exec dbo.Proc4HistoryTableCleanup
    @TableOwner     = 'dbo',
    @TableName      = 'CPUStatsHistory',
    @DateFieldName  = 'DateStamp',
    @retentionDays  = 700,
    @debug          = 1

exec dbo.Proc4HistoryTableCleanup
    @TableOwner     = 'dbo',
    @TableName      = 'CPUStatsHistory',
    @DateFieldName  = 'DateStamp',
    @retentionDays  = 300,
    @debug          = 1
*/

alter table  dbo.DataDictionary_Tables
add retentionDays INT DEFAULT 90

update dbo.DataDictionary_Tables
set retentionDays = null
where TableName in (
    'AlertContacts','AlertSettings','DatabaseSettings','DataDictionary_Fields',
    'DataDictionary_Tables','SchemaChangeLog','ServerChangeLog'
)

update dbo.DataDictionary_Tables
set retentionDays = 90
where TableName in (
    'BlockingHistory','CPUStatsHistory','FileStatsHistory','HealthReport','JobStatsHistory',
    'MemoryUsageHistory','PerfStatsHistory','QueryHistory'
)

alter table  dbo.DataDictionary_Tables
add dateField VARCHAR(100)

update dbo.DataDictionary_Tables
set dateField = 'DateStamp'
where TableName in (
    'BlockingHistory','CPUStatsHistory','HealthReport',
    'MemoryUsageHistory','QueryHistory'
)
update dbo.DataDictionary_Tables
set dateField = 'FileStatsDateStamp'
where TableName = 'FileStatsHistory'

update dbo.DataDictionary_Tables
set dateField = 'JobStatsDateStamp'
where TableName = 'JobStatsHistory'

update dbo.DataDictionary_Tables
set dateField = 'StatDate'
where TableName = 'PerfStatsHistory'

create view dbo.CleanupSettings
AS
select
    SchemaName,
    TableName,
    dateField as DateFieldName,
    retentionDays
from dbo.DataDictionary_Tables
where retentionDays is not null;

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbWarden_HistoryCleanup]'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[dbWarden_HistoryCleanup] ( ' +
            '    @Debug         TINYINT = 0' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')
END
GO

ALTER PROCEDURE [dbo].dbWarden_HistoryCleanup (
    @debug          TINYINT = 0
)
AS
/*
 ===================================================================================
  DESCRIPTION:
    This procedure takes care of purging old data inside tables defined in the
    table or view dbo.CleanupSettings of the same database as the one in which this
    procedure has been created.

  ARGUMENTS :
    @debug          if set to 1, this enables the debug mode

  REQUIREMENTS:

  ==================================================================================
  BUGS:

    BUGID       Fixed   Description
    ==========  =====   ==========================================================
    ----------------------------------------------------------------------------------
  ==================================================================================
  NOTES:
  AUTHORS:
       .   VBO     Vincent Bouquette   (vincent.bouquette@chu.ulg.ac.be)
       .   BBO     Bernard Bozert      (bernard.bozet@chu.ulg.ac.be)
       .   JEL     Jefferson Elias     (jelias@chu.ulg.ac.be)

  COMPANY: CHU Liege
  ==================================================================================
  Revision History

    Date        Nom         Description
    ==========  =====       ==========================================================
    19/11/2014  JEL         Version 0.1.0
    ----------------------------------------------------------------------------------
 ===================================================================================
*/

BEGIN

    --SET NOCOUNT ON;

    DECLARE @versionNb        varchar(16) = '0.1.0';
    DECLARE @tsql             nvarchar(max);            -- text to execute via dynamic SQL

    DECLARE @CurrentOwner       VARCHAR(50)
    DECLARE @CurrentTable       VARCHAR(50)
    DECLARE @CurrentColumn      VARCHAR(50)
    DECLARE @CurrentRetention   INT

    DECLARE getTablesToPurge CURSOR FOR
        SELECT *
        FROM [dbo].[CleanupSettings]

    open getTablesToPurge
    FETCH NEXT
    FROM getTablesToPurge INTO @CurrentOwner,@CurrentTable,@CurrentColumn,@CurrentRetention

    WHILE @@FETCH_STATUS = 0
    BEGIN

        if @debug = 1
        BEGIN
            PRINT 'Current Owner  : '    + @CurrentOwner
            PRINT 'Current Table  : '    + @CurrentTable
            PRINT 'Current Column : '    + @CurrentColumn + '(datetime column used in where clause)'
            PRINT 'Current retention : ' + convert(varchar,@CurrentRetention)
        END

        exec dbo.Proc4HistoryTableCleanup
            @TableOwner     = @CurrentOwner,
            @TableName      = @CurrentTable,
            @DateFieldName  = @CurrentColumn,
            @retentionDays  = @CurrentRetention,
            @debug          = @debug

        -- carry on ...
        FETCH NEXT
        FROM getTablesToPurge INTO @CurrentOwner,@CurrentTable,@CurrentColumn,@CurrentRetention
    END
    CLOSE getTablesToPurge
    DEALLOCATE getTablesToPurge

END

/**

Usage example :
==============
exec dbo.[dbWarden_HistoryCleanup]
    @debug          = 1

exec dbo.[dbWarden_HistoryCleanup]
*/

--
-- ------------------------------
-- Sample job : everyday @8:15PM
-- ------------------------------
--
IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = 'dbWarden_HistoryCleanup')
BEGIN
    BEGIN TRANSACTION
        DECLARE @ReturnCode INT
        SELECT @ReturnCode = 0

        DECLARE @jobId BINARY(16)
        EXEC
            @ReturnCode =  msdb..sp_add_job @job_name=N'dbWarden_HistoryCleanup',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'Database Monitoring',
            @owner_login_name=N'sa',
            @notify_email_operator_name=N'SQL_DBA', @job_id = @jobId OUTPUT

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC
            @ReturnCode = msdb..sp_add_jobstep @job_id=@jobId, @step_name=N'run proc',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'SET NOCOUNT ON
EXEC [DBA].dbo.dbWarden_HistoryCleanup',
            @database_name=N'master',
            @flags=0

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb..sp_update_job @job_id = @jobId, @start_step_id = 1

        DECLARE @schedule_id int
        EXEC msdb.dbo.sp_add_jobschedule    @job_id=@jobId, @name=N'dbWarden_Schedule_HistoryCleanup',
                                            @enabled=1,
                                            @freq_type=4,
                                            @freq_interval=1,
                                            @freq_subday_type=1,
                                            @freq_subday_interval=0,
                                            @freq_relative_interval=0,
                                            @freq_recurrence_factor=1,
                                            @active_start_date=20141119,
                                            @active_end_date=99991231,
                                            @active_start_time=201500,
                                            @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb..sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        COMMIT TRANSACTION
        GOTO EndSave
QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO


How to setup multi master replication in mysql After hours of searching I find lots of tutorials that do only 2 server Master Master. Now I wonder how to setup multi master setup because this is more suitable in my business. Because when the WAN connection is down my transaction in area's are still on going and then when the connection is back the changes in servers will be sync on. That's why i need multi master setup. Thanks in advance for any help and tips

MySQL Server master-master replication is possible for max 2 nodes.

You might want to use Galera Cluster (Percona Xtradb Cluster or Mariadb Galera cluster) Or Oracle's NDB Cluster to achieve HA as a better solution than master-master.


Search functionality using joins I have three tables as mentioned below. I need to implement basic search functionality where the user will search for a keyword which could either match 'title' (from Proposal table) or 'msNumber' or 'description' (from Project table) and should match 'stageNumber' and 'newState' from ProjectLifecycle table.

create table Proposal (
    proposalId LONG not null primary key,
    title VARCHAR(75) null,
);

create table Project (
    proposalId LONG not null primary key,
    msNumber VARCHAR(75) null,
    description VARCHAR(75) null
);

create table ProjectLifecycle (
    lifecycleId LONG not null primary key,
    proposalId LONG,
    stageNumber DOUBLE,
    newState LONG,
);

Below is the query which am using right now

SELECT
    p.proposalId, p.title, pj.msNumber
FROM
  Proposal p
JOIN
  ProjectLifecycle pl ON pl.proposalId = p.proposalId
JOIN
  Project pj ON pj.proposalId = pl.proposalId
WHERE
  (p.title like '%%' or pj.msNumber like '%%')
AND
    pl.newState=0
AND
  (pl.stageNumber= 60.60 OR pl.stageNumber < 60.60 OR pl.stageNumber = 95.99);

The stageNumber and the newState conditions are working fine as expected but the keyword search isn't.

Please help me in finding where I am going wrong. I am using MySQL which does not support FULL OUTER JOIN.

How would you write the FULL OUTER JOIN if it were available.  (I don't see the need for it in your case.)

p.title like '%%' is a costly no-op.  Remove it from the WHERE clause.  (I assume you are building the WHERE on the fly?  If so, make your code a little smarter.)

pl.stageNumber = 60.60 OR
pl.stageNumber < 60.60

-->

pl.stageNumber <= 60.60

ORs are costly because they often make it so the index cannot be used.

Are those stage numbers always dd.dd ?  If so, use DECIMAL(4,2).  DOUBLE has potential rounding errors, an may fail to match when you expect.


Memory utilization by SQL Server I m using SQL server 2012. I have a stored procedure that will eventually be tuned, but I have one question.  My server memory is 32 GB and max memory assigned in SQL Server is 20GB. When I execute this SP, the logical reads is around 4000000, so 4M data pages times with 8kb, divide by 1024, i will get 30 GB worth of data in memory.

My question is how SQL Server can keep 30 GB of data in memory if i only have assigned 20 GB of memory? Might be my question look silly but need your help guys. Thanks

First, you have a scan count of 17. I.e., it re-reads the same set of data over and over again, and the total for those are 4616999 logical reads. Assuming that the exact same set of data is re-read 17 times, you would divide your 30 GB with 17 to get the amount of data read from disk. So, at first pass it reads som 1.8 GB data, and then it re-reads this same set of data 16 times more. 1.8 GB data is no problem to fit in memory.

Second, even if you did have a scan count of 1, and the data wouldn't fit in memory, then where would the problem be? As it read some data, that data can later removed from memory during the read as it get further along the read operation.

Finally, don't get fooled by "physical reads" from statistics IO. You can also have physical I/O done as read-ahead, blob reads etc. Make sure you read the whole line and all numbers after the logical reads should be summed to get the total physical reads. Also, the perfmon buffer cache hit ratio counter don't include read-aheads, meaning it is very misleading.


Should a inherited table have a new surrogate key, or just use parent table's key? I am modeling some things with an "is a" relationship (aka inheritance), and there were two ways to go about it in regards to the inherited table's primary key and foreign key pointing back to the parent table:

Strategy #1 was to always create a new surrogate key on each inherited table, and then store the reference to the parent table's primary key in a separate column (and have unique constraint on that column to ensure a 1 to 1 mapping).
Strategy #2 was to make the primary key value of the inherited table have the same value as the parent table's primary key.

I think there is a clear winner here (Strategy #2), but I want to make sure there isn't some potential issue I am missing.

Here's the code (targeting SQL Server, but maybe generic) elaborating each strategy:

------Strategy #1: always create a surrogate key for a child table (separate column holds parent table's primary key)----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null, /*surrogate key*/
    AssetId int not null,
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    constraint UQ_AssetId unique (AssetId),
    foreign key (AssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null, /*surrogate key*/
    VehicleAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    constraint UQ_VehicleAssetId unique (VehicleAssetId),
    foreign key (VehicleAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, AssetId, VehicleRegistrationExpirationDate) VALUES (5678, 1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, VehicleAssetId, CargoCapacityCubicMeters) VALUES (9999, 5678, 8.80977)

GO

versus this

------Strategy #2: primary key references parent table's primary key directly----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null,
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    foreign key (VehicleAssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    foreign key (TruckAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, VehicleRegistrationExpirationDate) VALUES (1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, CargoCapacityCubicMeters) VALUES (1234, 8.80977)
----------------------------------------

Strategy #2 has a name. It is called Shared Primary Key.  It it widely used with is a type relationships.  There is a tag over in Stackoverflow that groups question related to this concept.

See link


Update existing SQL Server 2008 table via Flat File using SSIS I am trying to update existing records or add any new ones to a SQL Server table using a flat file. Also I need it work as an SSIS package. I don't get an option to do a source query like I do for Excel, etc. when setting up the import task and even when I was trying to use Excel my options seemed limited to simple selects for the source query.

I really need to be able to run a full script that checks the first column (which is my key), updates existing records, then adds any new ones. Maybe I am taking the wrong approach here altogether, but I thought there was a way to do this with SSIS. Can someone point me in the right direction? If it were two tables and I didn't have to use SSIS I would just write the sql and be done with it. It's really the bridge with using flat files and SSIS (automating the import) that I'm looking for here.

Just to give a quick background of what I ultimately am trying to accomplish:
We do exports of our data into flat files and those will go to a ftp server. We then have the customer import from those files into table representations of them so they can report against them.

You can follow either of the below approach :

Load the entire flatfile into a staging table and then update / insert the records based on the criteria you want. After the process is completed, you can truncate the staging table.
Look into using Lookup transform as described in Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services

Refer to this excellent series : Stairway to Integration Services for more details.


Query trans log or tempdb? New DBA, all of our databases are SQL 2005.  All of our servers run in Simple recovery mode.  I work for a school district, where we report student attendance to the state.

I run a query to fix reporting minutes on a specific grade level of students.  I have this scheduled as a job to run daily.

Our vendor is now saying that this is fixed in their product, but we believe it is fixed because we run the script daily.

Is there something that I can query to find out how many records were affected when the job is run?  I would need to do this for a date in the past (May 26, 2015).

Here is the script I run daily -

/*
The default absFTE value for students using twice daily attendance is 0.50.

This Query is used to change the absFTE values from 0.50 to 1.0 for PS students that are only
half day in order to send the full days worth of minutes.

*/

Update rev.EPC_STU_ATT_DAILY
Set ABS_FTE1 = '1.00'
Where DAILY_ATTEND_GU IN
(
Select
da.DAILY_ATTEND_GU
from rev.EPC_STU_ATT_DAILY da
join rev.EPC_STU_ENROLL en on da.ENROLLMENT_GU = en.ENROLLMENT_GU
join rev.EPC_STU_SCH_YR ssy on en.STUDENT_SCHOOL_YEAR_GU = ssy.STUDENT_SCHOOL_YEAR_GU
join rev.EPC_STU st on st.STUDENT_GU = ssy.STUDENT_GU
join rev.REV_ORGANIZATION_YEAR oyr on (oyr.ORGANIZATION_YEAR_GU = ssy.ORGANIZATION_YEAR_GU)
join rev.REV_ORGANIZATION org on (org.ORGANIZATION_GU = oyr.ORGANIZATION_GU)
join rev.EPC_SCH s on s.ORGANIZATION_GU = org.ORGANIZATION_GU
join rev.REV_YEAR y on y.YEAR_GU = oyr.YEAR_GU
join rev.SIF_22_Common_CurrentYear cy on cy.SCHOOL_YEAR = y.SCHOOL_YEAR
left join rev.REV_BOD_LOOKUP_VALUES grade on grade.VALUE_CODE=ssy.GRADE
      and grade.LOOKUP_DEF_GU=
      (
      select LOOKUP_DEF_GU
       from rev.REV_BOD_LOOKUP_DEF
       where LOOKUP_DEF_CODE='grade'
      )
Where da.ABS_FTE1 is not null and VALUE_DESCRIPTION IN ('PS')
)

Update rev.EPC_STU_ATT_DAILY
Set ABS_FTE2 = '1.00'
Where DAILY_ATTEND_GU IN
(
Select
da.DAILY_ATTEND_GU
from rev.EPC_STU_ATT_DAILY da
join rev.EPC_STU_ENROLL en on da.ENROLLMENT_GU = en.ENROLLMENT_GU
join rev.EPC_STU_SCH_YR ssy on en.STUDENT_SCHOOL_YEAR_GU = ssy.STUDENT_SCHOOL_YEAR_GU
join rev.EPC_STU st on st.STUDENT_GU = ssy.STUDENT_GU
join rev.REV_ORGANIZATION_YEAR oyr on (oyr.ORGANIZATION_YEAR_GU = ssy.ORGANIZATION_YEAR_GU)
join rev.REV_ORGANIZATION org on (org.ORGANIZATION_GU = oyr.ORGANIZATION_GU)
join rev.EPC_SCH s on s.ORGANIZATION_GU = org.ORGANIZATION_GU
join rev.REV_YEAR y on y.YEAR_GU = oyr.YEAR_GU
join rev.SIF_22_Common_CurrentYear cy on cy.SCHOOL_YEAR = y.SCHOOL_YEAR
left join rev.REV_BOD_LOOKUP_VALUES grade on grade.VALUE_CODE=ssy.GRADE
      and grade.LOOKUP_DEF_GU=
      (
      select LOOKUP_DEF_GU
       from rev.REV_BOD_LOOKUP_DEF
       where LOOKUP_DEF_CODE='grade'
      )
Where da.ABS_FTE2 is not null and VALUE_DESCRIPTION IN ('PS')
)

If you add the output clause at the end of each update statement with a COUNT(inserted.*) the counts of updated records will be returned as a result set.

You could create a new table in a new database to store the results from your scheduled script or have the SQL Agent output the results to a file.

Have a look at this answer for help with OUTPUT clause here


Dealing with corruption: file-level backup vs. pg_basebackup The wiki on corruption recommends immediately taking a file-level backup of your database by stopping the postmaster and using tar, but I was under the impression that using pg_basebackup was a better option.  Is there any reason I shouldn't use pg_basebackup?

pg_basebackup and other methods like pg_dump might not actually finish due to the corruption, and you really need that backup in that case. Being independent of PostgreSQL and its tools, tar is not as likely to run into those issues.

In the case of corruption, that backup will be an invaluable insurance policy when you try to recover your data or do forensics, so playing it safe is always the best way to go.


Is there a way to get the last NDF file READ/Access date / time? I have found a mysterious NDF file in my DB. Can't tell if it contains valid data or if the file is dead.

I'd like to remove it but I cannot tell if the file is in use by SQL Server. Windows Last Modified Date is 2 years ago, but for SQL files that is not reliable because SQL uses it's own drivers to access its files.

How can I tell what the last access time (either to read or to write) is for particular NDF file in a db?

Thanks!

Another option is to use sysinternals process utilities especially :

make sure you use the filter button to filter out unwanted things :-)

Process Explorer
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.

or

Process Monitor
Monitor file system, Registry, process, thread and DLL activity in real-time.


Why are multivalued dependencies named that way? I'm doing a relational design course on Stanford open courseware. I understand why functional dependencies are named that way - A->B means B is specified when A is specified, or in other words, the value of B functionally depends on the value of A. But I'm not able to think of a simple reason why multivalued dependencies are named that way. Understanding this would make it much easier to remember and use the concept.

A multivalued dependency A->->B means that each value of A determines a set of values of B (and not a single value of B as in functional dependencies).

For instance, suppose that programmers have an attribute programmer-id and an attribute known-language, and each programmer can know several languages, you have the multivalued dependency:

programmer-id ->-> know-language

So, supposing that the programmer with program-id 7 knows SQL and Ruby, this means that in a table in which you have both attributes programmer-id and know-language, every time there is program-id 7 there must be two different rows, one with language 'SQL' and one with language Ruby, and all the other attributes equal:

programmer-id  know-language other-attribute1 other-attribute2 ...
    7              SQL           XXX               YYY
    7             Ruby           XXX               YYY
    8             Java           AAA               BBB
 ...


Not retrieving data using Right Join The Above screenshot is my table structure..

Invoice Table

CREATE TABLE BASE_TInvoice
(
InvoicePaymentId BIGINT IDENTITY(1,1) NOT NULL,
PaymentDate DATE NOT NULL,
InvoicePaymentMethod VARCHAR(45) NOT NULL,
IsCashPayment BIT NOT NULL,
Note VARCHAR(5000) NULL,
IsRecordStatus BIT DEFAULT 1 NOT NULL
PRIMARY KEY(InvoicePaymentId)
)

Cash Inovice Table

CREATE TABLE BASE_TCashInvoice
(
CashInvoicePaymentId BIGINT IDENTITY(1,1) NOT NULL,
InvoicePaymentId BIGINT NOT NULL,
CashAmount  MONEY NULL,
CashReceived MONEY NULL,
CashBalance MONEY NULL,
IsRecordStatus BIT DEFAULT 1 NOT NULL
PRIMARY KEY(CashInvoicePaymentId)
)

I have used Right Join to retrieve data.

SELECT BASE_TInvoice.InvoicePaymentId, BASE_TInvoice.PaymentDate, BASE_TInvoice.InvoicePaymentMethod, BASE_TInvoice.IsCashPayment, BASE_TCashInvoice.CashAmount, BASE_TCashInvoice.CashReceived, BASE_TCashInvoice.CashBalance
FROM BASE_TCashInvoice RIGHT OUTER JOIN BASE_TInvoice ON BASE_TCashInvoice.InvoicePaymentId = BASE_TInvoice.InvoicePaymentId
WHERE (BASE_TInvoice.IsRecordStatus = 1) AND (BASE_TCashInvoice.IsRecordStatus IS NULL OR BASE_TCashInvoice.IsRecordStatus = 1) AND (BASE_TInvoice.InvoicePaymentId = 1)

The above query retrieving data fine, the bellow screenshot..

Once i set the BASE_TCashInvoice Status (IsRecordStatus) false and retrive data i'm getting blank, the below two screenshots.

I think the problem is in the where clause (BASE_TCashInvoice.IsRecordStatus IS NULL OR BASE_TCashInvoice.IsRecordStatus = 1)

How to retrieve exact data once BASE_TCashInvoice record InvoicePaymentId 1 and  IsRecordStatus went false need to display only the BASE_TInvoice table record only. Answer should be like below screenshot

Yes, the problem seems to be the condition that involves the CashInvoice table.
It should be moved from WHERE to ON. Besides that, I'd make a few more changes to the query, to make it more readable:

Refactor using aliases. Makes the queries shorter and easier to read.
Add comments in your code.
Format the queries nicely. No one likes to read code that is 300 characters wide.
Use LEFT JOIN instead of RIGHT JOIN. This is rather a stylistic matter but RIGHT joins are confusing to many. Even worse, if a query combines LEFT and RIGHT joins. I suggest you use one or the other everywhere, with preference to LEFT. This way, the "base" table(s) can be put always first, after the FROM and the "optional" tables in the end. Makes it easier for the future reader to identify which is which, especially when the query has many joins.
Add a statement terminator (;) at the end of every statement. Microsoft recommends that and in future versions non-terminated queries will be throwing errors (some already do).
Adding the WHERE to ON move of the condition,

the query becomes:

SELECT
    i  . InvoicePaymentId,
    i  . PaymentDate,
    i  . InvoicePaymentMethod,
    i  . IsCashPayment,
    ci . CashAmount,
    ci . CashReceived,
    ci . CashBalance
FROM
        BASE_TInvoice AS i              -- "base" table:      invoice:       i
    LEFT OUTER JOIN
        BASE_TCashInvoice AS ci         -- "optional" table:  cash invoice:  ci
    ON
        ci.InvoicePaymentId = i.InvoicePaymentId
        AND ( ci.IsRecordStatus IS NULL OR ci.IsRecordStatus = 1 )
WHERE
      i.IsRecordStatus = 1
  AND i.InvoicePaymentId = 1 ;


How to select data from differents servers with a single query? I have a challenge! I need select from 4 different tables from 4 different servers.

Example:

Select * from server1.tableS1.sys.view
Select * from server2.tableS2.sys.view
Select * from server3.tableS3.sys.view
Select * from server4.tableS4.sys.view

How I can make the select in the same query?

UNION is what you're looking for.

Select * from server1.tableS1.sys.view
union all
Select * from server2.tableS2.sys.view
union all
Select * from server3.tableS3.sys.view
union all
Select * from server4.tableS4.sys.view

You might want to do:

Select 'server1',* from server1.tableS1.sys.view
union all
Select 'server2',* from server2.tableS2.sys.view
union all
Select 'server3',* from server3.tableS3.sys.view
union all
Select 'server4',* from server4.tableS4.sys.view

... if you need to identify which rows came from which.


How to combine `delete` and `insert` operations in one statement? for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)?

create table product(product_id integer primary key);
insert into product(product_id) values(1);
insert into product(product_id) values(2);
insert into product(product_id) values(3);

create table split( parent_id integer not null references product,
                    child_id integer not null references product,
                    primary key(parent_id, child_id) );

insert into split(parent_id, child_id) values(1,2);
insert into split(parent_id, child_id) values(1,3);

create table sale(sale_at date, product_id integer references product);

insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 2);
insert into sale(sale_at, product_id) values(sysdate, 2);

select sale_at, product_id from sale where product_id not in (select parent_id from split)
union all
select sale_at, child_id from sale join split on(parent_id=product_id);

/*
|                     SALE_AT | PRODUCT_ID |
--------------------------------------------
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
*/

(SQLFiddle)

----edit:

to clarify, the effect I'm trying to get with a single merge statement is this:

insert into sale(sale_at, product_id)
select sale_at, child_id from sale join split on parent_id=product_id;

delete from sale where product_id in (select parent_id from split);

and the reason I'd like a single statement solution is to prevent a race condition where another transaction inserts/commits data between the insert and delete in the primary transaction.

You can do it in one go, sort of:

SQL> MERGE INTO sale s
  2  USING (SELECT ROWID rid, sale_at, product_id
  3           FROM sale
  4          WHERE product_id IN (SELECT parent_id FROM split)
  5         UNION ALL
  6         SELECT CAST (NULL AS ROWID) rid, sale_at, child_id FROM sale
  7           JOIN split ON (parent_id = product_id)) m
  8  ON (s.ROWID = m.rid)
  9  WHEN MATCHED THEN
 10     UPDATE SET s.sale_at = m.sale_at
 11     DELETE WHERE 1 = 1
 12  WHEN NOT MATCHED THEN
 13     INSERT VALUES (m.sale_at, m.product_id);

Done

SQL> select * from sale;

SALE_AT                                  PRODUCT_ID
----------- ---------------------------------------
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        2

This will delete the rows from sale that are present in split and replace them with their appropriate split products.

You could also write it:

SQL> MERGE INTO sale s
  2  USING (SELECT CASE WHEN row_number() over (PARTITION BY s.rowid
  3                                             ORDER BY sp.rowid) = 1
  4                     THEN  s.rowid
  5                END rid,
  6                s.sale_at, sp.parent_id, sp.child_id
  7           FROM split sp
  8           JOIN sale s
  9             ON sp.parent_id = s.product_id) m
 10  ON (s.rowid = m.rid)
 11  WHEN MATCHED THEN UPDATE SET s.product_id = m.child_id
 12  WHEN NOT MATCHED THEN INSERT VALUES (m.sale_at, m.child_id);

Here the row in sale will be replaced (updated) by its first split component and the additional components will be inserted.


UPDATE with multiple WHERE and SELECT condition - MySql So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the name that match to that ID but I think I need multiple WHERE conditions and SELECT, may anyone help me out? Thanks!
I have the following code to update

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?,
                           localizacao = ?, fabricante = ?, modelo = ?,
                           imei = ?, numero_serie = ?, ativo_sap = ?,
                           anexo_a = ?, evento = ?, data_evento = ?,
                         SELECT id_colaborador FROM colaboradores
                               WHERE nome = ? AND WHERE id_ativo = ?";

Your query (while removing PHP code and formatting) looks like

UPDATE ativos
SET ativo = ?,
    comentario = ?,
    data_aquisicao = ?,
    localizacao = ?,
    fabricante = ?,
    modelo = ?,
    imei = ?,
    numero_serie = ?,
    ativo_sap = ?,
    anexo_a = ?,
    evento = ?,
    data_evento = ?,
    SELECT id_colaborador
    FROM colaboradores
    WHERE nome = ?
    AND WHERE id_ativo = ?

It is syntactically incorrect. Use Multiple-table UPDATE syntax. Final query must look near

UPDATE ativos a
  JOIN colaboradores c
/* next line maybe wrong, question is not clear */
    ON a.id_ativo = c.id_colaborador
SET a.ativo = ?,
    a.comentario = ?,
    a.data_aquisicao = ?,
    a.localizacao = ?,
    a.fabricante = ?,
    a.modelo = ?,
    a.imei = ?,
    a.numero_serie = ?,
    a.ativo_sap = ?,
    a.anexo_a = ?,
    a.evento = ?,
    a.data_evento = ?
/* next line maybe wrong, question is not clear */
WHERE c.nome = ?


what does "if @@trancount > 0 commit tran" mean? What does this operation mean please?

if @@trancount > 0 commit tran

and after that

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

I do not understand the meaning of this sequence of commands even though I searched a lot online.

I think there are two concepts that you need to understand:

IF @@trancount > 0 COMMIT TRANSACTION
This checks whether there are still any open transactions from earlier in the
script, or that will be present if IMPLICIT_TRANSACTIONS are on. If you don't
check for open transaction before doing a COMMIT, you will receive the
following error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

IMPLICIT_TRANSACTIONS
To simplify this, when IMPLICIT_TRANSACTIONS is ON, there is an invisible BEGIN TRANSACTION before specific statements (see list here) if there aren't any transactions open already. And when IMPLICIT_TRANSACTIONS is OFF, your statement is in autocommit mode

If you provide us with a code example, we might be able to answer your question more specifically. You can play with this code to see how these commands affect a statement:
    /*
    SET IMPLICIT_TRANSACTIONS ON
    SET IMPLICIT_TRANSACTIONS OFF
    */

    --BEGIN TRANSACTION  --Uncomment if IMPLICIT_TRANSACTIONS is ON

    CREATE TABLE Test (col int)

    DROP TABLE Test

    SELECT @@TRANCOUNT

    BEGIN TRANSACTION

    SELECT @@TRANCOUNT

    COMMIT

    SELECT @@TRANCOUNT

    COMMIT

    SELECT @@TRANCOUNT

    --When IMPLICIT_TRANSACTIONS is ON, SQL will begin a new transaction count here,
    --You need to explicitly end the transaction at the end
    CREATE TABLE Test (col int)

    DROP TABLE Test

    SELECT @@TRANCOUNT

    IF @@TRANCOUNT > 0
        COMMIT

    SELECT @@TRANCOUNT


How do I choose a Mariadb version to install, and what is the difference between mariadb.com and .org? I have not worked on databases before but I am needing to research information about databases for work

I will be using Mariadb

What should I look for when choosing which version to download? It looks like the latest version is 10.4.12-GA, but it is only available for Ubuntu 18.04 (only relevant Ubuntu option in the drop down menu here). I will be using Ubuntu linux in the database server computer, and it looks like the latest Ubuntu release is 20.04. Should I use the latest Ubuntu and latest Mariadb, or would it be better to stick with Ubuntu 18.04 and the latest mariadb? Also, this page says that Mariadb 10.1 is included by default in Ubuntu 18.04. is that the recommended version?

One issue that was brought up during our discussion was the interoperability between Mariadb and MySQL. It looks like after version 5.5 the version numbering between the two seems to have diverged, with MySQL at 8.x and Mariadb at 10.x, with the 10.x version being compatible with the 8.0 version. Is this something I should be concerned about? Our database server computer will only have Mariadb and since we are not upgrading from an existing database nor do we intend to change to some other database in the future, I don't think I need to worry about compatibility with MySQL, but I just wanted some opinions on this.

Lastly, a quick google search brings up mariadb.com and mariadb.org. For example, searching "mariadb download" in google gives me https://mariadb.org/download/ and https://mariadb.com/downloads/ as the top search results. Are both of them the same thing? Which one should I be using?

On downloads, I'd go with the MariaDB.org repository management tool. There is only one 20.04 focal release for the 10.5 Beta branch at the moment (so not recommended yet for production use). The reason is that the releases include what distro builders where available at the time of release so as time goes on there will be a focal repository for every major release.

Until that happens, focal includes a 10.3.22 package version which is a good starting point.

On a normal fresh install I'd pick the latest GA stable (10.4) however since there isn't easy packages for focal yet, stick with the 10.3.22 distro package.

MariaDB.com releases are tarball only installs making upgrading harder.

On MySQL vs MariaDB, there is a large (and dry reading) compatibility page describing differences.

As its a green fields install there isn't a lot to worry about with compatibility. The main thing to look at is if MySQL/MariaDB has the features you need.


Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct? I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the database is too large).

According to Changing Roles Betweeen Primary and Secondary Servers, and Failing Over to a Log Shipping Secondary this all looks possible. I've never done it before and I'm going to be doing it in production this weekend. (I'll be doing some test runs on non prod databases before hand.)

My question: Should I be doing anything else over and above the Microsoft documentation to ensure success? (I'm wondering if anyone else has some gotya's or extra steps they take.) I'm hoping for a reassuring answer such as "Yes this works every time we do it in production and it's cool" but will be very happy also with "No don't do that, make sure you do x/y/and z instead."

Edit: Please don't recommend I use Mirroring as the answer to this question - yes that's a great idea but it doesn't work for us because of licensing costs. We use standard edition (and this only supports Mirroring in synchronous mode). I would like to know if anyone has followed the steps in the MS documentation in prod or if there's a better alternative which an "experienced" dba would use.

For built-in failover / failback functionality, choose DB Mirroring over log shipping:

http://technet.microsoft.com/library/bb934127.aspx

You can follow a process to do this with log shipping, but manual steps are required. Mirroring is the best choice.


How connect by is really working? How is it multiplying rows? I know (on high abstract level) how hierarchies in Oracle works, but this one I do not understand.
When issued following query:

select rownum-1 as num from dual

I've got one row as result.
The following query:

select rownum-1 as num from dual connect by level<=1000

is returning 1000 rows.
What is really happening in background of this query? How Oracle engine is multiplying rows when tries to resolve hierarchy.

If you look at the standard SQL way of constructing a recursive query, you would see something like this:

with t (num) as (
  select rownum-1 num from dual
  union all
  select num+1 from t where num+1 < 10
) select * from t;

SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0

As you can see, it starts with selecting one row from the DUAL table, then recursively applying a UNION ALL to that single row while the limiting condition remains true. The CONNECT BY construct is just hiding this SQL plumbing from you.

If you click on the Execution Plan link in the fiddle above, you'll see that.


SQL Server Backups - A couple of questions We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at streamlining/optimising what happens and would appreciate your comments.

Specifically:

The backup process is taking about 4hrs to update statistics during
the backup. Can we safely disable this process to save time?
We're running low on disk space very regularly and wondering if we should re-jig the process. Currently it creates the backup and then deletes the previous backup and this is what is hogging disk space. Can we safely delete the previous one first and then do the backup?

Any other comments or observations would be very welcome
EDIT: Total size of SQL files on the server is around 35GB. One db is about 25GB in size while the other six share make up the other 10 or so GB.

(1) Yes, I usually have the backup process by itself. I would not be doing much of anything during my backup time if I can. You might have it take the backup and then do the update on stats. As it sounds it seems you are running two jobs (1 for backup, 1 for update stats) at the same time?

(2) Do you copy the backup to tape or other disk storage? If so then I usually clean up the files before I create new backups locally. If not, then if I am scraping for storage space I would consider zipping the backup file prior to the new one being created. (That is if you cannot enable compression on the backups, as @Simon suggested, this would save some space as well.)


Column with Default value as Sum of TIMESTAMP I have a table which has 2 columns ( JobDate , RecordTime)

--------------------------------------
JobDate       | RecordTime |   SumCol
--------------------------------------
2019-07-20    | 2019-07-21 |
2019-07-19    | 2019-07-20 |

I need SumCol to have a default value as UNIX_TIMESTAMP(JobDate) + UNIX_TIMESTAMP(RecordTime)

I've tried creating a virtual column but it gives me this error :

Expression of generated column 'Test2' contains a disallowed function.

that's what I tried:

ALTER TABLE `jobsTemp`
ADD  `SumCol` TIMESTAMP
AS (UNIx_timestamp(`JobDate`) + UNIx_timestamp(`RecordTime`));

If the goal is to order by two columns, then simply do that.

SELECT ...
    ORDER BY JobDate, RecordTime

This will sort the output rows by JobDate, but in the case of duplicates for JobDate, it will use RecordTime.

Performance?  Not a problem, especially if can manage to get the right 'composite' index.  But we need to see the actual query to discuss that.

Note that your initial thought of adding the columns could lead to a different sort order.  Consider simple numbers:

id  JD  RT  JD+RT
 1  14  15    29
 2  14  13    27
 3   6  11    17
 4   7   5    12

SELECT id FROM .. ORDER BY JD+RT   -- 4, 3, 2, 1
SELECT id FROM .. ORDER BY JD,RT   -- 3, 4, 2, 1


Replicate only a few tables in PostgreSQL 10 I have a need to keep updated just a few tables between databases with different purposes and schemas, except for these few tables.

My current solution is a cron job that selects from one database and inserts into the other every minute or so, but I was wondering if there are more robust solutions around for PostgreSQL version 10.

To replicate some specific tables, you can use logical replication

Another option is to not replicate at all and access the remote tables through a foreign table


Setting up MySQL circular replication in existing replication topology? I have the following MySQL replication topology set up for fault tolerance/disaster recovery with master server A and slaves B and C:

A replicates to B

A replicates to C

Both B and C are fully up to date with everything from A and have nothing in addition to that.  i.e. there is nothing in B and C that is not in A.

I would like to introduce circular replication such that:

A replicates to B
B replicates to A

    A replicates to C

Changes made on B would replicate back to A. This would allow me to get more use out of a secondary server by spreading the load over two primary servers.

Primary use of each individual database on the servers would be limited to a specific server.  e.g. Users of database bookingsystem always access via server B, users of database enquirysystem always use server A. There is no danger of having to deal with changes to the same database in two different locations. The replication is purely for fault tolerance should there be a problem with either server.

Can I simply set server A to slave from server B and have this working straight away, or is there further preparatory, or subsequent work needed?

STEP01) Add this to /etc/my.cnf on ServerB

[mysqld]
log-bin=mysql-bin

STEP02) Restart mysql on ServerB without replication running

service mysql restart --skip-slave-start

STEP03) On ServerB, run this in mysql client

SHOW MASTER STATUS;

You should see something like this:

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 |              |                  |
+------------------+-----------+--------------+------------------+

STEP04) Record the Log File and Position from STEP03

STEP05) Restart mysql on ServerB as normal

service mysql restart

STEP06) Setup Replication User on ServerB

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...

STEP07) On ServerA, run the following

CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;

Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'

CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;

STEP08) Start Replication on ServerA by running

START SLAVE;

STEP09) Run SHOW SLAVE STATUS\G on ServerA

It should look something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.89.147
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 636515875
               Relay_Log_File: relay-bin.000038
                Relay_Log_Pos: 636509693
        Relay_Master_Log_File: mysql-bin.000109
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 636515875
              Relay_Log_Space: 636509885
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!

Give it a Try !!!


Oracle Backup procedure I'am trying to set up backup of Oracle DBMS.

Content init.ora file:

log_archive_dest_1='LOCATION=<location>';
log_archive_dest_state_1=ENABLE;
log_archive_format='%t_%s_%r.arc' scope=spfile

I executed the following commands:

alter database archivelog;
alter system archive log start;
alter system archive log current;
alter database create standby controlfile as '<location>';

Then, I copied the created log files (.arc), control file (.ctl) and data files (.dbf) to the replica.

Content init.ora on replica:

log_archive_dest_1='LOCATION=<location>'
log_archive_dest_state_1=ENABLE
log_archive_format=%t_%s_%r.arc
control_files='<location>'
STANDBY_FILE_MANAGEMENT=AUTO
compatible ='11.2.0'

I executed the following commands on the replica:

startup nomount
alter database mount standby database;
recover standby database;
alter database open;

But I get error:

ORA-10458: standby database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: filename.dbf'

Someone with such already faced, can you help?

first of all, if you are trying to build standby database, you may just open database for read only.

alter database open read only;

I would recommend you to check this article for your reference


Why is supplying the hostname sometimes optional and otherwise not in psql? After having used createuser and createdb to create a user and database both called teamcity I wanted to log in. Using both of these commands, I never said anything about which host the database was running on, so I would assume that when using psql it would use the same settings. This is what happens if I do not supply a hostname:
$ psql -U teamcity
psql: FATAL:  Peer authentication failed for user "teamcity"

No password prompt and immediate failing. If I supply -W to force a prompt, the password does not work. I am logged in as a normal unix user, not the postgres user.
Now, if I supply -h localhost everything works as it should:
$ psql -h localhost -U teamcity
Password for user teamcity:
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
psql (10.11 (Debian 10.11-1.pgdg80+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

localhost teamcity@teamcity=> \q

What can explain this behaviour? I assume some of the commands connect to the postgres using unix sockets and some using TCP, but it should be the same database, no matter, right, so why is the authentication failing in some cases?

The default for all Postgres command line tools is to use "local" connections, i.e. unix sockets (unless this is "overruled" by environment variables).
The next step in the authentication process is to look up if and how the user should authenticate - which is defined by the entries in pg_hba.conf.
Most probably the default entry in pg_hba.conf says "all local connections are authenticated using peer" which is the default and is controlled by the following line:
local   all             all                                     peer

So psql -U teamcity tries to use a "local" connection and pg_hba.conf requires peer authentication for the user teamcity - most probably your current Linux user is not named teamcity and thus the authentication fails.
If you add -h localhost to the command line, a "host" connection using TCP is established, which is controlled by a different set of rules in pg_hba.conf.  Maybe our file contains something like:
host    all             all             all                     md5

which allows TCP connection from any client if, with any database user as long as the correct password is provided.
You probably ran createuser and createdb while being logged in as the postgres Linux user, and for that peer authentication succeeds as the Linux username matches the Postgres database username.


Scaling MySQL Cluster I have an NDB cluster which consists of 4 data nodes. Some of the configuration parameters and system resources as :

CPU: 32
RAM: 128GB
Replication factor: 2
Data memory: 70GB
Index memory: 10GB

I am planning to extend cluster capacity. I have two options to achieve this:

I can add more RAM to data nodes so data memory and index memory will be able to double up
I can add 4 data nodes more to cluster so total data memory and index memory increases too

I am not sure which one is a better solution while extending cluster capacity. If I choose first option data size stored per node will be bigger. On the other hand what about 140GB DataMemory per data node? Are there any recommended maximum value for DataMemory ve IndexMemory parameters?

140GB DataMemory is no problem.
So the easiest change is to upgrade memory.
Adding nodes increases both memory and CPU capacity.
But adding nodes also changes the scalability, so could
be both beneficial and negative dependent on the scalability of
your application.


How does 'exists' affect output I previously asked a question about how to organize a long search query.  I have taken it apart.  I am presently attempting to understand the function of some of its parts.  Specifically, I am attempting to determine how an exists statement works. Does it return a single row or multiple rows?

exists(select ID
       From Address
       Join PersonAddress on PersonAddress.Address = Address.ID
       Where
       PersonAddress.Person = Person.ID
       and ((Description LIKE keyword)
         or(Street LIKE KEYWORD)
         or(City LIKE KEYWORD)
         or(State like KEYWORD)
        ))

According to the Microsoft Docs page for the EXISTS operator:

  EXISTS returns TRUE if a subquery contains any rows.

The PostgreSQL documentation page says this about the exists function:

  The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false".

Therefore, an EXISTS statement is used as a boolean expression to determine if some subset of rows exists for a given situation.

Take the following example:

CREATE TABLE table1
(
    key_value int
);

INSERT INTO table1 (key_value)
VALUES (0)
    , (1)
    , (2)
    , (3);

CREATE TABLE table2
(
    key_value int
);

INSERT INTO table2 (key_value)
VALUES (0)
    , (2);

So, table1 contains 4 rows, and table2 contains 2 rows.

This query will show results from table1 where the key_value matches any row in table2 that has the same key_value:

SELECT *
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.key_value = table1.key_value
    );

╔═══════════╗
║ key_value ║
╠═══════════╣
║ 0         ║
║ 2         ║
╚═══════════╝

This is similar to a simple INNER JOIN in that only rows where key_value matches in both tables are returned, however there are several key differences:

The results only show columns contained in table1, whereas an INNER JOIN query like the one below returns columns from both tables.

SELECT *
FROM table1
    INNER JOIN table2
        ON table1.key_value = table2.key_value;

    ╔═══════════╦═══════════╗
    ║ key_value ║ key_value ║
    ╠═══════════╬═══════════╣
    ║ 0         ║ 0         ║
    ║ 2         ║ 2         ║
    ╚═══════════╩═══════════╝

If there are multiple rows in table2 that match a single row in table1, only a single row is returned by the EXISTS query, whereas multiple rows (the cartesian product of matching rows in table1 and table2) would be returned by the INNER JOIN query.  Let's insert another row into table2 so we have two rows where key_value is 2:

INSERT INTO table2 (key_value)
VALUES (2);

SELECT *
FROM table1
    INNER JOIN table2
        ON table1.key_value = table2.key_value;

 ╔═══════════╦═══════════╗
 ║ key_value ║ key_value ║
 ╠═══════════╬═══════════╣
 ║ 0         ║ 0         ║
 ║ 2         ║ 2         ║
 ║ 2         ║ 2         ║
 ╚═══════════╩═══════════╝

The EXISTS version of the query still only returns two rows:

SELECT *
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.key_value = table1.key_value
    );

        ╔═══════════╗
        ║ key_value ║
        ╠═══════════╣
        ║ 0         ║
        ║ 2         ║
        ╚═══════════╝


Extract data for all groups in a single SELECT statement MySQL 5.

Table: schedule
Columns:
id  int(10) UN PK AI
startdate   date
groupid smallint(5) UN

The following statement extracts the Schedule ID for a given group (the schedule with the latest date not greater than the given date):

SELECT id FROM schedule
WHERE groupid=@group AND @date>=startdate
ORDER BY startdate DESC LIMIT 1

Question: Can I extract Schedule IDs for all groups, without enumerating (calling the above statement repeatedly) with all existing group IDs?

This works, provided the combination groupid - startdate is unique:

SELECT s1.groupid, s1.id FROM schedule s1, (
  SELECT groupid, MAX(startdate) startdate
  FROM schedule
  WHERE @date >= startdate
  GROUP BY groupid
) s2
WHERE s1.groupid = s2.groupid
AND s1.startdate = s2.startdate