Improve speed of index rebuild on SQL server I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import.

Now I want to re-enable the indexes, and I'm wondering if there is anything that I can do to optimize this.

There are > 100 tables and almost 2,000 indexes to be rebuilt.  The database is 200GB in size.

The key section of the script I'm running is this:

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

I considered setting ONLINE=OFF for the alter index statement, but as the indexes start out disabled I wasn't sure that this setting would have any effect.  I also considered setting SORT_IN_TEMPDB = ON, but as the tempdb files are on the same drive as the .mdf files of the databases I assumed that there was also no benefit to doing that.

Whilst running the rebuild script I have noticed that I have a lot of CXPACKET wait types.  I don't really understand why that would be or if it's a problem that I should be looking to address.

One final point that may be relevant: my entire server is currently inactive other than this import of data into the database.  There is no other user activity to consider or worry about; my only concern is importing the data into the database in the shortest possible time.

Achieving optimal import performance in this scenario requires three things:

Minimally-logged base table inserts
Minimally-logged nonclustered index builds
Avoiding physical reads

Minimal Logging

Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires:

Using either the SIMPLE or BULK_LOGGED database recovery models
Specifying a table lock and ordered input (e.g. TABLOCK and ORDER hints)

Side note:

  It is also possible to achieve minimally-logged inserts to a clustered
  table that has nonclustered indexes provided trace flag 610 is
  enabled. Whether nonclustered index inserts are minimally-logged or
  not depends on the query plan selected by the query optimizer.

  If the query plan uses a separate iterator for the nonclustered index,
  and the iterator has the DMLRequestSort property set to true, the nonclustered index inserts will be minimally logged, provided the
  other conditions mentioned previously are met.

Building nonclustered indexes separately

The advantages of doing this are:

Clustered index inserts can be minimally-logged without enabling TF 610
CREATE INDEX is minimally logged if the recovery model is not FULL

Avoiding physical reads

Ideally, the data to be imported will be stored on a separate machine, or at least on separate physical storage from that used to host the database.

The database server should have enough memory to hold the largest base table in cache, with enough left over for sorting operations necessary when building nonclustered indexes.

A good pattern is to fast-load the base table (minimally-logged clustered index load) and then to build all nonclustered indexes for that table while its data pages are still cached.

The question outlines a process whereby base tables are loaded first, and then nonclustered indexes built. The cursor definition does not use an ORDER BY clause to at least group nonclustered index builds on the same table together.

The likely result is that data pages for different tables are repeatedly read into cache and then discarded as nonclustered indexes are built in a non-deterministic order.

The cost of repeated physical reads completely dominates the benefits of minimal logging gained by building nonclustered indexes separately. This explains why you found that loading tables with existing indexes is faster (because all nonclustered indexes for a given table are maintained before moving on to the next table).

Summary

The import process should be reworked to bulk-load one table at a time. This means loading the table and building all nonclustered indexes before moving on to the next one. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time.

You could also try enabling TF 610 before loading the data into tables with nonclustered indexes already in place. This is not usually as fast as the previous method, but it may be fast enough.

See the following for more information:

The Data Loading Performance Guide

Operations That Can Be Minimally Logged


Word for describing how normal (technical term) a database is Database Normalization is the process of changing the structure of a formal database schema in order to minimize redundancy.

Database theory describes some states of normalization in order to make promises about the nature of queries on them - these are called Normal Forms, often abbreviated 1NF, 2NF, 3NF (and less commonly, 4NF, 5NF, 6NF).

I'm looking for a correct (grammatically and technically) term for describing how normal a database is - something like:

Implications on Database Normality

I've only ever heard "normalized" and "denormalized".  "Sorry, you'll need to update the data in a bunch of places; it's kind of denormalized."  So you might hear that a database is "fully normalized", "somewhat denormalized", or "extensively denormalized".


Yes, this is expected behaviour when you are using the non-standard MySQL "feature" of unorthodox GROUP BY. See this related post at SO: Why does MySQL add a feature that conflicts with SQL standards?

Your query basically says to the engine that you want the maximum date and arbitrary values for the other columns. And MySQL does that, exactly. It gets you the maximum date and arbitrary values for the other columns. You should consider yourself lucky that the values came form the same row. They might have been from different rows as well!

Now, to solve your issue, don't use this feature - unless you know very well what you are doing!. Here is another way to get the expected results. Don't use MAX() in the SELECT - which does an implicit GROUP BY the whole set - but do an ORDER BY date DESC and then LIMIT 1 to get only the row with the highest date:

SELECT mod_coli_id, line_number,total_ cost,total_sell, modify_date
FROM mod_customer_order_line_item
WHERE customer_order_id = 17761
  AND modify_date <= '2016-09-14'
ORDER BY modify_date DESC
LIMIT 1 ;


PostgreSQL: result of range difference would not be contiguous How to substract a smaller subrange from a bigger range?

123456789
    567

Result:

1234   89

SQL:

select '[1,9]'::int4range - '[5,7]'::int4range;
ERROR:  result of range difference would not be contiguous

The result does not fit into a int4range since it is not contiguous.

How to get the result as a set of int4ranges?

You could define your own functions range_add/range_sub (intersection doesn't need special handling):

select range_add('[1,5)'::int4range, '[8,10)'::int4range);
     range_add
--------------------
 {"[1,5)","[8,10)"}

select range_sub('[1,10)'::int4range, '[5,8)'::int4range);
     range_sub
--------------------
 {"[1,5)","[8,10)"}

You could define them like this:

create or replace function range_add(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 && $2
            then array[$1 + $2]
            else array[$1, $2]
        end
$$
language SQL;

create or replace function range_sub(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 @> $2 and not isempty($2) and lower($1) <> lower($2) and upper($2) <> upper($1)
            then array[int4range(lower($1), lower($2), '[)'), int4range(upper($2), upper($1), '[)')]
            else array[$1 - $2]
        end
$$
language SQL;

Unfortunately, PostgreSQL is missing native support for this set-based behavior.


Insert missing time interval rows I have a sql server  table with meter values which are read every minute each day which should give me 1440 records per meter per day. Sometimes the data doesn't come in every minute and as such a row can be missed. How would I insert a row with a 0 or null value to complete the intervals.

Example

Datetimecolum        kWh
2017-01-01 12.00 AM  64.00
2017-01-01 12.01 AM  0.02
2017-01-01 12.03 AM  2.32

I would like to populate a row for 12.02 AM with 0 or null value to indicate the reading was not received. Any help would be appreciated.

Thanks

A quick one using Itzik ben gan tally table.

DECLARE @date datetime='20180327 00:00'

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT TOP (1440) dateadd(minute,n,@date) n
FROM Tally

You can left join this with you meter reads table.


How to enable multiple traceflags I want to enable trace flags 4199, 8024, 9024, 1236 & 1117 on SQL SERVER (Startup Parameters).

I went to SQL Server Configuration Manager -> SQL Server ("instance_name"), and then clicked Properties -> Startup Parameters tab -> and I  added -T4199 -T8024 -T9024 -T1236 -T1117 separately.

This is a SQL Server 2016 instance.

Try add each trace flag separately.

Some trace flags are now build in, in SQL Server 2016 like TF 1117, TF 1236 only applies to SQL 2012 and 2014 without SP1, TF 4199 is now default as is TF 8024.

For the complete list of trace flags go to this url: https://msdn.microsoft.com/en-us/library/ms188396.aspx


How to track if employees are late which has more than one work schedules or shifts? I have designed a MySQL database for an employee monitoring system, it was working perfectly well until I was asked to put a new work schedule for some of the employees.

The new schedule was what they called a broken schedule because an employee would have different shifts in one day, for example, an employee could be given a work schedule for a given day starting from 7:00AM-11:00AM and then continues at 3:00PM-7:00PM...

The problem I encountered now is, I am confused on how to keep track if the employees are late of not now that some employees have two or more work schedules...

Could anyone help me pls?

addendum:

for more clarity, lets say we have employee A. employee A for example has the following work schedule for a given day - 5 days in a week.
        7:00AM-11:00AM - shift 1
        3:00PM-7:00PM  - shift 2
        9:00PM-11:30PM - shift 3

then, lets say employee A logged in at these different scenarios:
scenario #1: logged in at 7:10AM
scenario #2: logged in at 2:30PM

how do I check that employee A's log info at scenario #1 is not referencing shift2 or shift3?
likewise, how do I check if employee A's log info at scenario #2 would only reference shift2 only and would not make him/her late at his/her schedule at shift #1 or too early at shift #3?

If you absolutely cannot add a column to your schedule or employee to reference the shift in question explicitly, you could make assumptions regarding the length of time between a log in and a shift start to determine this.


Using SQLite ODBC without installing a driver I am currently trying to access a SQLite database via an ODBC connection but the system that I am working on is extremely locked down and it is therefore not possible to install the drivers. Is there any way to bypass this?

ODBC as just an interface between applications and drivers.
It is impossible to create an ODBC connection without using some installed driver.


SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates.

We have a large table partitioned by date with a couple of billion rows. The partition date is the prior business date and so is an ascending key. We only load data into this table for the prior day.

The data load runs overnight, so on Friday 8th April we loaded data for the 7th.

After each run we update statistics, although take a sample, rather than a FULLSCAN.

Maybe I am being naïve, but I would have expected SQL Server identify the highest key and lowest key in the range to ensure it got an accurate range sample. According to this article:

  For the first bucket, the lower boundary is the smallest value of the column on which the histogram is generated.

However, it doesn't mention the last bucket/largest value.

With the sampled Statistics update on the morning of the 8th, the sample missed the highest value in the table (the 7th).

As we do a lot of querying on data from the prior day, this resulted in inaccurate cardinality estimation and a number of queries timing out.

Should SQL Server not identify the highest value for that key and use that as the maximum RANGE_HI_KEY? Or is this just one of the limits of update without using FULLSCAN?

Version SQL Server 2012 SP2-CU7. We cannot currently upgrade due to a change in OPENQUERY behaviour in SP3 that was rounding down numbers in a linked server query between SQL Server and Oracle.

Should SQL Server not identify the highest value for that key and use that as the maximum RANGE_HI_KEY? Or is this just one of the limits of update without using FULLSCAN?

It's a limitation of the current implementation of sampled statistics. As it stands, sampled statistics collection uses TABLESAMPLE SYSTEM, which uses an allocation-order scan and chooses pages from the scan to sample. Only chosen pages contribute to the histogram.

Since the scan is allocation-ordered (rather than index-ordered), there is no way to give preference to the first and last pages in key order.

For more information see this related question:

How does sampling work when updating statistics?

and my article, Allocation Order Scans

For workarounds, see Statistics on Ascending Columns by Fabiano Amorim


Adding a primary key to an existing table in mysql is taking time 22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column.

In showproceslist its process status is "copy to tmp table". Can anyone tell me if this is the correct behaviour and how long it will take to complete?

Yes, creating a PK on a big table takes time.

When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places.

When you have a clustered index (PK in MySQL), the PK key will be the row's identifier and all indexes are using the clustered key to identify records.

When you modify the type of the table (heap to clustered), all indexes and the DBMS should rebuild the table and reorganize the data stored in it.

Best practices:

Drop ALL nonclustered indexes before you change (or drop/create) the clustered key
Stop all processes which are using that table to prevent deadlocks and long wainting (do it in your maintenance window)
If you want to change (or drop/create) the clustered key on a huge table, create a new table with the required primary ky (clustered index), then copy all data from the original table, then replace the new table with the old by renaming. When this done, you can add additional indexes to the table.

  how long it will take to complete?

It depends on the server and the processes runing next to the alter on the server. In short, we simply can not say a number how long it will take.


Problem sending an attachment using dbo.sp_send_dbmail I am trying to send an attachment using dbo.sp_send_dbmail.
I can't do this
I get the error
Attachment file \VMName\ListTEAM\LCPP_CP.pdf is invalid.

Just some quick things that I have checked.
I have tried various naming conventions.
I have shared the folder that the attachment is in.
I checked the Log On As in the Services MMC on the VM that the SQL database sits on.
The Log On As for SQL SERVER and SQL SERVER AGENT is SQLReportingAdmin@lan.domain.com
I have given this user SQLReportingAdmin@lan.domain.com access to the directory that the file that I want to send as an attachment is in.
I have triple checked the accuracy of the locations.
The user SQLReportingAdmin@lan.domain.com now has read and execute permissions on C$ on the relevant VM.

The file LCPP_CP.pdf is 72 KB.

The file LCPP_CP.pdf is not read only
The profile is public

I can send an email without an attachment.
The send_request user in dbo.sysmail_mailitems is domain\SQLReportingAgent

My questions are

Is there a stored procedure that I can use to check the SQL Engine logged on user?
Is it possible that msdb.dbo.sp_send_dbmail is not running under the SQL SERVER  account?
If so is there a stored procedure that I can use to check what user msdb.dbo.sp_send_dbmail is using
Is it possible that this isn't a permissions issue?

My code is below but it is really just boilerplate; the commented lines are the various permutations that I have tried.

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'valid@emailaddress.com',

@body= 'Test Email Body',

@subject = 'Test Email 18:01',

@profile_name = 'Team Meeting Profile'
--@file_attachments ='\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\ListTEAM\LCPP_CP.pdf'

You can check the database mail log by going to management -> right-click database mail -> select view database mail log.  Any errors should be in here, for example:

  Message
  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-05-16T02:21:47). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).
  )

Also, you could fire up SQL Server Profiler to check the user for sp_send_dbmail.  I think it uses the current user account to send the mail, so I would also confirm your windows account has permissions to the file.


Netezza Error Record size 120000 exceeds internal limit of 65535 bytes I have a column PARAM that has JSON data that looks like this:

JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information","SessionId":"ab04f2c4-ddge-4555-c792-01d9518e7148"}

I would like to have multiple columns (Name, StepName, StepData, SessionID) with their data in them.

SELECT substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21) as Name, substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) as StepName FROM Table

That query won't work exactly right since I have changed the names but I think you get the picture. When I run the query for either of these columns I get the data I am looking for. But when they are run together as I have above, I get the error:

ERROR [HY000] ERROR:  Record size 120000 exceeds internal limit of 65535 bytes

It feels like this means that I shouldn't assign the text of PARAM as the column name but that isn't what I am trying to do. Any suggestions or clarifications needed?

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name,
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17)
  as varchar (100)) as StepName
FROM Table


Running an SSIS package owned by a domain user from SQL Server running on a local service account I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the environment looks like this:

Domain

Server 1

SQL Server running on local account
On filesystem: SSIS package
In SQL Server Agent: a job

Server 2

SQL Server running on local account

To be able to log on to both servers, I've created a domain account to be used as service account. When I use this domain account to log on to Server 1, and then execute the package from file system, every step succeeds. However, when I try to add the job to SQL Server I run into one of the following problems:

Situation 1. Job owner: local account; run SSIS step as proxy to the domain account. When I set the job owner to a local account, but run the job as a proxy to the domain account, the job itself will successfully execute, but the package throws errors like

  Execution failed with the following error: "The directory
  'LocalApplicationData' does not exist.".

This error can be fixed by creating a login with Administrator-rights for the domain user on Server 1, but this is obviously not a desirable solution. Adding the account to one of the SQL Server agent / DTS groups doesn't work either.

Situation 2. Job owner: domain account; run SSIS step as a proxy to the domain account. When I set both the job owner and the 'run as user' for the step to the domain account, the job won't start at all, with the following error:

  Unable to determine if the owner (Domain\Domain user) of job Job
  name has server access (reason: Could not obtain information about
  Windows NT group/user 'Domain\Domain user', error code 0x5. [SQLSTATE
  42000] (Error 15404)).

I believe the last error is because SQL Server runs on a local account and therfor cannot look at which rights domain accounts have.

What is the right way to make the job run? Situation 2 feels cleaner to me, but seems impossible because SQL Server runs on a local account. Situation 1 would work too, but giving a domain user administrative rights on my SQL Server is not going to happen.

UPDATE:

@JonSeigel and @Mr.Brownstone:

It seems plausible that this is issue is because of the lack of permissions. However, the error is about the non-existence 'LocalApplicationData' - one of the folders which is generated for each account. I've already logged in to the server with the credentials under which the package runs, (hereby creating a profile directory) and tried several combinations of permissions for the profile directory. Even when manually granting almost all permissions on this specific directory, I get the error mentioned above.

While doing some more research, I ran into a forum thread at http://www.sqlservercentral.com/Forums/Topic391332-148-1.aspx#bm391441 which is quite similar - without solution either though.

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.


Keep local variable after rollback in mysql I wish to keep local variable from stored procedure even if rollback occurres.

In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments. When I call the procedure with user_id = 1, value_from = 10, value_to = 20, it registered numbers 10, 11, ..., 19, 20 to user 1. Every number can be registered only once and only to one user.

There are two possible conflicts: I try to register number, which is already registered to another user, or I try to register number, which is already mine.

I wish to determine, which of these options occurred and store that value.

Here is my procedure, which takes also OUT parameter, which describes the result of the operation

-- create table
CREATE TABLE test
(
    value INT NOT NULL UNIQUE,
    user_id INT NOT NULL
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;
            ROLLBACK;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END$$
DELIMITER ;

The problem is, that ROLLBACK not only throws away all new records in table test, it also resets the value of @retval.

Is there any way to keep the custom error message even in case of ROLLBACK

-- test - should return OK - work fine
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 10, 20, @retval);
SELECT @retval;

-- test - should return 'ERROR_ANOTHER_USER' - throws error and resets @retval, should
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 1, 30, @retval);
SELECT @retval;

-- test - should return 'ERROR_ALREADY_HAS' - throws error and resets @retval
SET @retval := 'UNKNOWN';
CALL sp_insert_values(2, 15, 30, @retval);
SELECT @retval;

It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want.  Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block :

CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
proc_body:
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;

            ROLLBACK;
            LEAVE proc_body;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END proc_body;


Max DATAFILE size in NDB engine My database setup is using NDB cluster with 2-3 nodes, on CentOS. While trying to test out the performance, i tried to store ~7mil records (around 2GB size) into one of the table. at around ~200k, it failed with notification:

ERROR 1114 (HY000): The table 'my_request' is full

These are the LOGFILE GROUP and TABLESPACE:

CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.log'
    ENGINE NDBCLUSTER;

ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_2.log'
    ENGINE NDBCLUSTER;

CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_2.dat'
    ENGINE NDBCLUSTER;

Suspecting the problem lay in DATAFILE, i added 2 more into it: data_3.dat and data_4.dat, which solve the problem.

So according to this page, AUTOEXTEND_SIZE and MAX_SIZE are ignored in my current version. That table is expected to hold 700GB+ data before getting dumped into disk after pre-determined time. Therefore, Are there any way to setup the size of this DATADISK to calculate the future usage of it? Where is this data stored? (I tried to check in datadir, exec find, but found nothing). Would be really grateful if you can share some good information/strategy which explain how to work with NDB.

Note: Not an expert in NDB, please assume i know nothing when explaining. big ty.

To get undo/data file size:

SELECT TABLESPACE_NAME, LOGFILE_GROUP_NAME, FILE_NAME, FILE_TYPE, EXTRA,
INITIAL_SIZE/1000000 size_init_mb,
MAXIMUM_SIZE/1000000 size_max_mb,
TOTAL_EXTENTS - FREE_EXTENTS AS extents_used,
(TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE/1000000 AS mb_used,
FREE_EXTENTS * EXTENT_SIZE/1000000 AS mb_free
FROM INFORMATION_SCHEMA.FILES WHERE EXTRA IS NOT NULL;

To change MAX_SIZE of datafile, all it need is to set the INITIAL_SIZE

ALTER TABLESPACE ts_1
ADD DATAFILE 'data_4.dat'
INITIAL_SIZE 1G
ENGINE=NDBCLUSTER;

Even though the documentations are pointing that this datafile can be located by checking the datadir in config, but somehow i can't find it in my system (by default it is at /var/lib/mysql/).


Setting the right Max Server Memory value on an Active/Active SQL Server Clustered Environment I have looked around in the dba stack exchange and have not being able to find an answer to this one, so here it goes my concern. Hopefully somebody has done some research on it and can provide an authoritative answer.

We have an Active/Active SQL Server cluster running on 2 nodes. SQLA is the default node for one instance with the other (SQLB) as a fail-over and viceversa (SQLB default node for the other instance with SQLA as a fail-over). These servers are running on physical boxes with 64GB of RAM

We have to configure "Max Server Memory" and there are 2 theories around here

First Theory: Follow Jonathan Kehayias pretty much universally accepted approach that, in a nutshell, goes like this "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM." This will leave each box with a Max Server Memory setting of 53 GB
Second Theory: We need to cap Max Server Memory to a value that will allow for the secondary node to take care of the other instance if a failover occurs. This limits each instance to much smaller value (around 30 GB so you have 30 GB x 2 + 4GB for the OS = 64GB)

On a first look, the first theory would be desireable as it gives each instance a way larger memory space, but some people (me included) are not sure what happens if you fail over and then you have 2 instances on the same box, each with a Max Server Memory of 53 GB of RAM.

Do you know what is the way to go with this scenario? is any of these two approaches right? or do you have valid reasons that a third approach should be used instead? do you know of any serious blog-post or white-paper to back it up with evidence?

Thank you all!

Please note this is called multi instance cluster not active active actually. Your concern is correct about what would happen when both nodes are on same node, in that case you need a dynamic script would identify a failover and adjust SQL Server max server memory accordingly.

Let us focus on how to set max server memory correctly when both nodes are running on respective nodes for that please see my answer on SE thread What is Sensible way to calculate max server memory.

Now for scenario when failover happens and both instances are on same node. You have to use scripts from follwoing resources.

Use PowerShell Script via startup Agent Job to balance Memory between two instances on a cluster on a Failover
A similar thread talking about how to adjust memory for Multi Instance cluster

Now when both instances are on same node you would have to evenly distribute memory leaving enough for OS. In that scenario 29 G for each instance and 6 G for OS is what I would say would be good value. Because you have 2 instances running leaving few more gigs for OS would actually help.

Also note you should immediately come out of this situation as both SQL Server would be extremely slow.


Lookup table naming convention? Is there a naming convention for lookup tables?
I cannot see any declaration by oracle or anything consistent on google.

However I assume some of you professional DBAs follow a convention?
Perhaps there is a convention which occurs the most when you are called to edit a project you didn't author?

example table

metals

+----+------------+
| id | metal_type |
+----+------------+
| 1  |    gold    |
| 2  |    silver  |
| 3  |    bronze  |
| 4  |    brass   |
+----+------------+

Currently I have been using the original table name + the column name as my lookup table name.

metals_metal_type

I decided to change to LK_ + original table + column as it allows easier navigation when browsing all tables.

LK_metals_metal_type

What are your views on this?

Thanks

Technically no there isn't a standard for table naming conventions. As long as you use something consistent across your environment there shouldn't be a problem. It should also be well documented as to why you are using this prefix.

That being said however here are few rules that I follow. IAlwaysBookEndMyTables the reason for doing this is it make it clear to read without any spaces or underscores. The reason I don't use underscores is that I'm a SQL server guy and they can be used as a wild card. When naming a lookup table I've typically used LU. Once again this doesn't matter as long as you are doing things consistently. LKMetalsMetalType isn't really any different from LUMetalsMetalType.

One other thing be as descriptive with your table names as possible. Whoever picks up after you leave a project should be able to look at your table structure and have a good idea what's going on. There's plenty of room for a long table name but very limited sanity in trying to figure out what the heck the Pl_uva table means. FYI that was just an example.

I shouldn't have to say this and am assuming you already know but I'll say it anyway. Do not use reserved words or include spaces in your table names. I swear to god I saw a guy on Stack Overflow yesterday who had named a column "from" so it was literally.

SELECT
    [FROM]
FROM HisTable


Linking first and second level composite primary keys I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not match:

Here a simplified Structure of the sql query:

CREATE TABLE a(
    a_ID serial PRIMARY KEY,
    a_name char(100)
);

CREATE TABLE b(
    b_ID serial PRIMARY KEY,
    b_name char(100)
);

CREATE TABLE c(
    c_ID serial PRIMARY KEY,
    c_name char(250),
);

-- First refering table
CREATE TABLE b_c(
    b_ID INT references b ON UPDATE CASCADE,
    c_ID INT references c ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID)
);
-- Second higher level combining
CREATE TABLE full(
    b_c_ID INT references b_c ON UPDATE CASCADE,
    a_ID INT references a ON UPDATE CASCADE,
    PRIMARY KEY (b_c_ID, a_ID)
);

The creation of the last table fails.
Am I missing something here or is it impossible to refer to a composite primary key in just another composition?
Any comments?

You need 2 columns in the foreign key constraint, to refer to a table with  2 columns in the primary key:

-- Second higher level combining
CREATE TABLE full_b_c(
    b_ID INT NOT NULL,
    c_ID INT NOT NULL,
    a_ID INT NOT NULL references a ON UPDATE CASCADE,
    PRIMARY KEY (b_ID, c_ID, a_ID),
    FOREIGN KEY (b_ID, c_ID)
      references b_c (b_ID, c_ID) ON UPDATE CASCADE
);


Database Design for a Graph (Network Topology) Is there a recommended pattern for modeling a graph in a relational database?

My presumption is that you'd have the following three tables:

Graphs
------
GraphID, PK
GraphName

Each record in the Graphs table represents a single graph you are modeling.

Nodes
-----
NodeID, PK
GraphID, FK
NodeName, string
...

Each record in the Nodes table represents a node in GraphID, specifying attributes specific to that node.

Edges
-----
EdgeID, PK
GraphID, FK
FromNodeID, FK
ToNodeID, FK
EdgeName, string
...

Each record in the Edges table represents an edge in GraphID, specifying the Nodes it connects and the attributes of an edge (name, distance, etc.).

What I'm having a hard time wrapping my head around is what if there are different types of nodes, where each type of a node has some attributes in common (say 5 or so), but has many unique attributes (say 10-30)?

For simplicity's sake, imagine there are two types of nodes to capture: A and B. How do I model this? I've seen one option being to have a "general" Nodes table:

Nodes
-----
NodeID, PK
GraphID, FK
NodeName, string
AnotherSharedAttribute, string
...

And then a detailed table for node type A and node type B:

NodesA
------
NodeID, PK
UniqueToA1, string
UniqueToA2, string
...

NodesB
------
NodeID, PK
UniqueToB1, string
UniqueToB2, string
...

Am I understanding this approach correctly? Because what flummoxes me is how do I query this efficiently? It seems to me that to determine the attributes for a particular node, I have to check all possible node type tables (NodesA and NodesB in this case) for a matching NodeID. This might not be a big deal with just two different types of nodes, but in my project there are ~30 different types of nodes and 5 different types of edges.

Thanks

There is no great way for modeling a graph in a relational database. They are two different data models. One says data "is really" sets of tuples. The other says it "is really" nodes connected by edges. These are different. Although any real-world scenario can be represented in either model the affordances of each are different. If the use-case is to perform an operation on multiple items that conform to a predicate (e.g. update .. where..) the relational model will, generally speaking, be more convenient. For use-cases involving following connections between items ("people who bought .. also bought ..", "you and Andi have 17 friends in common") the graph model is likely to work better. A relational DBMS makes implementing the former easy and the latter more difficult, and vice versa for a graph DBMS.

There are a few well-trodden relational implementations for the specific sub-set of graphs that are trees.

When implementing a solution in a RDBMS we only have tables to work with. So one has to decide which tables are needed to implement a generalized graph. Are the items of interest in the problem domain simply "things" which can be modeled as a single Node table and all the values of interest in a NodeAttribute table? This is analogous to an EAV, but in a graphy kind of way. Generally this is not useful unless we are concerned purely with the connectivity of nodes.

Usually we want to group things into classes and apply different constraints to each class. We could add a Class attribute to the Node table. This still leaves all the hard work of filtering rows of specific Class, and navigating between classes, to user code.

Relational databases are designed to have one table per class. If we have multiple classes of nodes then each should have its own table. If, as in your question, there are sub-classes of nodes then the problem can be restated as "how to model sub-classes in a relational way?" This is now a problem of table inheritance, about which quite a lot has been written. Some DBMS implement this natively; SQL Server does not.

  how do I query this efficiently?

The same way you query any other relational database. Good indexes that support the queries. Understanding the implementation and referencing only those objects required to satisfy the query's needs. If you have 30 types of nodes and 5 types of edges it is unlikely that all will be required in every query.

The discussion above for node tables applies equally for edge tables.

There are hybrid DBMS which allow the representation of data in both graph and relational ways. Starting with 2017 SQL Server introduced graph extensions. Sadly the MATCH clause was little more than syntactic sugar on SQL-89 joins redux. No graph-like affordances were "supported in the first release", though the language was encouraging.

With SQL Server 2019 transitive closure is now supported. One can only hope polymorphism is not far behind.


VMWare Memory Sizing and SQL Server 2014 We have a production server that dumps it's Page Life Expectancy many times a day (climbs to 2500s and falls back to near 0s)... After watching some training videos about virtualization for SQL Server, I was pointed towards several Perfmon counters to look at:

Memory Limit in MB
Memory Ballooned in MB
Memory Reservation in MB
Memory Swapped in MB

Ballooned, Reserved, and Swapped are all at 0 but Memory Limit was set to nearly 3tb (perhaps a default setting like SQL's Max Memory setting...)

I viewed these counters during an event when the PLE dropped to 0 and none of the counters changed. According to Spotlight, Buffer Cache and Procedure Cache are remaining at high % utilization and no stats seemed to really budge except the PLE itself.

To me, this points to not enough RAM allocated to the machine to the point that many of the queries are forcing the server to look to disk to get results.

What steps do I need to take to prove or refute this?

Server Config:
- Windows 2008 r2 VM
- 4 cores
- 12GB RAM
- main DB is ~150GB

Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.)

However, they don't help size the VM.

If you're having performance problems, you'll want to identify two things:

The SQL Server's top wait during that time, and
The queries causing that wait

To get the top wait, try running sp_BlitzFirst @SinceStartup =1. (Disclaimer: that's an open source script, and I'm one of the authors.) You can also use 3rd party monitoring tools like Idera SQL DM, Quest Spotlight, and SQL Sentry Performance Advisor. (Disclaimer: those cost money, and I didn't write any of those, but they're awesome.)

The @SinceStartup = 1 switch gives you waits since startup, which isn't as cool as monitoring software, but it's a free start.

If your top wait is PAGEIOLATCH, that means reading data pages from a data file. If that's the case, you want to find the queries reading the most data. For that, use sp_BlitzCache @SortOrder = 'reads' (again, disclaimer, open source script, and I'm one of the authors.)

That'll give you the top 10 queries ordered by how much data they're reading. They're usually candidates for index tuning or query tuning. I'd always rather tune those rather than add memory, but if PAGEIOLATCH is your top wait, and you're not allowed to tune those top 10 queries, nor tune their indexes, then the next fix is to add memory to the VM. (But only after going down this route of troubleshooting.)


SQL Server and TFS - How to rename stored procedures After a while one may wish to do some light refactoring, such as giving more appropriate names to stored procedures as their functionalities are better established.

For the life of me, I simply cannot rename anything in SQL in a multi-branch TFS environment without it being an onerous, manual process.

Without using some third-party tool, how are you supposed to do it?

P.S. This is just for renaming stuff in a way that doesn't require deployment scripting to migrate anything. That is its own giant can of worms.

When I try to rename a SQL entity (e.g. a table, stored procedure), I use the Visual Studio Find and Replace in Files utility and then rename the file, since the super-useful refactor tools that I use all day for C# do not exist for SQL.

After enabling the advanced SSDT Publish Option to drop objects in target but not in source and manually verifying everything works (since the validations done by the build and the publish are severely lacking) and convincing myself my rename was implemented correctly, I now have to merge changes to a couple other branches in TFS.

Apparently TFS Power Tools Migrate of rename is bugged (but since Microsoft retired Connect, I can't follow up on that post) because all my renames are now showing as ADD changes.

After undoing all those erroneous pending changes, the files are leftover on disk, so manually renaming the files throws file already existing errors. Upon deleting those orphaned files, I am back where I started with no automated tools to help, having to repeat all the changes in every target branch and having to manually detect then merge conflicts.

To add insult to injury, my %AppData%\Local\Microsoft\VisualStudio\14.0\ComponentModelCache folder keeps getting corrupted for some unknown reason (which persists through a complete repair reinstall) such that renaming files only under *.sqlproj throws a null reference error at me about a parameter name 'o' upon which the file is renamed on disk but not in the solution which then corrupts my TFS workspace.

I don't know what is wrong with me, but it seems that renaming SQL entities is unofficially blocked by Microsoft in every possible devious way.

P.S. There is no "rename" tag to put on this post, further suggesting that renaming stuff is not something people ever do in databases.

This question isn't so much about how to RENAME a stored procedure, as it is asking about how to MERGE the RENAME to other branches in TFS.

As pointed out by Dan Guzman in the comments, the Refactor > Rename feature while editing a SQL file should be used:

(note that this is not an option if you right-click the file; you have to right click the entity name in the file (or have the caret on the name and use the shortcut chord))

The hard part is now merging the rename between branches.

Using either the MERGE command from Source Control Explorer or the unshelve /migrate command to TFS Power Tools (tfpt.exe), the pending changes are generated as a DELETE and an ADD which deletes the link to the TFS history!

Furthermore, your .sqlproj file will be a giant headache to merge conflicts this way.

Instead, the renamed files should be manually renamed in the target branch, then the TFS Power Tools unshelve /migrate command will just require merging the file contents as normal, with the .sqlproj file already having the changes made to it (so you can choose the Keep target branch version option on it, presuming you don't have other ADD or DELETE changes additional to the RENAME changes).

There seems to be another method using labels, but the gated check-in process is a contraindication to it.

Note that if you start getting this error message renaming files, immediately restart Visual Studio and manually fix the .sqlproj file in a text editor as the file was renamed on disk but not in the project file:

To recap, this is the process I have found to work the best:

Manually perform all the renames in the target branch
Ensure changes in the source branch are in a shelfset
Use the TFS Power Tools unshelve /migrate command
Verify. Verify. Verify. And also verify by using this advanced publish option:

I hope this helps somebody, as this process has been a serious headache for me because I have to merge to multiple branches, and my changeset is very large.


Stored procedure execution time went from under 2mins to "infinity" without modifications I have an SSRS report that pulls it's source from a vendor's MSSQL 2012 database. It's an extensive dataset so I've created a stored procedure that runs at the same time every morning.

There's quite a bit happening in that stored proc, but I had optimized it to run in about a minute and a half. It did so for the last 3 months without any issues until this morning. When I came in and checked the logs, I saw that the sp was still running (2 hours later). I waited a bit, then stopped it thinking it might just be hanging.

I tried executing it again and it's been running for over an hour again.

The only thing I can think of that changed was an update that the vendor applied to the database yesterday.

I tried to run a query to see if there were any locks on the tables that I'm pulling data from, but I don't have sufficient permissions. I also ran sp_who2 to get some info as I read on here that it might help out, but all I've learnt is the following:

Status = RUNNABLE | BlkBy = . | Command = SELECT INTO | CPUTime = 47 | DiskIO = 0

I'll be contacting the database administrator to troubleshoot, but I'd like to get an idea of what I can suggest, since the hourly rate for support services is very expensive and they often try to extend the call beyond what is necessary.

Is there something to look at right away when a stored procedure suddenly becomes inefficient overnight?

I received a follow-up from the database administrator. So I will post his message as the answer.

I tried again after receiving his message and sure enough the stored procedure ran in under 2 minutes.

His email:

  Seems that the issue was a deadlock on the db. I killed the locked processes and it seems fine now.

  The system update essentially caused rehashing in some of the tables and the replication job to your datamart hung up because of this.

  Please try again and let me know.

Edit: Additional Information

If you have the same issue, please also consider the comment from @Kin below since I am unable to confirm 100% that the DBA actually did what he said.

@Kin: Well, SQL Server will automatically choose the deadlock victim based on the log usage (the process using less log will be killed unless you have deadlock priority set). I think, it is a blocking issue not a deadlock as thats where you kill the offending process.


Building an execution plan takes too long on SQL Server I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and entire execution takes about 1 minute.
But on SQL Server it takes about 40 minutes. I tried to look at the execution plan and I started to wait... I tried to get the execution plan by executing a query from an application session, but there were no execution plan.
Then I got the query and asked in SQL Server Management Studio for "Display estimated execution plan", and I also started to wait. So, it looks like it takes too much time just to build the execution plan. All statistics is gathered with "exec sp_updatestats", and I checked it in sys.stats - everything looks fine. All indexes are in place.
I commented all joins and started to uncomment them one by one, and
  SET STATISTICS TIME ON

shows that with every uncommented join it takes a longer time to parse, e.g. timing for 13 joins:
SQL Server parse and compile time:
   CPU time = 32250 ms, elapsed time = 32729 ms.

so, it's definitely a parsing issue.
select count(*) from sys.index_columns
where object_id in (OBJECT_ID('tables_names'),...')

says there are 128 columns, when
select * from sys.indexes
where object_id in (OBJECT_ID('tables_names'),...')

returns 43 rows with HEAP, CLUSTERED, NONCLUSTERED indexes.
Could you recommend what to look at? Why does it parse so much?
Update: Thank you for "Break the query up" and "Use a FORCE ORDER hint", but this SQL is generated by our application, so it could be a lot of effort to make it possible with the application logic, but in general cases they should be a great solution.
Second update: Applying SP3 did the whole thing - the whole execution query takes less than one second. The performance became better by two thousand times :)

I have an SQL Server 2014 (12.0.2000.8)

RTM version? I recall running into excessive compilation duration (minutes) for some queries. The issue was fixed post RTM. I suggest you patch your server to a supported patch level (SP3+).


How to connect to an Amazon PostgreSQL database using SSL I'm trying to log into an Amazon PostgreSQL DBS using SSL.  I have all credentials but my psql version doesn't seem to support SSL.  I've tried searching Postgres docs but all I see is how it has native support.  Here's my command and output.  I'm running the psql command on a CentOS 6.5 box.

psql --host=XXXX.rds.XXXX.com  --sslmode=require --sslrootcert=rds-ssl-ca-cert.pem \
--port=XXXX --username=XXXXX --password --dbname=XXXX

Response:

psql: unrecognized option '--sslmode=require'

So my question is, how do I install a psql client that supports SSL.  I don't need the full blown DB support, just the connector, psql.

Thanks

I should add that I'm trying to follow these instructions.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

Many connection options aren't available as command line flags. That's one of them. Use a connection string, e.g.:

psql "host=XXXX.rds.XXXX.com sslmode=require sslrootcert=rds-ssl-ca-cert.pem port=XXXX user=XXXXX dbname=XXXX"

(Syntax corrected)


How can I generate call stack on error on the fly I have an issue with an index on a Sql2k server:

Event Log shows the following errors happening:

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Error: 644, Severity: 21, State: 5

and then ...

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Could not find the index entry for RID '16958f0b001e00020000' in index page (4:964157), index ID 74, database 'db'..

I've been chasing this error and have already ruled out http://support.microsoft.com/kb/822747

Then I found the following forum discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60518

My question, I guess, is whether or not anyone knows this "undocumented way to turn on dumping for a particular error." (last forum entry).

Thank you, in advance.

If the reports are always referring to the same index, then you might just have a corrupt nonclustered index (since the index ID is greater than 1). I'd definitely keep a close eye on hardware diagnostics, and also keep SQL Server up-to-date on patches to identify the root cause of it becoming corrupted. But you may be able to repair the problem by simply rebuilding the index:

DBCC DBREINDEX(table_name, index_name)

It's possible that the corruption is a total fluke (either a software bug, or a hardware error that went undetected), but if it hits again, and corrupts a table/clustered index, rather than just a nonclustered index, then you could have a bigger problem on your hands.


SSIS Excel export changes # to I am exporting an excel file through SSIS using excel connection manager. I am using SSIS 2008 version and the excel is .xlsx

CREATE TABLE `Sheet1` (
    `Date` DATETIME,
    `NMMO` NVARCHAR(30),
    `P1 #` NVARCHAR(30),
    `P2 #` NVARCHAR(30)
)

but when i see the excel sheet, it gets created but the # symbol is replaced by . I have used P1 '# but it does not make any difference. Is there any way i can retain the column names in excel as it is? Please Help

You can alter the table header using a script task:

Note: you have to add Microsoft.Office.Interop.Excel.dll file to the following directories (.Net Framework dll directory)  C:\Windows\Microsoft.NET\Framework\v2.0.50727 and (sql server data tools dll directory) C:\Program Files\Microsoft SQL Server\100\DTS\Binn (using vs 2005 and sql 2008) and then add this dll as a reference in your script task

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Text;
using Microsoft.Office.Interop.Excel;

public void Main()
    {

        string filename = @"F:\ExcelLibrary\myexcel.xls";
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }
        Microsoft.Office.Interop.Excel._Application oApp;
        Microsoft.Office.Interop.Excel._Worksheet oSheet;
        Microsoft.Office.Interop.Excel._Workbook oBook;
        oApp = new Microsoft.Office.Interop.Excel.Application();
        oBook = oApp.Workbooks.Add();
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oBook.Worksheets.get_Item(1);
        oSheet.Cells[1, 1] = "Date";
        oSheet.Cells[1, 2] = "NMMO";
        oSheet.Cells[1, 3] = "P1 #";
        oSheet.Cells[1, 4] = "P2 #";
        //Microsoft.Office.Interop.Excel.Range oRange = oSheet.Range["A1", "B3"];
        if (oApp.Application.Sheets.Count < 1)
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.Add();
        }
        else
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oApp.Worksheets[1];
        }
        oBook.SaveAs(filename);
        oBook.Close();
        oApp.Quit();
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Don't forget to set the Delay Validation property to True for DataFlow Task which contains the Excel Source.

References

Create Excel File with Header Name by using C# Coding


Normalized Table Structure for Logging How can I normalize a table that has four columns for logging the latest activity for a single row:

Created On
Created By
Modified On
Modified By

We have dozens of tables, all with these 4 columns. Is there a way or pattern to normalize this in an efficient and flexible manner?

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:

CREATE TABLE dbo.Users
(
  UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
  Username NVARCHAR(128) NOT NULL UNIQUE,
  /* , other columns */
);

CREATE TABLE dbo.Tables
(
  TableID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 tables
  Name NVARCHAR(128) NOT NULL UNIQUE
  /* , other columns */
);

This auditing table assumes that all of the audited tables have an INT primary key (or a PK that will fit into INT). This will obviously be more complex if you have different data types, or compound primary keys, in which case you may just consider different auditing tables - still more valuable, IMHO, than only keeping the last modification for any one row.

CREATE TABLE dbo.AuditLog
(
  TableID       TINYINT NOT NULL
                FOREIGN KEY REFERENCES dbo.Tables(TableID),
  ID            INT, -- loose reference to entity table's PK
  Action        CHAR(1) CHECK (Action IN ('I', 'U', 'D')),
  UserID        TINYINT NULL -- just in case
                FOREIGN KEY REFERENCES dbo.Users(UserID),
  EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(Note that I intentionally did not dictate what the clustered index should be, since I don't know all of your query patterns aside from the one mentioned in the question. It is quite likely that you will want to cluster leading on EventDateTime, especially if you are going to query for events that happened recently, and/or you are going to purge data periodically, which is never a bad idea. This will at least ensure that new rows are added to the "end" of the table instead of page splits happening all over the place if you choose something like TableID as the leading column.)

Now your trigger can check the user name (not sure about your authentication method, but this may be via SUSER_SNAME()), it knows what table it's dealing with, it can determine the action, so it just has to add the ID(s) from inserted/deleted. Hokey example that assumes a table named dbo.foo with a primary key called FooID:

INSERT dbo.tables(Name) SELECT N'dbo.foo';
GO

CREATE TRIGGER dbo.AuditFoo
ON dbo.foo
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID TINYINT, @TableID TINYINT, @now DATETIME;

  SELECT @UserID = UserID -- could be NULL
    FROM dbo.Users WHERE Username = SUSER_SNAME();

  SELECT @TableID = TableID
    FROM dbo.Tables WHERE Name = N'dbo.foo';

  -- inserts
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'I', @UserID
    FROM inserted AS i WHERE NOT EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- updates
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'U', @UserID
    FROM inserted AS i WHERE EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- deletes
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'D', @UserID
    FROM deleted AS d WHERE NOT EXISTS
    (SELECT 1 FROM inserted AS i WHERE i.FooID = d.FooID);
END
GO

Only one of those inserts will fire per statement / trigger invocation (well, to be pedantic, the trigger itself will fire multiple times in the event of MERGE).

If you don't want to keep a list of users / tables you can always populate this dynamically (though if you're not strictly controlling the list you may seriously reconsider the TINYINT suggestion above). E.g. to capture users you're seeing for the first time and haven't manually inventoried:

  SELECT @UserID = UserID FROM dbo.Users WHERE Username = SUSER_SNAME();

  IF @UserID IS NULL
  BEGIN
    INSERT dbo.Users(Username) SELECT SUSER_SNAME();
    SELECT @UserID = SCOPE_IDENTITY();
  END

But for the table this doesn't make much sense. Just add the row when you create the trigger, and you can even hard-code the TableID instead of deriving it at runtime.

Once you are collecting data in dbo.AuditLog, you should be able to easily derive information about the last action of any specific type, restricted to a table, user, or even individual entity. If you need help constructing any of those queries, please start a new question with the schema, some sample rows and desired / expected results. Using SQLFiddle can be quite helpful.


Store massive values data from remote controllers I implemented telemetry system in order to observe state of remote objects. Every object has a controller collecting data from some sensors (about 10-30 controlled parameters, lets take average value = 20). Controller send stored values to server approximately 100 times per day. There ara about 5000 objects.
I use simple schema to store values:

Params table(id, controller_id, parameter, index) and

Values table(id, parameter_id, value, add_time).

So I've got a rapidly growing Values table. I need to store values for one year minimum and the object count is increasing dramatically.

20 (values row) * 100 (times per day) * 5000 (objects) = 10M rows per day;
3.65B rows per year

I want minimize the used space and amount of rows. Also I've applied partitioning.

The values are used for reporting, usually daily. The report is built by one or two selected parameters.

Question: is it a good idea to store daily values in a single text field? Every new controller params will be appended to existing values field. New Values table(id, date, controller_id, values);

For example:

00:00:01=0:1.234;1:4.44;2:124.4;|00:01:06=0:1.256;1:2.55;2:3.44;|...

So daily values query is very simple SELECT * FROM values WHERE controller_id=123;

Is there a better solution? Or should I use existing one?

EDIT #1:
Controller has multiple controlled params (temperature, pressure etc)
Param changes stored in values table.

So query for report usually will be like:
We show to user available parameter list for controller:

SELECT index FROM parameters WHERE controller_id = 123;

Then user selects needed indexes and submits them to server:

SELECT * FROM values WHERE parameter_id IN (index0, index7,...) AND add_time >= today_date;

3.65B rows a year is a lot.  How many year's data will you keep?

PARTITIONing?  Don't do that until you have a reason.  One reason is that you want to delete data that is over a year old.  In which case, use a rolling RANGE of partitions based on month or week.

Another thing to consider -- will you need the details after some period of time?  Or can you simply summarize the data, then throw away the raw data.  This will potentially save a lot of space.

One text field -- it depends on what queries you will be doing.  If you want the "average temperature" for last week, a single text field would make it 'impossible' in SQL.

What do the queries look like?  Probably they want summary info.  No amount of indexing will salvage the performance of a huge table.  You must do incremental summarization.

If controller 123 has data scattered over 3.65B rows, it will take hours, maybe days to fetch all the info for that one controller.  Think again whether that is really the query you want to run.  And think about the other queries.

You must present all the queries up front in order to design this table optimally.


How do I export data using PostgreSQL COPY command in required format? I have some sample data in my PostgreSQL 9.5 db like follows:

ID(integer)   value_1(numeric)   value_2(numeric)  array(text)
1             25.2               14.1              1,112,292,19.7
2             11.3               5.9               2,30,110,22.3,60,270,30.1

I am exporting this data in my_table using Postgres COPY command to a custom text file like this:

Copy
(
Select
ID, value_1,
value_2, array
from
my_table
Order by ID
) to '~my_path/output.str' With DELIMITER ',';

I get the exported output like:

1,25.2,14.1,1\,112\,292\,19.7\
2,11.3,5.9,2\,30\,110\,22.3\,60\,270\,30.1\

However, my desired output is:

1,25.2,14.1,1,112,292,19.7
2,11.3,5.9,2,30,110,22.3,60,270,30.1

How do I remove these unwanted characters \ in COPY command export output?

You cannot.  Just ask an editor to replace \, with ,.

Otherwise, if the desired output format is really what you want, you can change the query:

SELECT id::text || ',' ||
       value_1::text || ',' || ... ||
       your_array_column
...

In any case, I'd suggest fixing your array data type.  Storing numbers as text looks really bad.


Obtain SQL Login account password Is there anyway I can obtain a SQL Login account password, I did not create the logins so I would like to see what the password is for a certain SQL login.

Thanks!

If it's a sql login and you have access to the application servers, you could look for it in connection strings on those. Otherwise, there is no way to find the password.  Your only option is to reset the password.


Modeling a database for a printing company Scenario
Create a conceptual schema for the case study assigned to you using the concept of entity relationship model. Specify the cardinality ratio and participation constraint for each relationship type.
The following requirement is for a ABC printing company that handles printing jobs for book publishers.

The printing company keeps track of publisher's id, publisher's name, Publisher's address composed of street, city, postcode, publisher's telephone number and credit code.

The company keep track of the printing jobs (books or part of books) and the job id is the unique key for each job. The company also would like to record the date of the printing job, the description and the type.

A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. The purchase orders for each job is recorded for each purchase order the purchase order id (unique) and the date for the purchase order also recorded. Each printing job may have several purchase orders assigned to it. If the job is deleted, you need not keep track of its purchase order any longer.

The printing company also would like to keep track the details of the materials(items). Each items has item id, description, stock in hand and price.

Each purchase order for each job can have several items.

Entity-relationship diagram

Schema
My schema is like this
Publisher (PID, PName, street, city, postcode, Ptele,CCode)
p.k = PID
f.k =

PrintingJobs (JID, Pdate, Description, Type, PID)
P.K = JID
F.K PID

Purchase ( JID, OID, ODate)
P.K = JID, OID
F.K = JID

Item ( IID, stock, Description, Price, JID, OID)
P.K = IID
F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

Question
Can anyone guide me and tell me if I'm correct, what changes I should make, and if I represented the relationships well?

This part in Item doesn't look right:

F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

I suppose the design is only on paper, as no DBMS would allow that, when the primary key of PURCHASE is (JID, OID). You need one (composite) Foreign Key constraint, not two:

  (JID, OID) REFERENCES PURCHASE (JID, OID)

Now this foreign key belong to Puschase_Materials (or Purchace_Items) and not to Materials (or Item). With that in mind, the corrected schema would be:

PurchaseOrders (JID, OID, ODate)
PK = (JID, OID)
FK1 = (JID) REFERENCES Jobs (JID)

Items (IID, Stock, Description, Price)
PK = IID

Purchases_Items (IID, JID, OID)
PK = (IID, JID, OID)
FK1 = (IID) REFERENCES Items (IID)
FK2 = (JID, OID) REFERENCES PurchaseOrders (JID, OID)

The image shows an additional relationship, between Purchase_Materials and Jobs/PrintingJobs, which doesn't appear in the schema - and you don't really need it.

Since there is a foreign key from Purchase_Items to PurchaseOrders (or Purchase_Orders in the image) and a foreign key from PurchaseOrders to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship.

There is inconsistencies in the naming, between the schema and the images. I assumed the following are renames:

Schema         - Image              - Code in this answer
---------------------------------------------------------
Jobs           - PrintingJobs       - Jobs
PurchaseOrders - Purchase           - PurchaseOrders
Item           - Materials          - Items
  (none)       - Purchase_Materials - Purchases_Items

Some table names are plural and others singular.
Some names have underscores between name parts, some not.

It would be best if you fixed those and decide on a naming convention (say all table names are plural, there are no underscores except in case X, etc).


How to start oracle service? I have 3 instance of oracle database which myservice, myservice1, myservice2.
1st database service name is MYSERVICE; it's working ok

But I can't start myservice1 and myservice2. How can I start these service?
I am using oracle linux.

 tnsping myservice1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2020 08:27:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydatabase.orcl.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = myservice1)))
    OK (0 msec)
    [orcl@mydatabase ~]$
    [orcl@mydatabase ~]$ tnsping myservice2

    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-MAY-2020 08:28:02

    Copyright (c) 1997, 2014, Oracle.  All rights reserved.

    Used parameter files:

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydatabase.orcl.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = myservice2)))
    OK (0 msec)

When I trying to connect the db with SERVICE NAME: myservice1 using SQL developer it says:

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Vendor code 12514

select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
myservice          OPEN

Do not use the "-startoption mount". Mount state is only for making some configuration changes, backup/recovery operations, and a handful of other administrative functions. Just use this:

srvctl start database -db myservice1
srvctl start database -db myservice2

Note that "tnsping" won't actually test whether a service is running, only whether the listener is running on the specified hostname and port number from the connection string. You could use any made up service_name and still get an "ok" response as long as the listener is up.


Efficient way to paginate in sql server with 50M plus data (sql server 17) I was looking for efficient way of pagination in sql server 2017 so I came across following code:
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
order by id
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only option(recompile)

This works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.
So, I decided to fetch data with something like id > xx concepe. Something like
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id > 50000000 and id <= 50000050
order by id

This code works lighting fast. However, this could not work perfectly if there are deleted data.
So, I tweak little bit and switch to Top method instead.
select Top(50) id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id > 50000000
order by id

This seems quite close to get next 50 data in lighting speed. However, this too seems failed when you want to move from page 1 to page 3. You could not know how many data are deleted in between row 50 to row 100. So, this too seems failed for numeric pagination.
Now, I started to look pagination with top method and finally found:
SELECT  top(50) id, col_1, col_2, col_3
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id) AS RowNum, id, col_1, col_2, col_3
          FROM      table_name
          where deleted_at is NULL
        ) AS RowConstrainedResult
WHERE   RowNum >= 50000000
ORDER BY RowNum

But, even for this it took around 20-25 sec when the RowNum is around 50M.
How can I decrease this query time. AnyIdea?
NOTE:
I have already created an index as
create index IX_table_id_deleted_at on table_name(id asc,deleted_at) include(col_1, col_3, col_3) where deleted_at is null

Also, I have tried with NOCOUNT ON as well as WITH(NOLOCK) but still the query time is around 11 sec

There is simply no cheap way to get the 12753rd page out of a 50M row result set.

  [offset .. fetch] works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.

Which is normally fine, as you rarely page deep into a large result set.

  [The "Key Seek Method"] failed when you want to move from page 1 to page 3.

Yep.  But it's so much more efficient that you simply avoid skipping pages.


Usage of SQL Server 2008 R2 Express. Please Advise I'd like you all to give me hints and advise on the following scenario:

We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application.

The Web Application won't have more than 10 CRUDs, and won't have a high data traffic, all of them are simple web forms. About data scalability, the Web Application is basically focused on employee data, like personal information, vacancy control, and the daily frequency control.

From what I've seen, I've estimated a maximum of 5 simultaneous users, generating data traffic.

And also, our employee table should hold a maximum of 100 records.

As to physical limitations I think we're fine, since I don't think a maximum of 100 employee should generate 10 GB for the next, say, ten years? But since I've read that the Express edition only manages 1 GB of RAM and uses only 1 CPU, I thought it'd be best to ask.

Do you think it can manage to keep up a good response and performance in this case?

So please advise on using SQL Server 2008 R2 Express or some other database manager. Just keep in mind that it has to be free.

Aditional info:

Windows Server 2008;
4 GB of RAM;
1 TB HD.

I believe Express should be more than adequate. Once your database is larger than 1GB it won't all fit in memory, but unless your hard drive is 5400 RPM and your users are very particular it is quite unlikely they would ever know the difference. It doesn't sound like you'll get past 1GB in any feasible time frame anyway...


In a school database, do I need to have repeating data when a student can also be a teacher? I've seen lots of ERDs of schools, teachers, and all the junction/associative tables.  They always have three tables:

Teachers
Students
Associative table betweeen them, maybe subjects of something like
that.

it is assumed that a teacher cannot be a student and vice-versa.  But what if a student can also be a teacher (a non-grade school, obviously)?  If I keep the traditional view, then I end up with repeating data between teachers and students, such as the name.  What am I missing?

Would it be bad design to have:

Person table
----
person_unique_id
lastname
firstname
...

Teacher table
----
teacher_unique_id
person_unique_id
subject_unique_id #if I want to know what this person can teach.

Subject table
----
subject_unique_id

I would still have to have an associative table for all the many-to-many relationships like:

schedule
----
schedule_unique_id
person_unique_id
teacher_unique_id
subject_unique_id
time
date
room
....

Then I could do something like:

SELECT * FROM Person p, Teacher T, Subject s
WHERE p.person_unique_id = t.person_unique_id
AND t.subject_unique_id = s.subject_unique_id
AND p._person_unique_id = 12;

What you are saying makes sense, yes.

One thing is that you actually can't select students without excluding Persons that are teachers...  And even if you did, the students that are also teachers would be missing without further efforts. So maybe it makes sense to have a students table as well to assign Persons that are students the same way you did it with teachers.

Meaning:
P1 is a teacher.
P2 is a Student.
...

There are for sure more possible solutions. I think it all depends on the requirement you have and any solution must stand.

It's possible that a student also sometimes is a teacher and maybe even teaches the course where he or she is a Student... Or Maybe the school Administration used a database for different school let's say elementary same time as for parents night school. Maybe then additional flexibility is required.

Basically just go ahead and think about normalization to solve your requirement and get to a valuable Model. Using m:n tables to solve this just follows the normalization rules.

Normalization howto example:

https://www.studytonight.com/dbms/database-normalization.php


Mysql innodb recover from frm and ibd There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1).

Situation:

I assigned to a server where the mysql database is just crashed and can't started. To let them start, i saved the whole /var/lib/mysql folder, then deleted the ibdata1, ib_logfile0, and ib_logfile101, in order to let the mysql server start.

That's worked, MyISAM and some InnoDB table got back to online. However there's some databases where some InnoDb table can't back to operation.

It turned out, that there's no backup from the databases (and they operating 3+ years). Everybody excepted that plesk makes dayli database backup, but actually not (Schrödinger's backup).

After this, it's also turned out that the ibdata1 I saved has been previously overwritten or deleted and a new one created by the mysql (I view the file with hexedit and it's filled fully with zeros). I also looked for the previous file in the lost+found but no result.

There are two database which is crutial to restore, one wordpress site and the plesk database.

Then i copied the saved mysql directory to my machine and started trying to restore the broken tables.

Problem:

I have a crashed MySQL database where the ibdata1 file has benn lost and no database backup available.
On my machine is the same mariadb version installed, so i moved the save mysql directory from the server and started to restore as much as i can from the two databases requested by the owner.

I already wrote the innodb_force_recovery = 6 line into /etc/mysql/my.cnf to help recover the broken tables.
As described here: Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

Then i tried to reimport the *.ibd files as described here:
https://medium.com/magebit/recover-innodb-database-from-frm-and-ibd-files-99fdb0deccad [1]. An that's worked on smaller tables. On other bigger tables like wp_posts when execute ALTER TABLE wp_posts IMPORT TABLESPACE the mysql server crashes:
InnoDB: Trying to access page number 68160 in space 15 space name admin_wordpress/wp_posts, which is outside the tablespace bounds. Byte offset 0, len 16384 i/o type 10.

So the Question: How to restore tables with this kind failure?
What i found:

1) https://twindb.com/recover-corrupt-mysql-database/
For this i need ibdata1. So it's a dead end.

2) Percona data recovery tool for innodb
Theorically this can connect the existing *.ibd to the new ibdata1
However i can't find binary package, the debian package doesn't contains ibdconnect command. I also found the source: https://github.com/jiramot/percona-data-recovery-tool-for-innodb but this can't be compiled. I tried to resolv the compilation an linking errors but this might tacke more time and maybe this solution doesnot work.

3) And the last chance: Rebuild the site. I can extract string from the ibd files strings admin_wordpress/wp_posts.ibd and that way extract the text of the posts on the wordpress page, nut rebiulding a site this way is a Sisyphean task. (And this mathod can't applied to the other database is should restore)

I there any other working method to extract or restore data from *.ibd files?
Even extracting the records to eg.: JSON or BSON or CSV is gonna be good enought to move forward in the recovering process.

[1]: The external site says: drop the database, recreate the empty tables by using mysqlfrm on the broken database *.frm files (this results create table statements). Then execute ALTER TABLE "table" DISCARD TABLESPACE to release the newly created *.ibd file. Move the old *.ibd to the server directory, then execute ALTER TABLE "table" IMPORT TABLESPACE to acquire the old tablespace and therefore recover the data.

There is a way to fix this issue but it may not work all the time, but still it worth a try (note take a backup first):

execute the following command mysqlfrm –server=root:PWD@machine_ip –port=xxxx “/path/to/mytable.frm” > “/destination/path/recovered_mytable.sql”
This will generate  create table script you can you can execute it to create the table
remove  new tablespace for mysql alter table mytable drop tablespace;
copy the original one to the new table location for example cp /home/mysql/x.ibd /mysql/data/mydb/
reactivate your tablespace alter table mytable import tablespace;

for more information about mysqlfrm please refer to https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.4-en/mysqlfrm.html
file can be downloaded from https://downloads.mysql.com/archives/utilities/


Search Column with Like by Lookup table I have a table with a "Description" field.  Which contains the titles of a whole lot of phone models.

**Table1**
Id - Int (PK)
Description - nvarchar(200)

**Lookup Table**
LookupId - Int (PK)
SearchString - nvarchar(50)
Result - nvarchar(100)

I then have another lookup table with all the different models of phones I want to search for.

If I was going to do a text search on the description field I could easily write this by doing

SELECT * FROM Listing WHERE Description Like '%SearchTerm%'

However I want to somehow do a search on each record in the Lookup Table.  Im thinking that I probably have to do a loop for each record in the Lookup table and then write some dynamic sql?  Is there an easier way than this?

If I understand your question correctly, you basically want to join your [Table1] to [Lookup Table] whenever [Table1].[Description] is like [Lookup Table].[SearchString].

You can try this:

select * from Table1
join [Lookup Table]
on Table1.Description like ('%' + [Lookup Table].SearchString + '%');

This will only return records where there is a match on the like condition. You can adapt this to an outer join if you need all rows from one of the tables, as required.

I won't vouch for the performance of this query. Also, it is an adaptation of another question on Stack Exchange.


INSERT INTO FROM: Concatenating Forename and Surname when Forename can be blank, I have a staging (RAW) table that contains one field for to hold a person's first name, and another for a person's last name. I want to insert those value on a production (DBO) table, where the person's forename and surname will be concatenated:

INSERT INTO [dbo].Test
SELECT firstname + ' ' + lastname
      ,field1
      ,field2
      ,fieldn
FROM RAW.Test

Problem however is that I just found out that the firstname field can be NULL (no blanks, but NULL) in the RAW table. Considering the code I have above, this would mean that the person's name would be " Smith" (note the trailing space) instead of "Smith".

What would be the best way to check for such issue and only add the trailing space between firstname and lastname if firstname contains a value?

Note that firstname would never be blank, since the RAW table has a NULLIF conditional that replaces all blanks with NULL.

Thanks for your help and support!

P.

EDIT: Sorry for the lame edit but I made a mistake, the firsname field is the one that can be blank (which makes sense if you see the example I gave about " Smith").

Liya has a nice solution. This is another that handles both the null case and the empty string last name case as well.

INSERT INTO [dbo].Test
select LTRIM(RTRIM (COALESCE(firstname,'') + ' ' + COALESCE(lastname,'')))
    ,field1
    ,field2
    ,fieldn
FROM RAW.Test ;

EDIT: Modified to allow either field to be nullable or an empty string with trimming in full effect in response to the modified requirements of the OP.


Differential Backup from SQL Server 2012 to Azure Blob I am attempting to backup a database to my Azure Blob storage. The databases are relatively large... between 1 and 30 Gig.

I have successfully backed up the first day, but on subsequent days, I get an error stating that the file already exists and that I need to use the WITH FORMAT.

But I need to do a differential backup, as the load of data on a full backup would be very slow and pricey.

I get the error "MyBackup.bak exists on the remote endpoint, and WITH FORMAT was not specified.".

What I am trying to do is:

BACKUP DATABASE [MyDatabase]'
TO URL = 'https://myaccount.blob.core.windows.net/dbbackups/Container/', MyDatabase.bak',
WITH CREDENTIAL = 'AzureDBBackupsContainer'
, DIFFERENTIAL
, COMPRESSION;

I have seen that a differential backup is supported when using URL backups.

Is this actually not possible, or am I doing something wrong? Do I need to somehow change the file name each day?

You are correct, this is not possible in Azure cloud. You can do this for premise backup which I highly discourage. It just makes your restore more complicated. But in Azure if file name already exists in your container only way to use that same name is to use WITH FORMAT which will create a new media set.

I tested this and I get following error message:

  Msg 3296, Level 16, State 1, Line 1 The file
  https://storageacocunt/sqlbackfiles/testFull.bak
  exists on the remote endpoint, and WITH FORMAT was not specified.
  Backup cannot proceed. Msg 3013, Level 16, State 1, Line 1 BACKUP
  DATABASE is terminating abnormally.

From Books online:

BACKUP (Transact-SQL) | Microsoft Docs

  FORMAT

  Specifies that a new media set be created. FORMAT causes the
  backup operation to write a new media header on all media volumes used
  for the backup operation. The existing contents of the volume become
  invalid, because any existing media header and backup sets are
  overwritten.

You can check this with RESTORE HEADERONLY command. If you take a fullbackup and then a differential WITH FORMAT, you will see backup type change from 1 to 5.

I suggest you create a new for each file.

This link has example about generating a new filename for each backup.


Can mysql restore a single table from a large mysqldump? What is the best way to restore a single table from a large (~5GB) database mysqldump file?

update:
I have found solutions (posted below) using command line tools to parse the table, but is there a way to do this with mysqlimport?

I have found two solutions, one using

grep -n "Table Structure" mydump.sql
# identify the first and last line numbers (n1 and n2) of desired table
sed -n n1,n2p mydump.sql > mytable.sql # (e.g. sed -n 48,112p)

and one using awk

awk '/Table Structure for table .table1./, /Table structure for table .cultivars./{print}' mydump.sql > mytable.sql


What is the advantage of using numeric primary keys in a relational database? Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to):
create table widgets (
    widget_id number primary key,
    widget_name varchar2(20) not null,
    widget_colour varchar2(10)
);
create unique index ix01_widgets
    on table widgets (widget_name);

So there are two indices: one for the primary key, widget_id, and one for the widget_name.
Can anyone explain the advantage of having a numeric primary key over just using widget_name as the primary key? The latter has the advantage of making the contents of child tables more readable, e.g.:
create table widget_characteristics (
    widget_name varchar2(20) not null,
    characteristic_name varchar2(20) not null,
    characteristic_value varchar2(100),
    constraint pk_widget_characteristics
        primary key (widget_name, characteristic_name),
    constraint fk01_widget_characteristics
        foreign key (widget_name)
        references widgets (widget_name)
);

Otherwise (if using a generated numeric primary key) I would need to create views to see the denormalised data.
Is it something about the "indexability" of numbers versus strings? I can understand the issue of index size if the identifier (i.e. widget_name) is long, but if it's short then is that still an issue? For tables with multi-column primary keys I can understand the logic to having a generated numeric PK, but for "simple" tables I'm not clear.
Given the ubiquity of numeric primary keys I assume there is a fundamental reason for this: I'm just not sure what it is!

Size is one consideration, certainly.  Consider not only the size of the index in the widgets table, but also that primary keys show up in other tables as foreign keys.  In some systems, even short strings are going to take up more space than integers (e.g., MSSQL:  VARCHAR is a byte per character plus 2 bytes, so at only two characters you're already as large as an integer).

Almost all numeric primary keys I have seen are surrogate keys, implemented via series, sequence, auto_increment, IDENTITY or whatever the database engine's native method is for generating values itself.  I suspect this is a leading reason for the ubiquity of the numeric primary key.  One advantage to surrogate keys is that they have no business meaning.  Since business meaning can change over time, using a key without business meaning helps ensure that the primary key is static.

If your widgets have some sort of industry-standard identifier (like the auto industry's VIN, publishing's ISBN, UPCs and so forth), that's probably the best choice for your primary key.  My concern in using widget_name is that attribute's immutability.  Will it ever change?  How do you know it will never change--did Sales tell you that?  :)

The whole surrogate vs. natural key issue is nearly a religious debate, and is sort of tangential to your question.  I would say if you have a natural key that is static, minimal, and unique, use it.  Otherwise, consider a surrogate key (which is likely going to be numeric).


Can we use save transaction command only in nested transactions? I am a newbie to SQL and I came across a situation where I have to use save transaction, but can any one please let me know if save transaction should only be used in nested transactions ? If not what is the difference between having a save point and not having a save point transaction in a Stored procedure where we don't use nested transactions ?

Thanks !!

I am a newbie to SQL and I came across a situation where I have to use save transaction

Savepoints are rarely used (or useful) and it's unusual that "a newbie" would "have to use save transaction".

  what is the difference between having a save point and not having a save point transaction in a Stored procedure where we don't use nested transactions

Save Points allow partial rollback.


Does SQL Server Management Studio 2017 Contain a New Explain Feature TL;DR

I happened upon a feature in SQL Server Management Studio v17.9 which I was not aware of. It looks a bit like the EXPLAIN found in Oracle, PostgreSQL and MySQL. Where is this feature documented?

How to Turn The Feature On

I was running the following query with the options Include Actual Execution Plan (Ctrl + M) and Include Live Query Statistics turned on:

    SELECT sdes.session_id
          ,sdes.[status]
          ,sdes.login_name
          ,sdes.[host_name]
          ,sder.blocking_session_id
          ,sdb.name
          ,sdes.cpu_time
          ,sdes.logical_reads --optionally: + sdes.reads + sdes.writes
          ,sdes.last_request_start_time
          ,sdes.program_name
          ,sdes.session_id
          ,sder.request_id
          ,dest.[text]
    FROM   sys.dm_exec_sessions             AS sdes
           LEFT JOIN sys.dm_exec_connections     AS sdec
                ON  sdes.session_id = sdec.session_id
           JOIN sys.databases               AS sdb
                ON  sdes.database_id = sdb.database_id
           LEFT JOIN sys.dm_exec_requests  AS sder
                ON  sdes.session_id = sder.session_id
           CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest
    WHERE 1=1
    AND sdb.name = ''

Outcome

This produced four results tabs:

Results
Messages
Live Query Statistics
Execution Plan

Special Results Tab

When I switched to the Results tab, I noticed a result set just below the actual results which looks like this:

Observations

If I turn on either the Include Actual Execution Plan (Ctrl + M) option or the Include Live Query Statistics option, then the SQL Server Explain isn't displayed. The "SQL Server Explain" is only displayed if both options are turned on.

Questions

Can this feature be turned on without having to activate both Include Actual Execution Plan (Ctrl + M) option and the Include Live Query Statistics?
Where is this neat little feature documented?

Prerequisites

The following components and/or software packages are involved in the scenario:

Server

Windows Server 2016
SQL Server 2016 SP1

Client

Windows 10 Version 1807 (Build 17134)
SQL Server Management Studio v17.9

Microsoft SQL Server Management Studio    14.0.17285.0
Microsoft Analysis Services Client Tools  14.0.1016.283
Microsoft Data Access Components (MDAC)   10.0.17134.1
Microsoft MSXML                           3.0 6.0
Microsoft Internet Explorer               9.11.17134.0
Microsoft .NET Framework                  4.0.30319.42000
Operating System                          6.3.17134

SQL Server Management Studio v17.9

Include Actual Execution Plan (Ctrl + M) turned ON
Include Live Query Statistics turned ON

No, not a feature - it's turning one of these on:

SET SHOWPLAN_TEXT ON;
SET STATISTICS PROFILE ON;

The SHOWPLAN_TEXT option and STATISTICS PROFILE option are well-documented and have been around for over a decade, but I don't know when SSMS started turning it on for the combo of live query plans and actual plans. I would consider it a bug.

Here's the SSMS bug I filed for it.