Not specific to your question but in general do not put sales into an expenditure table. The name of the table suggests that the table contains information about money leaving the business, not coming in - it will confuse the reader and hinder maintainability in the future. I suggest you create a second table called sales and put your receipts in there.

There are very good example database designs that you can find online (I googled bookstore database schema and quite a few hits came up that would be useful to you).

Additionally, if you were going to do this seriously, then I'd look into the general ledger system or otherwise known as double entry book-keeping system which will help you model this a bit better. I'm not an accountant, but wikipedia seems to have a decent link on it:

https://en.wikipedia.org/wiki/Double-entry_bookkeeping_system


How can I strip non-numeric characters out of a string?

I found this T-SQL function on SO that works to remove non-numeric characters from a string.

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END


Postgres- update a value in a array on particular index

Indeed there is no such function, but you can easily write your own:

create function array_set(p_input anyarray, p_index int, p_new_value anyelement)
  returns anyarray
as
$$
begin
  if p_input is not null then
    p_input[p_index] := p_new_value;
  end if;
  return p_input;
end;
$$
language plpgsql
immutable;

(If you never need it for anything different than bigint[] then you can replace the anyelement and anyarray with the equivalent types)

Then you can use it like this:

update the_table
  set ids = array_set(ids, 2, 50::bigint)
where ...;


How can I create a role to create,update,delete stored procedures and views

The easiest way is to add a user to the db_ddladmin role.

-- Older method
EXEC sp_addrolemember 'db_ddladmin','username';
-- New method
ALTER ROLE db_ddladmin ADD MEMBER username;

This will give the user the ability to create/modify/drop any object in any schema.  Unfortunately that will include objects you probably don't want them to change (tables for example).

A more precise method is to use the CREATE permissions.  There is a small trick to using them however.  You will also need to grant ALTER on the schema where they can create/alter the objects.  So to grant the ability to create procedures and views in the dbo schema you might do this.

GRANT ALTER ON SCHEMA::[dbo] TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];
GRANT CREATE VIEW TO [UserName];

Same with CREATE TABLE, CREATE FUNCTION etc.  The reason you need both is this: CREATE PROCEDURE gives you the ability to create the procedure, ALTER schema gives you a place to put the procedure as you create it.

I should also point out that the CREATE permissions also grant the ability to alter, and drop.


select count(*) in mysql 5.5 innodb-- rewrite advice?

Since you already have the necessary indexes, consider the next thing: Amazons RDS

You are most likely using the default settings. You cannot effectively tune InnoDB for multithreaded/multicore usage in smaller RDS server models.

As to server model usage of memory, here is a chart

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

Since count requires passing through many data and index pages, try using a bigger server model.

I have discussed this before:

Aug 02, 2012 : Local database vs Amazon RDS
Feb 26, 2013 : MySQL optimization

Give it a Try !!!


How to perform schema migration from array to many-to-many to lookup table?

Basically, I suggest a many-to-many design like this:

CREATE TABLE blog (              -- I suggest singular terms
   blog_id serial PRIMARY KEY    -- and descriptive names
 , blog    text NOT NULL         -- NOT NULL!
);

CREATE TABLE tag (
   tag_id serial PRIMARY KEY
 , tag    text NOT NULL          -- NOT NULL!
);

CREATE TABLE blog_tag (
   blog_id int REFERENCES blog
 , tag_id  int REFERENCES tag
 , PRIMARY KEY (blog_id, tag_id) -- !
);

See:

How to implement a many-to-many relationship in PostgreSQL?

And the migration can be done efficiently with a a single command using data-modifying CTEs:

WITH org AS (TABLE blog_posts)       -- read original table once
, ins_blog AS (                      -- fill table blob ...
   INSERT INTO blog (blog_id, blog)  -- ... keeping original IDs for simplicity
   SELECT id, blog_title
   FROM   org
   )
, bt AS (
   SELECT unnest(tags) AS tag, id    -- unnest tags & keep link to blog_id
   FROM   org
   )
, ins_tag AS (                       -- fill table tag ...
   INSERT INTO tag (tag)
   SELECT DISTINCT tag               --  ... with distinct tags
   FROM   bt
   ORDER  BY 1                       -- optionally order tags while being at it
   RETURNING tag_id, tag             -- new tag_id + connected tag
   )
INSERT INTO blog_tag (blog_id, tag_id)
SELECT id, tag_id
FROM   bt
JOIN   ins_tag USING (tag)           -- join to unnested tags, voilá
ORDER BY 1,2;  -- optional

Related:

Insert data in 3 tables at a time using Postgres

Don't forget to sync the sequence for table blog:

SELECT setval(pg_get_serial_sequence('blog', 'blog_id'), max(blog_id)) FROM blog;  -- sync blog serial

See:

How to reset Postgres' primary key sequence when it falls out of sync?

Consider an additional index on (tag_id, blog_id). See:

Is a composite index also good for queries on the first field?

db<>fiddle here


Why cannot I call a table function in iSeries DB2 that I just created?

As Heinz Z. did, I discovered the problem.

One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it.

Solution is either

changing function parameter to varchar
or cast parameter to char in the function call:

SELECT *
FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'),
                            DATE('10/23/2013'),
                            CAST('ANY' AS CHAR(3))
          )) AS T

If doesn't work, try in any case to remove all parameters from the function and see if they are the culprits. Then you can investigate deeper adding one by one, try to work on dates format, for example you can try to pass current date instead of 10/23/2013.

Also you must investigate on why it thinks that FNREPORT is a type and not a function...


As per the error message, more than a single row is being returned in a context where that is not permitted.

Looking at your query, the following line could easily be the culprit:

SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name);

You can eliminate that as the cause of the problem by modifying that line to:

SET @FQN = (SELECT TOP(1) fqn FROM fulldatabasemapping WHERE rciaem = @name)


Create function with a parameter, which will become the part of IN (1, 2, 3) clause

Use = ANY instead of IN

AND some_table.some_integer_field = ANY (some_other_parameter);

The equivalent of NOT IN would be <> ALL (...)

To declare a parameter with a default value use:

CREATE FUNCTION my_cool_func(..., p_id_list INTEGER[] default array[42])


What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?

Nearly full?  None at all.
Completely full?  Probably a dead database!
By far the biggest consumer of SYSAUX in 19c is the Unified Audit Trail.
Where we used to dump gigabytes of Audit files into a file system somewhere and delete them occasionally, 19c now insists that we dump the same gigabytes of Audit data into tables held in SYSAUX, by default.
It has always been the case that if the Audit system cannot record what it wants to then it throws an almighty strop and crashes the entire database.
Nothing like subtlety.
So, if your Unified Audit Trail is writing to SYSAUX and SYSAUX fills up completely?
Bye bye, Database.
Oracle recommend changing the tablespace used by the Unified Audit Trail.
Kind of makes you wonder why they don't ship Oracle Database with a separate tablespace for this in the first place.


It is OS performance counter and refers to reads of page file. Generally it points to low memory condition on server. With your provided info no specification can be made about the issue. You need to monitor the server processes at that time. Also have a look at other applications consumption if exist on same server along with SQL Server.


There's a lot of redundancy between the mma and the bjj tables. If the only real data difference between an MMA practitioner and a BJJ practitioner is the belts and stripes, I'd consider pushing the unique fields from mma_practitioner and bjj_practitioner tables into the martial_arts_practitioner table; including adding a field indicating whether a given practitioner is mma or bjj (if mutually exclusive), or small fields (bit or 1 character) mma and bjj (if someone can be in both).

Even if other practitioner types will be added later, I'd still recommend this change; creating and maintaining an entire set of roster and session tables for each practitioner type wouldn't necessarily scale well. If there are more significant differences that aren't obvious, that could change this recommendation, but I can't think of anything off the top of my head.

An additional thought: it looks like instructors and students are both kept in the same table. This may make sense (especially if an instructor can also be a student in some other class or practition type). However, there are likely to be a number of fields unique to teachers (certifications, wages/salary, etc.) that would not apply to students. Similarly, there are likely to be a number of fields unique to students (grades, source [for marketing to other students], etc.) that wouldn't apply to instructors. And, presumably, every class/training session must have an instructor. It might make sense to have students and instructors in separate tables.


9000 rows affected message, but no rows actually changed


MYSQL - How to make a table with a column that will sum columns from parent tables?

What you're asking to do is a type of denormalisation: You're adding redundant data to a single table in order to avoid having to query multiple tables to get the same information. This is done to increase performance of SELECT queries and/or simplify such queries.

Since the price columns are in different tables / rows, you unfortunately can't use generated columns.

So you're going to have to consider other options:

You could use a VIEW on top of the FOODS table which summed the prices from the two parent tables. So whenever you wanted to SELECT from the FOODS table (and you wanted to include sum_price), you instead SELECTed from this VIEW. Something like:

CREATE VIEW V_FOODS([the columns of FOODS], sum_price) AS
  SELECT [the columns of FOODS], FLAVOR.price + SIZE.price AS "sum_price"
  FROM FOODS
    INNER JOIN FLAVOR USING(IDflavor)
    INNER JOIN SIZE USING (IDsize);

(As pointed out by RDFozz, note that this isn't actually denormalisation of the data, since the actual data stay in the parent tables.)
You could use TRIGGERs to populate a sum_price column in FOODS. You would need a trigger on UPDATEs to each of FLAVOR and SIZE, and a trigger on INSERTs to FOODS.
You could have a sum_price column in FOODS and give your application the responsibility of keeping it up-to-date.
You could also give up and not denormalise. Just let your application join FOODS, FLAVOR and SIZE whenever you need the sum of prices.


Optimization of multiple CASE Statements

In Postgresql, you can use the FILTER clause as follows (untested):

SELECT
  DD_1409.ZIP_CODE,
  count(*) RECORDS,
  count(*) filter (where GENDER = 'F') / count(*) * 100 GENDER_F,
  count(*) filter (where PRESENCE_OF_ELDERLY_PARENT = 'Y') / count(*) * 100 PRESENCE_ELDERLY_PARENT_Y,
  MEDIAN(LENGTH_OF_RESIDENCE),
  count(*) filter (where HOMEOWNER_STATUS = 'P') / count(*) * 100 HOMEOWNER_STATUS_P,
  count(*) filter (where HOMEOWNER_STATUS = 'R') / count(*) * 100 HOMEOWNER_STATUS_R,
  count(*) filter (where HOMEOWNER_STATUS = 'U') / count(*) * 100 HOMEOWNER_STATUS_U,
  count(*) filter (where MOPS.KBMG_INDEX_GEN2.GEN2 in ('T1','T2','T3','T4','T5','T6','T7')) / count(*) * 100 GEN2_T
FROM MOPS.DD_1409
 LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.IID
 WHERE STATE = 'CA'
 AND ONE_PER_ADDRESS ='Y'
GROUP BY DD_1409.ZIP_CODE;

But as others have mentioned, optimising expressions isn't going to help you; the Postgresql syntax is merely cleaner.

You'll need to show us your execution plan(s) and table definitions for us to be able to help you further.


How to sort query filename output like Windows explorer?

Try setting your NLS_SORT to PUNCTUATION or XPUNCTUATION before running the query:

ALTER SESSION SET NLS_SORT = 'XPUNCTUATION';


Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.
Some posts discussing it are

Performance Tuning 101 - 2005
Plan Regression with Sequence Projections - 2008 R2 @Paul White
Common Table Expressions (CTEs), Window Functions, and Views - 2012 @Jonathan Kehayias
The Problem with Window Functions and Views

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).
An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)


How should I best handle a rapidly growing database?

I don't recommend to shrink your database files unless you are completely sure that the space will not ever be needed again.  Why shrink every night only for it to grow every day?  You're going to run into the pain points of shrinking your data files, and you're forcing yourself to run into performance impact when the database files need to grow during the day.

If it ramped up to 120 GB because of initial space requirements, is it safe to say that the database will not be larger than that (obviously with some buffer)?  Size your database appropriately.

Otherwise, if the database will continuously grow, and you don't have any input on the data growth that happens (in other words, if you can't archive data off of the database) then you will simply need to ensure that there is ample drive space for the required growths.

Do you know which database files have been growing?  There is different behavior that will cause data files to grow, as opposed to transaction logs to grow as well as maintaining their size.  If the transaction log is the main consumer of the space because of heavily logged transactions, you can consider more frequent transaction log backups to ensure the log is reused more often (there are some stops here, as open transactions, so this might not be possible across the board).

More information surrounding what is growing and the scenario is needed to get more specific, but I don't recommend scheduled shrinking of your database files.


The query cost calculated for a query is an estimate. Even in the actual execution plan it is the estimated cost that is shown.

For some reason SQL Server Management Studio uses those estimated costs and comes up with a cost percentage for each query in a batch.

Generally speaking that percentage has nothing to do with the actual performance of the query and should (in my opinion) not be used.

Estimates are based on statics of the involved tables and columns but XML variables (and columns) does not have any statistics that describe what shape for form they have so for XML queries the estimates are even less useful then for other types of queries.

Measure the actual performance instead like duration and IO.

  suggest a more efficient way of writing it

Nothing comes to mind that would improve the performance of your query so until you actually see a performance issue with this query you could use it as is. Provided it does return what you want.


This done, on mongodb sharding version 4.4, shard server must be replica set althoug only 1 server in replica set.


No. You're referring to SQL Server Management Studio, presumably, and that behaviour doesn't exist there. There are other applications you can use that will leverage FKs in that way though.


How to prevent SQLite from blocking while using R?

I solved the problem by opening the database from sqlitebrowser and typing the following query there: pragma journal_mode=wal;

for more info: https://www.sqlite.org/wal.html


Randomizing table contents and storing them back in the table

If the calling app is explicitly setting a particular order in its query (if you are running MSSQL you can check this by having a profiler session running while the app does its thing, other DMBSs will have similar logging options) then there is nothing you can do and if it isn't you can not completely guarantee any particular order.

If no explicit ORDER BY clause is given then the data will come out in an order that is officially "undefined" - it will be what-ever order the server finds most convineint. For a single table query this will most likely be the order of the primary key. In MSSQL if you have a clustered index the results will most likely come out in that order for a single table query. For multi-table queries it is even less clear cut as it depends which way around the query planner choses to go to get your results (which without explicit index hints could vary over time as the balance of data in the tables, as estimated by the index stats the server keeps, changes).

If the table has no clustered index or primary key then the data is likely to come out in an arbitrary order similiar to the order the data was inserted. In this case you could try:

SELECT * INTO temp_table FROM table_to_be_reordered
DELETE table_to_be_reordered
INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()

or this may be faster

INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()
DROP TABLE table_to_be_reordered
EXEC sp_rename 'temp_table', 'table_to_be_reordered'

In the above NEWID() is MSSQL's function to return a UUID and it uses random rather than sequential IDs by default - in other DMBSs you should find a similar function that you can use. Be careful with your choice of function: for instance under MSSQL the RAND() function is evaulated once per query, not once per row, so SELECT * FROM somewhere ORDER BY RAND() would not have the desited effect (you can see why by running something like SELECT RAND(), * FROM some_table).

If you are using MSSQL (your question didn't state which DBMS you are targeting) and do not already have a clustered index on the table, and either have a sufficiently random column (a UUID column for instance) or could add one without upsetting the calling app, you could create a clustered index on that which would be quicker than the SELECT INTO / DELETE / SELECT INTO above. But again: this will have no effect at all if the app is explicitly asking for the results in a particular order and may not have any effect anyway otherwise.


Is there a way to automate this step?

Sure.  You can write a proc that uses sp_helparticle to compare the articles with the sys.tables, and then runs sp_addarticle to add articles for the new tables.

But there's no replication setting that will do this for you.


MySQL Replication ERROR 1130: error conecting to master

Check the quotes.
ON *.*

GRANT ALL PRIVILEGES ON . TO 'replication'@'localhost' WITH GRANT OPTION;


Is it possible to implement minimally logged reads from a disk table

We're going to have to move back to a disk table for now, and I'd like to not log anything to the transaction log for reads on this table.

Reads aren't logged because they don't change anything, so you already get what you want.


Two tables, same Attributes unable to count,

You could try to fetch all affected records of both tables using UNION ALL and then COUNT the records. (I'm not sure if you want to COUNT records or to SUM quantities.)

Notice you should add transaction_type to the GROUP BY clause.

If you want you can move the WHERE clause to the main query.

SELECT transaction_type, sku, COUNT(quantity_purchased) as SKU_ORDER
FROM   (
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'
        UNION ALL
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test1
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'
       ) t1
GROUP BY transaction_type, sku
ORDER BY sku;


That is a very denormalized design, as you have pointed out yourself.  Especially if there isn't always a 1 : 7 ratio of Phone Numbers to Flags.  Depending on your business data, you could potentially have a vast amount of sparse data.

I'd recommend a thin, normalized structure like this:

create table Flag
(
    pkFlag int identity(1, 1) not null primary key clustered,
    FValue varchar(100) not null
)
go

create table PhoneNumber
(
    pkPhoneNumber int identity(1, 1) not null primary key clustered,
    PnValue varchar(20) not null
)
go

create table PhoneNumberFlag
(
    fkFlag int not null foreign key references Flag(pkFlag),
    fkPhoneNumber int not null foreign key references PhoneNumber(pkPhoneNumber)
)
go

This allows you to create a one-to-many relationship (or possibly a many-to-many relationship, I can't say for sure as you haven't explained the meaning of Flags or any part of the domain).

A simple query to get all flags for a particular phone number would be:

select
    f.fValue
from PhoneNumber pn
inner join PhoneNumberFlag pnf
on pn.pkPhoneNumber = pnf.fkPhoneNumber
inner join Flag f
on f.pkFlag = pnf.fkFlag
where pn.PnValue = '555-555-5555'

As for the RDBMS you are looking to go with, that is very dependent on MANY factors.  What's your environment?  What do you already have?  MySql is a very different type, as it is open source.  Oracle, SQL Server, these are not.  I'm not sure about Oracle, but SQL Server has a free edition (SQL Server Express) that you could leverage, but it is limited.  RDBMS licensing can get costly very fast.  There are too many questions that need answering before a sound recommendation on that front can be made.


Referring to https://www.mysql.com/support/supportedplatforms/database.html the answer is no, AIX is not a supported operating system for MySQL database however you can install it through yum, for information on how to install yum on AIX refer to https://www.ibm.com/developerworks/aix/library/aix-toolbox/alpha.html

Concerning support you should address this with MySQL Technical Support (ref. https://www.mysql.com/support/) as they are the ones who would ultimately need to decide if they will provide support for your installation, which they may refuse on the basis that you are not operating their product on an officially supported / certified operating system. Concerning pricing, no, MySQL for commercial purposes is not free (ref. https://www.mysql.com/products/).


How to use values from table column as field names in SELECT query?

Assumptions and Clarification

Postgres 9.4
You want various counts from the same given table.

desc is a reserved word in SQL, don't use it as identifier. Using descr instead.

1. Basic query

The fastest and most elegant way to get multiple partial counts from the same table is a single SELECT statement with multiple aggregate functions using the FILTER clause:

Return counts for multiple ranges in a single SELECT statement

A statement like:

SELECT count(*) FILTER (WHERE size > width)    AS ct1
     , count(*) FILTER (WHERE size > height)   AS ct2
     , count(*) FILTER (WHERE width <> height) AS ct3
FROM   testtable;

Or, for Postgres 9.3:

SELECT count(size > width OR NULL)    AS ct1
     , ...
FROM   testtable;

2. Advanced query

To get the result in the form you want it, I counter-pivot with a VALUES expression in a LATERAL join, adding the ID and description id, descr in the process:

SELECT x.*
FROM  (
   SELECT count(*) FILTER (WHERE size > width)    AS ct1
        , count(*) FILTER (WHERE size > height)   AS ct2
        , count(*) FILTER (WHERE width <> height) AS ct3
   FROM   testtable
   ) t
, LATERAL (
   VALUES (1, 'size should not greater than width' , ct1)
        , (2, 'size should not greater than height', ct2)
        , (3, 'with should be equal to height'     , ct3)
   ) x(id, descr, ct);

Bold parts come from the errortable when building the statement dynamically:

3. Dynamic query

To concatenate the above statement dynamically from values provided in errortable:

CREATE OR REPLACE FUNCTION
SELECT 'SELECT x.* FROM (SELECT '
    || string_agg(
         format('count(*) FILTER (WHERE %I %s %I) AS c%s'
              , field1, operator, field2, id)
       , ', ')
    || ' FROM testtable) t, LATERAL (VALUES ('
    || string_agg(format('%s, %L, c%s', id, descr, id), '), (')
    || ')) x(id, dscr, ct)'
FROM   errortable;

format() with %I quotes column names where necessary and makes the statement safe against SQL injection - except for operator, which is concatenated as is. You might be able to make that safe, too, with the OPERATOR() construct ...

If untrusted users don't have write access to errortable, you can control its content and need not worry.

4. Full automation

Since the return type is uniform and well known, we can encapsulate it in a function:

CREATE OR REPLACE FUNCTION f_error_count()
  RETURNS TABLE (id int, descr text, ct bigint) AS
$func$
BEGIN

RETURN QUERY EXECUTE (
   SELECT format('SELECT x.* FROM (SELECT %s FROM testtable) t
                        , LATERAL (VALUES (%s)) x(id, dscr, ct)'
               , string_agg(format('count(*) FILTER (WHERE %I %s %I) AS c%s'
                                 , e.field1, e.operator, e.field2, e.id), ', ')
               , string_agg(format('%s, %L, c%s', e.id, e.descr, e.id), '), ('))
   FROM   errortable e
   );

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_error_count();

Simplifying the concatenation by one more step with an outer format(). Also note how all columns are table-qualified to avoid conflicts.
Again, since dynamic SQL is involved, make sure it cannot be abused for SQL injection.

SQL injection in Postgres functions vs prepared queries
Permission for sequence in another schema

SQL Fiddle for Postgres 9.3 since 9.4 is not available.


Do you know what pages are getting the latches? DBCC heavily uses tempdb as @ShawnMelton was alluding. Have you considered that tempdb contention could be the source of your pagelatches?  You should be able to investigate that with sp_whoisactive if you aren't already. How many data files do you have for tempdb? It sounds like you've only got one, try bumping it up to 4 and increase in intervals of 4 as needed, though 8 is often enough for most systems and many suggest starting with that number of data files. You'll also want to ensure the files are equally sized. You can also look at this post which has lots of info and links to reduce the impact of dbcc including a post from Paul Randall on running DBCC on a VLDB which might be helpful.


AS I explained on my previous answer you can use a variable to store last entry value.

In this case you need to count entries grouped by year and month.

SELECT Year, Month, Entry,
       IF(@last_entry = 0, 0, ((entry - @last_entry) / entry) * 100) "Growth Rate",
       @last_entry := entry
FROM
      (SELECT @last_entry := 0) x,
      (SELECT YEAR(leadDate) Year, MONTH(leadDate) Month, COUNT(*) Entry
       FROM   Lead
       GROUP BY 1,2) y;

This is the result:

| Year | Month | Entry | Growth Rate      |
|------|-------|-------|------------------|
| 2016 | 6     | 1     | 0                |
| 2017 | 3     | 1     | 0                |
| 2017 | 4     | 1     | 0                |
| 2017 | 5     | 2     | 50               |
| 2017 | 6     | 9     | 77,7777777777778 |

Rextester here


Scheduling jobs on a SQL Always On HA cluster

We create the jobs (and sync them as well) across all the instances that are part of AlwaysON AG.

There is an additional logic that you should put in the job to check if the instance is primary and then only run the job. A pseudo code would be like below :

-- http://dba.stackexchange.com/a/45152/8783
if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

To sync logins, jobs, etc, you can use PowerShell or this utility from SQLSkills (not sure if it works for 2014 !).


SQL Server 2008 Installation pointers for optimizing Performance and Scalability

It is SQL Server 2008 or SQL Server 2008 R2 ?? Because in your question you mention that client want to upgrade from 2005 to 2008 R2 and you're asking about migration from 2005 to 2008. So for sake of answer I am assuming it is 2008 R2

Since you will not be using other services like SSRS,SSAS and SSIS it should make you like a little easy. Some suggestions are,

Use separate physical drives for data and log files for all user DBs
Separate physical drive for temp db, backups,system dbs
Aligned drive partitions (however it should be taken care by itself since you are using Win 2008 R2 but better check 'em)
Setup proper auto increment based on your expected growth
Proper recovery mode (Full recovery mode is recommended)

Actually you can find loads of tips for SQL Server database server configuration. Following links can give you some help.
For SQL server 2005/2008 (but can be used for 2008 R2)
Or General MSDN help for SQL Server 2008 R2
Or google for "SQL Server best practices", which can be of different categories like installation, dev, security configuration, administration, migration etc. But I really doubt that you will find all-in-one type of solution somewhere because in most of the cases it really depends on your requirements.


Is it possble to replicate MySQL server on Azure virtual machines

For purposes of this discussion, there is only one way to replicate from one MySQL server to another, and the post that you linked to does seem to describe it correctly.

In this setup, the slave server needs to have the ability to establish a tcp connection to the master on port 3306. (The master never initiates this connection, so the connection only needs to be established from slave, to master.)

You can verify this on the slave at a command prompt:

C:\>mysql --host=ip.addr.of.master --port=3306 --user=username --password=password

The ip.addr.of.master should be the private IP of the master, and should be the same one you used in the CHANGE MASTER TO statement you executed on the slave.  You should also be able to use the username and password from the same statement to connect.

If this doesn't work, either, then the problem is probably not that you didn't follow the setup correctly, but is simply that the slave server's machine cannot reach the master machine over the network.

The steps to remedy this are going to be determined by the Azure setup, such as whether the two servers are in the same cloud service and virtual network, and whether there's a firewall on the master server that needs to be configured to allow access from the slave.

The command, above, should produce a numeric error code that you can translate into a descriptive error by using perror nnnnn command or by finding the code here.  I would expect, based on the description, that you will see 10060, connection timed out, which would be the strongest confirmation that what you have is a networking issue.


Datetime conversion fails when filtered index is placed on partition column

The error comes from converting the string 2000-01-20 00:00:00.000 (in the filtered index definition)

Changing the filtered index to use unambigous datetime format works.

create index IXF__test_of_filtered_idx__dt
  on dbo.test_of_filtered_idx
  (dt) include (id)
  where dt >= '19991210 00:00:00.000'
    and dt < '20000120 00:00:00.000' ;

It definitely seems buggy to me  that the filtered index range is interpreted according to the settings of the client and so the same date can be either included or not included in the filtered index dependant on the settings of the client at insert time (as in below demo)

CREATE TABLE T
  (
     dt DATETIME
  )

CREATE INDEX IXF__test_of_filtered_idx__dt
  ON T (dt)
  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';

SET LANGUAGE ENGLISH

GO

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan

GO

SET LANGUAGE RUSSIAN

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --Still 6th Jan

GO

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T

Returns two rows - both with the same date

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
| 1999-01-06 00:00:00.000 |
+-------------------------+

But

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T
WHERE  dt >= '1999-01-06'
       AND dt < '1999-02-06';

Uses the filtered index and just returns one of them

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
+-------------------------+

Running DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS at this point then fails for either language.

Msg 8951, Level 16, State 1, Line 4
Table error: table 'T' (ID 1045578763). Data row does not have a matching index row in the index 'IXF__test_of_filtered_idx__dt' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 4
Data row (4:24:1) identified by (HEAP RID = (4:24:1)) with index values 'dt = '1999-01-06 00:00:00.000' and HEAP RID = (4:24:1)'.
DBCC results for 'T'.
There are 2 rows in 1 pages for object "T".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'T' (object ID 1045578763).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.T).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


How to GRANT or DENY access to an object based on metadata for that object in SQL Server?

is my only option to periodically run a job that reads the metadata and updates permissions?

You could alternatively use Row Level Security to enforce security predicates dynamically at runtime.  But that has additional complexity and runtime cost.

Do you foresee any problems with this method?

For those roles you should not mix manual changes to the permissions and permissions generated based on your metadata.


Identity column jump in sql server restart

If you want something to run on server startup you can create a stored procedure in the master database and mark it as a startup stored procedure as long as the scan for startup procs configuration option is set. No need for powershell or batch files.

  I have come up with one solution for which I am not sure - is it good
  or not?

To determine whether it is a good solution we need to know the problem it is intended to solve. It turns out there is no problem to solve so no this is certainly not a good solution. If it ain't broke don't fix it.

Identity columns have never guaranteed a gapless sequence. If you in fact need one then you would need to roll your own incrementing solution that blocks other transactions until each allocated range has been successfully committed.

The code you posted will reseed all identity columns to 0 and then go through and reseed them all using the maximum value in the identity column? What happens if someone tries to insert rows into a table processed by the first step but not yet processed by the second? They will likely get a duplicate key violation as the insert tries to reuse identity values that have already been allocated to other rows in the table.


How to access external DLL's static methods that incorporate output parameters in SQL Server

What have you tried and what error are you getting?

The following works for me on SQL Server 2008 R2.

C#:

namespace CLRTest
{
    public static class ParamsTest
    {
        [SqlProcedure]
        public static void Test(out int p)
        {
            p = 5;
        }
    }
}

SQL:

CREATE PROCEDURE dbo.Test(@p int OUTPUT)
    AS EXTERNAL NAME [CLRTest].[CLRTest.ParamsTest].[Test];
GO

DECLARE @x int;

EXEC dbo.Test @p = @x OUTPUT;

SELECT @x;

Returns 5 as expected.

Now, if you're trying to return an array in an output parameter, I don't think that's supported, save for the exceptions noted here. If that's what you're looking to do, use a table-valued function or stored procedure to return a result set. Note also that table-valued (input) parameters are not supported as mentioned here, assuming you're using SQL Server 2008 or higher.


How to execute some script when database starts up

[Updated]

Create SQL Agent job for what you need.
Use Server-scoped DDL trigger for that

Approximately like this:

Create TRIGGER ddl_trig_AlterDatabes
ON ALL SERVER
FOR ALTER_DATABASE
AS
    -- your code here
    -- use EventData() to check database name if needed.. or other info..
    Exec msdb..sp_start_job @job_name = 'job_name'


Completely remove additional data file from SQL Server 2012 DB

Looks like changing files from one file group to another is not
  possible?

Correct, you would need to create a new file on the correct filegroup and recreate all your objects on the correct filegroup (if any existed) to move them, as you cannot move a file between filegroups.

  The data file is empty so I tried removing the file using alter DB
  remove file, when I run DBCC showfilestats, the file is gone, but when
  I check SYS.master_files, I can see still the entry of the file I want
  removed. Is there a way to remove it completely, even deleting it
  physically from the server as I need to recover the space?

I've seen this before in SQL Server 2012. If you query sys.master_files you will see the drop_lsn field has an LSN value in it for this file. This indicates the LSN at which this file was dropped. You need to perform a log backup to clear this value, then it will disappear from the DMVs.


Why disabling a clustered index makes the table inaccessible?

why isn't it possible to access the data directly from the table discarding the B-tree?
(most likely by scanning the table row by row)
wouldn't that be more appropriate than inaccessible data at all?

To answer your question, Indexing basics comes more handy -- An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. For more details refer here.

Also, as many people have described, Clustered Indexes == Original tables which are physically ordered with one or more keys or columns. So, when a clustered Index is disabled, its data rows cannot be accessed. You wont be able to Insert any data (for Non Clustered Index the Insert will succeed -- but that is not entirely related to this post -- as here the discussion is of Clustered Index) as well or neither Reorganize operation will work.

Below will explain you in detail :

we will use Adventureworks database to see the effect of disabling the CLUSTERED Index.

Now check the row count in the table:

Now disable the Clustered Index

Now select the row count from the table. This time it will error out with below message:

Even the reorganize operation does not work !!

Now rebuild the Clustered Index and it should work fine.

Select the table to see if we can access the data

So the bottom line is that, if we disable the Clustered Index, then Data in the table still exists, but will not be accessible for anything other than Drop or REBUILD operations. All related Non-clustered Indexes and views will be unavailable as well as Foreign Keys referencing the table will be disabled and there by leading the FAILURE for all the queries that are referencing the table.

Note: There is no option to ENABLE the Index. You have to REBUILD it.


How to/Where to find MySQL templates for Toad Data Modeler

You should use MySQL Workbench for work with MySQL. It is an excellent, free and Open Source tool for working with MySQL databases. As well as that, there isn't a one-to-one correspondence between Oracle and MySQL datatypes. Some samples/examples are available here.

[EDIT] Ooops - just realised that there is Toad for MySQL, so presumably the data types will be appropriate.However, I would just suggest that the best people to make admin tools for a given database are the actual manufacturers of that database - Toad started as a specialised Oracle product and probably still carries some baggage from those origins, whereas MySQL Workbench has always been a specialised MySQL tool.

Having said that, compiling it from source is a tricky business with numerous components and unless you're a guru, I'd use the pre-compiled version. If you are interested, you might like to look at this site which is a kind of MySQL Workbench fanzine. This site is also of interest to those working with MySQL.


benefit of using [md5] over [trust] authentication for local Unix sockets

For Question 1: using md5 auth for local connections is particularly helpful when you have a multi-user machine, or if you might add additional user accounts for friends/family/coworkers/whoever someday.

If you are and will remain the only actual user on your system, there is less benefit, but it could still be useful: suppose some bad guy manages to break into some system account like 'www'. He may not be able to do a whole lot from that account without root privileges. But if you've left your pg_hba.conf open to 'trust' all local users, the bad guy can do anything to your database, probably including taking over the 'postgres' user account as well, and moving on to attempt to root your system, steal your data, or whatever from there.

For Question 2: You could use a .pgpass file with, say, only a 'readonly' user's credentials, so if a bad buy took over that account he wouldn't have superuser-level access to the database. If you do store the password for the postgres user in your ~/.pgpass, then yes, it would be "game over" for your postgres instance if a bad guy took over that local user account. Again, that documentation was written with the possible use-case of a multi-user machine in mind, e.g. different local users might have or use various Postgres user accounts, not necessarily superuser accounts.


SQL query not displaying a row when one of the variable gets 0 values

Your problem is that you are inner joining both subqueries together, meaning that if there are no rows for the join condition (in your case, p.sender_mark = b.sender_mark) in one of the subqueries, you won't get a row in the results for the row in the other subquery that does exist.

You could get around this by turning the join into an outer join, but (IMO) the better option is to use conditional aggregation, like so:

SELECT sender_mark,
       ROUND(removed * 100 / total_trains, 2) trainremovals
FROM   (SELECT sender_mark,
               COUNT(DISTINCT CASE WHEN removed_from_dispatch_ind = 'Y' THEN train_id END) AS removed,
               COUNT(DISTINCT train_id) AS totaltrains
        FROM   bulletin.dispatch_train
        WHERE  sender_mark IN ('BNSF', 'NS', 'UP', 'METR', 'BRC', 'CPRS', 'CSXT')
        AND    modified_ts >= SYSDATE - 7
        GROUP  BY sender_mark);

Aggregate functions (including COUNT, except for when you do COUNT(*)) nearly all exclude null values from their calculations. Therefore by outputting NULL values if a condition is not met, you can get COUNT to ignore those rows.

Doing it like that means you are now only querying the table once instead of twice, which is nearly always going to be more performant.


Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

There is no appreciable difference in performance when the underlying hardware is the same, assuming we're talking about a single physical disk.

However, separate logical drives might help you maintain your sanity, and could result in lower physical fragmentation of the SQL-related volumes


What is the impact of setting "max text repl size (B)" to -1?

What is the downside of setting "max text repl size (B)" to -1?

I would not set the value to -1 (limitless). I would instead find out the max datalength of your lob data and set it accordingly with a 10% buffer. This way you have more control of what and how much gets into distribution.

I can see that in an unlikely situation, setting of -1 can cause high network latency if the blob data to be replicated is of a much larger size.


Tempdb is getting full very quickly in microsoft sql server 2008

Tempdb is growing like this because something is using it.  No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first.  This alone may resolve the whole issue and remove your need to do anything else with tempdb.  In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).


combine rows from table with NUM columns

Here's one way.

SELECT Product, MAX(Qtdy), MAX(Price)
FROM table
GROUP BY Product

The better answer is to clean up your data so it isn't ditry.


How to generate create table script without primary and foreign key in Oracle

This will do the trick :

EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL


Database explorer disabled in SQL Assistant

once you are connected to the DB on the left side of the window you will see the DB tree hierarchy.

Let me know if this helps.


Modification_Counter influence the Cardinality Estimator?

How does the modification_counter influence the CE to take a different execution plan?

One way this can happen is that the new CE in SQL Server 2014 may interpret statistics differently depending on how many modifications have occurred on that column since the last statistics update. From Seek and You Shall Scan Part II: Ascending Keys by Itzik Ben-Gan:

  The new cardinality estimator identifies that the query filter exceeds the maximum value in the histogram. It has access to the column modification counter (call it in short modctr), so it knows how many changes took place since the last refresh. When the column is unique and of an integer type or numeric with a scale of zero, SQL Server assumes it’s ascending. So it interpolates the estimate based on the distribution of the values within the existing histogram and the number of changes that took place since the last refresh.

I cannot say if that behavior is leading to your issue. You could try running both queries with trace flag 9481 to see if your issue is caused by something new in the 2014 CE:

OPTION (QUERYTRACEON 9481)

You can also use query hints to force what you think is the better plan. Comparing the hinted plan with the unhinted plan may give you a clue as to why SQL Server is choosing the different plan.


Error 29527: While upgrading from SP1 to SP4 in SQL Server 2005

As per logs you posted

Products Detected                         Language  Level  Patch Level       Platform  Edition
Database Services (ALCSQLMFG)             ENU       SP1    2005.090.2047.00  x64       STANDARD
Database Services (MSSQLSERVER)           ENU       SP4    2005.090.5069.00  x86       STANDARD
Reporting Services (MSSQLSERVER)          ENU       SP4       9.00.5069.00  x86       STANDARD
Notification Services                     ENU       SP4       9.00.5069.00  x86       STANDARD
Integration Services                      ENU       SP4       9.00.5069.00  x86       STANDARD
SQL Server Native Client                  ENU                 9.00.2047.00  x64
Client Components                         ENU       SP4           9.4.5069  x86       STANDARD
SQLXML4                                   ENU                 9.00.5000.00  x64
Backward Compatibility                    ENU                    8.05.2312  x64
Microsoft SQL Server VSS Writer           ENU                  10.3.5500.0  x64

If you see it you have two instances installed

ALCSQMFG instance is at patch level 2047 and is 64 bit standard edition
MSSQLSERVER instance is at patch level 5069 and is 32 bit Standard edition.

Further if you go down the logs you can see message

Database Services (MSSQLSERVER)           Setup cannot update Database Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Reporting Services (MSSQLSERVER)          Setup cannot update Reporting Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Notification Services                     Setup cannot update Notification Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Integration Services                      Setup cannot update Integration Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Client Components                         Setup cannot update Client Components x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.

Now the message is clear you are trying to update MSSQLSERVER which is already at SP4 level and is 32 bit. You are using 64 bit installable to update 32 bit to SP4 which is already at SP4. Seems like you are confused with instances.

What you have to do is

You need to update instance ALCSQMFG which is at SP1 level to SP4 and since because this instance is 64 bit you need to run 64 bit installable. What mistake you are doing is you are running 64 bit installable but you are selecting wrong instance you have to select ALCSQMFG instance and then proceed with upgrade


Should I write to MySQL slave (replica) for reporting?

I would do the nightly aggregation into a Summary Table on the Master.  This abides by the principle that the Master and Slave are always in sync (within a slight delay).  It also allows for multiple slaves to share the read load.


Can't empty all my database in SQL Server

I've had a similar issue to this before, the issue is that you need to fully drop the FK constraints rather than disabling them. I used this code to convert non clustered primary keys to clustered ones but it'll work for this too (I've commented out the EXEC statements because somebody's going to mess up a system if I leave them in, try this as is first and then check the messages tab to see what it would actually do before running it in anger);

First query is going to put all of our Foreign Key information into a temporary table;

SELECT
     o.name [ReferencedTable]
    ,OBJECT_NAME(fkc.parent_object_id) [FKTable]
    ,c.name [FKColumnName]
    ,fk.name [FKName]
INTO #FKAgainstTableList
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id
    AND c.column_id = fkc.parent_column_id
INNER JOIN sys.objects o ON fkc.referenced_object_id = o.object_id

This is fine, but what if we've got foreign keys that reference more than one column? Let's allow for that by making a new table, it makes sense to put in our square brackets while we're doing this;

SELECT DISTINCT
     '[' + a.ReferencedTable + ']' ReferencedTable
    ,'[' + a.[FKTable] + ']' FKTableName
    ,STUFF([ColumnName],1,1,'') FKColumnName
    ,'[' + a.[FKName] + ']' FKName
INTO #SanitisedFKList
FROM #FKAgainstTableList a
CROSS APPLY
(
    SELECT ',[' + b.FKColumnName + ']'
    FROM #FKAgainstTableList b
    WHERE a.FKName = b.FKName
    FOR XML PATH ('')
) sub (ColumnName)

We'll need some variables for the cursors coming up;

DECLARE @sql varchar(max)
DECLARE @ReferencedTable varchar(max)
DECLARE @table varchar(max)
DECLARE @FKColumnName varchar(max)
DECLARE @FKName varchar(max)

Now let's make a cursor to drop all of these Foreign Keys (i like to keep the PRINT statements so I can see what's happening in the messages tab);

DECLARE cursor1 CURSOR  FOR
SELECT * FROM #SanitisedFKList

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql ='ALTER TABLE '+@table+' DROP CONSTRAINT '+ @FKName
        PRINT @sql
        --EXEC(@sql)
        FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor1
DEALLOCATE cursor1

Then you can do your original truncate table statements without worrying about those FK's.

When you want to reapply them you'll need that temp table and do something like this;

DECLARE cursor2 CURSOR  FOR
SELECT  * FROM #SanitisedFKList
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE '+@table+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY('+@FKColumnName+')
        REFERENCES '+@ReferencedTable+' ('+@FKColumnName+')'
        PRINT(@sql)
        --EXEC(@sql)
        SET @sql = 'ALTER TABLE '+@table+' CHECK CONSTRAINT  '+@FKName
        PRINT(@sql)
        --EXEC(@sql)

        FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor2
DEALLOCATE cursor2


Rows_affected is set
Errors could occur

But there is no "datatype" being returned.  SELECTs return resultsets; writes do not.


How to specify SQL startup parameters in quiet installation via command line

If you go just over half way down on this link you will see the startup parameter for trace flags.

You can add them in the configuration manager here:

In this case it would be -T272 with the capital T.  Per the link the lower case t is for specific flags used by the support engineers.

EDIT  @MaxVernon pointed out I'd missed the point of the question :)
Shawn Melton has a post here were he discusses what you are trying to do.  He has a script that you can run to add startup parameters.  Mike Fal then wrote a followup here where he shares a function he wrote called Set-SQLStartupParameters that is supposed to be somewhat safer than Shawn's method.

Here is Shawn's code:  (Mike's requires a download from GitHub so you can follow the links if you'd like that one.)

$server = 'MyServer'
$sqlservice = "MSSQLSERVER"
$sqlagentservice = "SQLSERVERAGENT"
$flagsToAdd = ';-T1117;-T1118;-T3226"

Add-Type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
$sqlwmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server
$wmisvc = $sqlwmi.Services | where {$_.name -eq $sqlservice}
$wmisvc.StartupParameters = $wmisvc.StartupParameters + $flagsToAdd
$wmisvc.Alter()

$wmisvc.Stop()
Start-Sleep -seconds 15
$wmisvc.Start()

$wmiAgent = $sqlwmi.Services | where {$_.name -eq $sqlagentservice}
$wmiAgent.Start()


You haven't really given enough information about the original execution plan, what it changed to or what columns you put in the new NCI.  It is possible that the query performance can be improved even further.  The cost of the new index is the initial build, the additional processing of entries on INSERT and UPDATE and potentially the fragmentation maintenance.  Also, it's possible it may require some statistics maintenance if the data profile is likely to change.

It is possible that the index could improve overall system performance by reducing read access to the clustered index, but with a table changing so rapidly, the cost of maintaining the index might be very high.  On the other hand, you have to look at the overall requirements.  If this query only runs once a month, perhaps it is too high to bear.  If it runs 10 times a hours, then running it in a few seconds, with inserts slightly slower might be a small price to pay.

A thing to look out for would be index fragmentation if there are a lot of updates because the NCI key is not static.


MySQL: Does the default value checked by the `CHECK` constraint?

Checking default value doesn't make sense, as you have declared the static value which must be correct only. But the values which are going to be inserted manually are not checked using CHECK constrainst.
CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),

    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);

See here : the value like  hps://sdfs.com , asdfas://sdfs.com are not supposed to insert but they are inserted successfully.
insert into Company(ACCOUNTID , Name )  values ('12','asdf');
insert into Company(ACCOUNTID , Name , Website)  values ('13','asdf',"hps://sdfs.com");
insert into Company(ACCOUNTID , Name , Website)  values ('1','asdf4',"asdfas://sdfs.com");

select * from Company ;

Output :

# AccountID, Name, Website, Categories
1, asdf4, asdfas://sdfs.com, []
12, asdf, https://sample.com, []
13, asdf, hps://sdfs.com, []

Apart from using CHECK constraint I would suggest to use Triggers for validating this values or using JAVASCRIPT to valid this values.


11g
Oracle recommends that we configure the EXTPROC by altering the extproc.ora file rather than configure the listener for Oracle 11g.
 The extproc.ora file is located in the ORACLE_HOME\hs\admin directory.


How does SQL Server control which users get automatically added to a new database?

You have a user defined in the model database. Good odds someone mis-clicked once upon a time and created the spurious user in model accidentally. model is the template for any databases created on an instance, so if a user exists there it will exist in all databases you create.

Run the script below (on a development instance!) and you'll see a user created in model is added to the TestForModelUser database.

USE [master]
GO

CREATE LOGIN [ModelUser] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [model]
GO

CREATE USER [ModelUser] FOR LOGIN [ModelUser]
GO

CREATE DATABASE TestForModelUser
GO

USE [TestForModelUser]
GO

SELECT * FROM sys.sysusers WHERE [name] = 'ModelUser'
GO

USE [master]
GO

DROP DATABASE [TestForModelUser]
GO

USE [model]
GO

DROP USER [ModelUser]
GO

USE [master]
GO

DROP LOGIN [ModelUser]
GO


Can I use MariaDB as a replication slave for MySQL?

Yes, this is possible, and probably makes sense. See e.g. Replicating from MySQL Master to MariaDB Slave. So with MySQL 5.5 as the master, you can use MariaDB 5.5 or later as the slave.


If the cast of the column is to exactly the same datatype and length and the seek predicate is a literal it does indeed seem to disregard it or treat it as a no-op and does an index seek on equality.

Seek Keys[1]: Prefix: [tempdb].[dbo].[#test].name = Scalar Operator(N'rpc')

If the cast of the column is to the same datatype but greater length and the seek predicate is a string literal it causes an index scan. This is obviously to be avoided.

If the cast of the column is to the same datatype and the same or greater length and the seek predicate is a local variable it adds a compute scalar operator to the execution plan. This  calls GetRangeThroughConvert and outputs a range.

This range is used to do an index seek and seems pretty efficient

Seek Keys[1]:
Start: [tempdb].[dbo].[#test].name > Scalar Operator([Expr1006]),
End: [tempdb].[dbo].[#test].name < Scalar Operator([Expr1007])

Testing Code

SELECT *
 INTO #test
  FROM [master].[dbo].[spt_values]

CREATE NONCLUSTERED INDEX [ixname] ON #test
(
    [name] ASC
)

DECLARE @name NVARCHAR(MAX)

SET @name = 'rpc'

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))= @name --Cast the same and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))=@name --Cast to longer and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))='rpc' --Cast the same and literal

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))='rpc' --Cast to longer and literal


Can this be achieved with replication?

Yes, Transactional replication can be used in your case.

  Can we get subsets of data from a subset of tables from the source, into our new replicated database?

Yes you can just replicate the tables (articles) that you want along with its subset of data.

e.g. where PersonTypeId = x --> You need to use static row filter as it uses a WHERE clause to select the appropriate data to be published.

selecting specific articles (tables) :

Filtering what data to publish :

  Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?

No. See above answer to use static row filter when you are publishing the articles.


How can I select data size group by id for every value in a table

It seems that you need in
SELECT idMsg, SUM(LENGTH(txBlob)) AS total_size_in_bytes
FROM sat_msgBlob
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02'
GROUP BY idMsg

Then, after receiving the output into the client you may format it to Mbytes. Or divide the sum in the query to 1048576 and cast to decimal with needed fractional part length.


DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID,
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND)
WHERE NOT EXISTS
(
    SELECT 1
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            )
            OR
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            )
            OR
            (
                [tInner].[Users_ImageObjectID] IS NULL
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.


Associative property inside functional dependancy

Yes.  AB in AB --> C really means A and B functionally determine C.  And, which is the truth functional operator of conjunction, is associative.  Section 4 of the linked article presents the algebraic properties of conjunction.  Therefore A and B is equivalent to B and A.


How to access user information via sys.fn_dump_dblog

By reference to @Kin, I have come to the solve with the query.

SELECT
    SUSER_SNAME([Transaction SID]) UserName
    ,[Current LSN]
   ,[operation]
   ,[Context]
   ,[Transaction ID]
   ,[Transaction Name]
   ,[Begin Time]
   ,[End Time]
   ,[Flag Bits]
   ,[PartitionID]
   ,[Page ID]
   ,[Slot ID]
   ,[RowLog Contents 0]
   ,[Log Record]
   ,[RowLog Contents 1]
   INTO ##temp
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\balbla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (([operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT'))
OR ([operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))))
OR ([operation] = 'LOP_HOBT_DDL'))

SELECT t2.UserName,t.operation,object_name(p.object_id),count(1) FROM ##temp t
LEFT JOIN ##temp t2 ON t.[Transaction ID]=t2.[Transaction ID] AND t2.UserName is not null
INNER JOIN sys.partitions p ON p.partition_id=t.PartitionId
WHERE t.[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
GROUP BY t2.UserName,t.operation,object_name(p.object_id)


Link DAX OpenQuery Output to SQL Server Temp Table

If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]')
-- Result: [[awful column name]]]

Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets.

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]


I think you are making a fairly common mistake here by selecting NoSQL technologies in order to be "future-proof" without understandng the tradeoffs.  If in doubt start with PostgreSQL and figure you can always set up a graph db or other NoSQL db on the side as you need it.  Also you can do graph traversal in PostgreSQL but keep in mind you are working with sets and so that's very, very different from a graph db with different benefits and drawbacks.

Your basic tradeoff is between flexibility of data entry and an ability to flexibly utilize data on output.  NoSQL db's (including graph db's) sacrifice the latter for the former and because very often you need an ability to run ad hoc queries on sets of data you probably want an RDBMS somewhere.  This means if you have a good data model, you should be able to add adjunct db servers to your environment for special purposes of your RDBMS won't handle it.

Nonetheless, there are a couple specific reasons why if you are thinking at some point of adding various NoSQL solutions why I would recommend starting with PostgreSQL instead of MySQL and these have to do with performance of NoSQL-friendly database structures and the fact that PostgreSQL supports WITH RECURSIVE common table expressions.  This approach (WITH RECURSIVE CTE's) allows things like hierarchies and graphs to be traversed using recursive SQL statements which can repeatedly traverse paths.  These are also relatively efficient for a set-based approach, and so if you end up with a lot of part/subpart modelling problems you may be able to do that directly in PostgreSQL without too much work.  If this doesn't work however, you can also more easily represent the data in a way which is easier to import into your NoSQL db.  So it gets you further and it integrates better.  You may even be able to set up foreign "tables" against your NoSQL db and run queries against it from inside Pg!

More on the Tradeoff 1

Graph databases arose from a need to do binary traversal quickly over large sets of information.  The obvious example might be a social network site like LinkedIn which might want to tell you quickly how far removed you are from another user (in simple terms this means essentially they are designed to play "six degrees of Kevin Bacon").  Typically on a graph database you are essentially traversing nodes using what are often described as "three word sentences" which represent the graphs.  In this regard you are looking at essentially something like "John friended Jane" and traversing this way.  Typically the API is relatively navigational.

A relational database is really designed to work with sets of information.  Typically when giving up on relational databases, one is giving up on set operations too.  This can be a big deal.  Often it is faster both operationally and in terms of development time to be able to do set operations rather than navigate, aggregate, and report.  This is a huge difference and if your use case fits a relational workflow, I have trouble imagining that a graph db will help you.

More on the tradeoff 2:  Consistency Models

A second thing to investigate and keep in mind is the question of what consistency model your database uses.  Even with "ACID-Compliant" RDBMS's there is a range of consistency models based on different levels of transaction isolation that can be used to prevent problems.  If your data is important, the standard RDBMS consistency models are going to fare much better than most of the NoSQL models because they make more guarantees both to the DBA and to the application.  Graph databases are a relatively large field, and each vendor may have some differences in their consistency model.  This isn't necessarily something that will rule the use out, but it is something to be cautious about when looking at the solutions as a whole.

Note that schema flexibility is both a blessing and a curse in the NoSQL world and this also affects graph databases.  It is a blessing because you can get up and running in the initial stages faster, but it is a curse because the set operation strengths of an RDBMS depend on a fixed schema, and those that relax that to some extent (Informix, for example, supports jagged rows in return sets) require programmer knowledge of the places where those requirements have been relaxed.


Trying to get DB2 to enumerate databases

You will have to have admin rights on your computer and the use these instructions to create DAS:

  Enter db2admin create. If a specific user account is desired, you must use /USER: and /PASSWORD: when issuing db2admin create.

You can then see current config with command:

db2 get admin cfg

and update it with

update admin cfg

command.

Sources:

[1] http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.gui.doc/doc/t0004960.html

[2] https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.gui.doc/doc/t0005173.html

[3]
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002023.html


The reorg is necessary when the table content has changed (lots of inserts, lots of updates, many deletes, a combination of all of that) and you can query which tables need reorganization via 'REORGCHK'

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0001971.html


Why is restoration from binary logs so slow?

You must understand what the binary logs are: they store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded. So, your command:

mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname

basically executes all insert, updates & deletes that occurred to the server during those almost 30 binlogs, serially. That is why recovering from the binary logs is only useful for point in time recovery (recovering the latest changes since the last backup).

There are some things that you could do to reduce the application time, like reducing temporarily the durability and consistency constraints of the database (as you can always replay the steps) -innodb_flush_log_at_trx_commit, disable the binary log, the double write, etc.- or try using ROW binary logs only, which may increase the size on disk but make them faster to apply.

Alternatively, you could reduce the number of binlogs that you have to apply by creating more frequent full backups (the size of your full backups is not precisely big), creating real incremental or differential backups with tools like xtrabackup- or using lagged slaves for disaster recovery. This is because both mysqldump and mysqlbinlog recover data in a logical way- physical backups may be faster in some cases, specially for full recovery.

Obviouslly, there could be specific issues as well, for example, I have sometimes found that replaying LOAD DATA statements is slower than I would have thought.


SQL Server 2012 Browser Service doesn't appear

The Log you posted seems incomplete, also it is not the log for SQL Server installation but for patch update. You can see in the log

Requested action:   Patch

Anyways log is helpful because it shows below

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
  SQL Server 2012                                                          Management Tools - Basic                 1033                 Express Edition      11.0.2100.60    No
  SQL Server 2012                                                          LocalDB                                  1033                 Express Edition      11.1.3000.0     No

Whenever you run patch SQL Server writes in Log what all instances and features it has found on the current system and as per log you only installed

SQL Server management studio
Local DB not Database engine.

You need to download ENU\x64\SQLEXPR_x64_ENU.exe because your system is 64 bit from This Link

I am not sure error is because of missing Browser or missing Express Database Engine service. Browser service is installed with SQL Express database engine so if you are looking to connect to database engine not Local DB you need to to install Express DB engine.


The auto_explain feature seems to fit this use case, if you can take a brief service outage to enable it. It logs a plan for any statement exceeding a time threshold whether or not it has completed yet. It can also log plans before execution starts, so you can even get the plan of a statement that blows out memory!


Query Spatial Data for Nearest Neighbor for each group

I'm going to go out on a bit of a limb here and guess what you are trying to achieve.

I suspect that you want to find the nearest neighbour from within the DS1 table for all the rows in DS1.

For a test set I created the following randomly filled table.

-- Test table
CREATE TABLE DS1 (
  MSPID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Location Geometry NOT NULL
);

-- 1 million random points
INSERT INTO DS1 (Location)
SELECT TOP 1000000
  Geometry::Point(
    RAND(CAST(NEWID() AS VARBINARY))*100000,
    RAND(CAST(NEWID() AS VARBINARY))*100000,
    0) Location
FROM TALLY;

CREATE SPATIAL INDEX DS1_SIDX ON DS1 (Location)
USING GEOMETRY_AUTO_GRID
WITH (BOUNDING_BOX =(-15000, -15000, 2015000, 2015000));

I a version of your initial query for a quick test, but unfortunately on my desktop the optimiser decide that parallel was better and ignored the spatial index resulting in a 3 second query.  Restricting it to a single core caused it to use the index and returned in milliseconds.

-- Nearest neighbour test
DECLARE @Location Geometry = Geometry::Point(50000,50000,0);

/* Ignored index
 SQL Server Execution Times:
   CPU time = 21781 ms,  elapsed time = 3805 ms.
*/
SELECT TOP 1
  MSPID,
  Location.STDistance(@location) Distance
FROM DS1
WHERE Location.STDistance(@location) IS NOT NULL
ORDER BY Location.STDistance(@location)

/* Used index
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.
*/
SELECT TOP 1
  MSPID,
  Location.STDistance(@location) Distance
FROM DS1
WHERE Location.STDistance(@location) IS NOT NULL
ORDER BY Location.STDistance(@location)
OPTION (MAXDOP 1);

To achieve what you want for a single row from DS1 and assuming that you want the nearest row from DS1 to the one you've picked, the following uses the index for me and again works in milliseconds.

-- For a specific row using cross apply
DECLARE @MSPID INTEGER = 500000;

SELECT
  a.MSPID FromMSPID,
  b.MSPID ToMSPID,
  b.Distance
FROM DS1 a
  CROSS APPLY (
    SELECT TOP 1
      MSPID,
      c.Location.STDistance(a.Location) Distance
    FROM DS1 c
    WHERE c.Location.STDistance(a.Location) IS NOT NULL AND
      c.MSPID <> a.MSPID
    ORDER BY c.Location.STDistance(a.Location)
    ) b
WHERE a.MSPID = @MSPID
OPTION (MAXDOP 1);

This can be made to go through the entire table, but it will take some time to complete as it needs to find the nearest neighbour for each one.  It took ~1 minute for 10,000, so for the full million that would take almost 2 hours.  I have found that restricting this query to a single core isn't required and allowing parallism will improve performance.  For me it halved the time.  I would also recommend that you have a look at this question and it's answer for other performance considerations.

/* For all (well 10,000) using cross apply
 SQL Server Execution Times:
   CPU time = 60641 ms,  elapsed time = 64545 ms.
*/
SELECT TOP 10000
  a.MSPID FromMSPID,
  b.MSPID ToMSPID,
  b.Distance
FROM DS1 a
  CROSS APPLY (
    SELECT TOP 1
      MSPID,
      c.Location.STDistance(a.Location) Distance
    FROM DS1 c
    WHERE c.Location.STDistance(a.Location) IS NOT NULL AND
      c.MSPID <> a.MSPID
    ORDER BY c.Location.STDistance(a.Location)
    ) b
OPTION (MAXDOP 1);


Linked server returned message "Query timeout expired."

This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes.

You mention:

  The remote timeout on the linked server is set to 0.

Intuitively, this might seem like there is no limit.

What it actually does is use the sp_configure default for remote query timeout, which is 600 seconds (10 minutes).

Setting the query timeout on the linked server to a higher value, perhaps 1200 seconds (20 minutes), will likely allow your job to complete.  And hopefully job completion will provide some insight into why it's taking so much longer on this specific day.

I think the setting is a little confusing, as discussed another question here on the site: Linked server connections to Multi-subnet failover cluster


Storage perspective Best Practices :

Have separate drives for Data,Log and Tempdb.
Do not keep the log files and Tempdb files on same drive as Tempdb is very active and needs good IOPS all the time. So add another drive for Tempdb.
If you can, keep data files too in SSD. This will enhance your performance in case the db is very read/write intensive.
Enable Instant File Initialization.

Also I want to ask that for single production database how much Datafile/logfile need to create? 1 datafile and 1 logfile is best practice?

Adding multiple log files does not help or improve the performance as
the logging is done sequentially. Paul Randal has a great blog article where he proved why multiple log file is bad for you.
On data files, having one mdf file is good enough. If your database will be huge then later go for filegroup partition. There may or may not be any improvement in performance if you add multiple datafiles for user databases which is totally unpredicatable.Another blog from Paul Click here where he answers this. In my environment we only use single datafile unless we run out of space and an extra .ndf needs to be added directing data to another drive.
Below is a snippet from Microsoft Documentation :

Storage
Use different disks or disk partitions for the following:

Windows operating system.
Data files for the system databases.
Log files for system and user databases.
Data and log files for the TempDB database.

For optimal read/write performance, make sure that disks that are used for SQL Server data files are formatted using 64 KB block size.

Microsoft Documentation


How to model the relationship between these three tables

I personally would not design it so that I had to create new tables all of the time. As you already track the user_id in the meals table, I would just add another column for the date to it and remove the day table altogether. I would advise to not make the date_time attribute as the primary key though because it is highly probable that more that one person will want to eat on the same day...

You also have a redundant attribute user_user_id in the meals table because it is already included as user_id.

The user_name field does not belong in the meals table because it is an attribute of a user. You can however include the user_name in the query used to obtain meals items like so:

SELECT `date_time`
     , u.`user_id`
     , u.`user_name`
     , `calories`
     , `fat`
     , `carbohydrates`
     , `carbs_which_sugar`
     , `fibre`
     , `protein`
  FROM `meals` AS m
  JOIN `users` AS u ON u.`user_id` = m.`user_id`;


What I would do is set it up with sufficient partitions so that your top (MAXVALUE) partition never gets used. In this way when the time comes to add the next partition, you are going to re-partition the 'empty' MAXVALUE partition which should be a lot quicker.

So you would start by adding a partition for VALUES LESS THAN (2300000) which replaces the MAXVALUE partition.

Then when you get close to 2300000 entries, you would add a new partition for VALUES LESS THAN (2350000). And because you would be splitting the empty MAXVALUE partition it will be relatively quick.


How do you generate large OLTP and reporting workloads?

I think the TPC-H benchmark (or TPC-DS) might be a good start. The council publishes the tool to generate data and the workload.


Multiple counts in one table output should be one table

Syntax error, remove the ( and ) from the parent query, do away with the outer FROM and the extra count's. That is,

SELECT
   (SELECT count(TXN)
        FROM sale1
        WHERE DATE(SALEDATE)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
   ) AS c1,
   (SELECT count(TXN)
        FROM sale1
        WHERE RESPONSE='Y'
        AND DATE(SALEDATE)=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
   ) AS c2;

Note count of count i.e. count(count(*)) will normally give you 1.


I also considered a trace, but it can be easly stopped or it will not
  restart at server start.

You can have a server side trace as a SQL Agent Job and set up the job to start whenever SQL agent start and a second schedule to start every xx number of minute.  That way job will always start and second schedule will take care if somebody stop it by mistake.

  Is the trigger the wrong way? Can I fix this in some way?

I suggest you create a database role and make all the users member of that role. I understand your concern of forgetting to add users to the role or assign permission. I personally do not prefer to have too many users with privilege to change schema in my database so the list should be small and a role will make it easy for you to manage the privilege.


how to get rank number that group by a column

You can use a variable for this purpose.

select customer_id, total, rank
from (
      select customer_id, total, @rank := @rank + 1 as rank
      from (
            select customer_id, sum(total) total
            from   invoices
            group by customer_id
            order by sum(total) desc
           ) t1, (select @rank := 0) t2
     ) t3
where customer_id = 1;

customer_id | total | rank
----------: | ----: | ---:
          1 | 14080 |    2

dbfiddle here


Limit the number of results but show how much would be returned

Are you sure you even need the left join to get a good count?
Are you using values from #rLog in the distinct?
And I would go with , distinct not +

this line is different

ON dc.[hashUFI] = #rLog.[hashUFI]

it might help

SELECT COUNT( DISTINCT [field 1] + [field n] )
  FROM #tempDC dc WITH (NOLOCK)
  JOIN #tempUID u WITH (NOLOCK)
        ON dc.[hashUFI] = u.[hashUFI]
       AND dc.iId IN ( '', u.iId )
       AND dc.rangeStart <= dc.rangeEnd AND u.rangeStart <= u.rangeEnd
       AND u.rangeStart <= dc.rangeEnd AND u.rangeEnd >= dc.rangeStart
  -- try without the left join and see if the number is different
  LEFT JOIN #rLog
        ON dc.[hashUFI] = #rLog.[hashUFI]
       AND dc.docId     = #rLog.docId
       AND #rlog.iId IN ( '',  u.iId )


Is it Possible that a variable name changes in a stored procedure after a restore?

Its not possible for the variable name to change by itself, its likely you restored from a different (older? newer?) backup than what you thought. You can check sys.objects for the stored procedure create/modified dates and compare across the 2 databases.


Multiple AGs on same Windows Failover cluster the Listeners don't work properly

It sounds like you have set the listeners to use the same port, probably the default 1433.

When you create an AG listener you specify a port for it to listen on. If you have multiple listeners on multiple AGs, they will each need to listen on a different port otherwise you will only be able to connect to the listener that started first and gained control of the port.

You should also ensure configure your SQL Server instances to listen on a specific port, as named instances will use a dynamic port by default. Specifying the port helps you document and control your environment better.

Info on configuring SQL Server to use a specific port

For your listener, run the below query against the primary of each AG to get the current port configuration:

SELECT ag.name AS [Availability Group], agl.dns_name AS [Listener DNS Name],
agl.port AS [Port]
FROM sys.availability_group_listeners agl
INNER JOIN sys.availability_groups ag ON agl.group_id = ag.group_id
ORDER BY ag.name, agl.dns_name

And use this code to update your listeners to use a specific port

USE [master]
GO
ALTER AVAILABILITY GROUP [** AG name here **]
MODIFY LISTENER N'** Listener name here **' (PORT=1433); -- or change to whatever port you are wanting to change to.
GO

Info on changing listener ports here


Since you mentioned about Availability Groups, this means that you are using SQL Server Enterprise edition (and possibly SQL Server 2012 or 2014).

You can use database snapshots, but in our case there were lot of dependencies on different components and so using database snapshot was ruled out.

Below will require a bit of more planning and testing.

Blue-Green Concept :

The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein

The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.
At the same time Green will be your staging/testing environment wherein you will upgrade your schema/build or product to the NEXT release, do a full regression test and get signed off by your business users. Once happy, during a cut-over period, you will promote the Green to be your "LIVE" environment and demote the Blue to be a preprod/staging or testing for the next release.

This way, you have a very less downtime and the risk of deployment failure on a live system (which is in maintenance window, since you are doing upgrade) will be highly minimized. Also, following the Blue-Green approach, you will be oscillating between LIVE and PREVIOUS version which will be staging for the next version.

Again, this will require more hardware/licensing as well as planning and testing.

Most of the steps can be automated using DACPACs and PowerShell. Also, if you are installing multiple instances on one server, make sure to re-balance the Memory settings when switching between Blue and Green. The LIVE environment gets more memory than the Passive environment.

In my current environment, we have implemented Blue/Green Model for Agile Code Deployment that allows us to promote code every 2 weeks with ample amount of time for testing and business sign-off. Also, its a breeze to rollback in case something goes horribly wrong. We have automated majority of the deployment stuff using Dacpacs and PowerShell.

(Image source)

Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies


MariaDB, SEQUENCE engine, dynamic upper bound

SELECT seq
    FROM seq_0_to_99999999
    WHERE seq BETWEEN 12345678 and 12345688;

works fine and fast.  It returned 11 rows.

A more complex query:

SELECT MIN(FROM_DAYS(seq)) AS first_day,
       MAX(FROM_DAYS(seq)) AS last_day,
       COUNT(*) AS number_of_days
    FROM seq_0_to_99999999
    WHERE seq BETWEEN TO_DAYS('2020-01-01')
                  AND TO_DAYS(CURDATE());

Returned:

+------------+------------+----------------+
| first_day  | last_day   | number_of_days |
+------------+------------+----------------+
| 2020-01-01 | 2020-04-02 |             93 |
+------------+------------+----------------+
1 row in set (0.00 sec)

It, too, was fast, and touched only 94 rows according to the 'Handler_read%' values.  Cf: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

For your case, you may need something like

WHERE seq BETWEEN ...
              AND ( SELECT MAX(...) FROM ... )

I find that the general use of seq is to generate a generous number of values, then let WHERE go for the range I need.  The Optimizer avoids really generating the zillion values I seem to be asking for.  (At least in examples like these.)


Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past.

No, Access itself won't corrupt data.  However your Access application needs to be designed so that it respects SQL Server constraints and rules. Also how will your Application stop users from making simple mistakes, like deleting records if they're not permitted to.

The most common problem I had was dealing with concurrency.  When 2 clients are doing things at the same time such as inserting a record. Do you let Access generate the primary key? or do you let SQL Server manage it? In either case after you've inserted rows how do you ensure that each client is still dealing with the same row?

This is a consideration for anyone developing a multi user system. It just takes some time and thought.

Some advice: Make sure that you define the primary key in SQL Server. If you don't do this Access won't allow you to update records as it can't guarantee a lock on a single record. Primary keys and indexes also have a huge impact on performance.

Edit: I've just been thinking about your Question title a bit more. You mention "security breach". If security is a concern, You need to consider how users will connect to the database. Domain authentication? or SQL authentication, and how you log actions taken by users.


What is the function that determines the maximum value in a series?

The function is called GREATEST()

  GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.


Cannot restore database because of stuck rollback

If you can restart the SQL Server service, then:

Stop the SQL Server service
Delete the old data/log files for MyDatabase
Start the SQL Server service again
At that point, you can delete the (offline) database
Do your restore


I worked with an insurance company that used active directory on all of their Redhat Linux servers so that their DBA's could log in with their own credentials. The DBA's would then SUDO to Oracle which did not have a password. Hence, the upper management could track when DBA's logged into each server. Using AD can work well in Linux the manage authentication. It also makes for fewer passwords that the DBA's need to remember.


Where is the cache for the MySQL caching_sha2_password auth plugin stored?

'when there exists a cached copy of the password hash in memory' (ref).

So its all in memory.

FLUSH PRIVILEGES can clear it


SQL Server database schema (and likely, some data changes) - how to auto-distribute over many database instances

Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)

For sql server : SQL Compare.

Useful resource for automating using Powershell or command line.

for Oracle : Schema Compare for Oracle

Note: Just to mention, there is Data compare to sync data as well.


Is it (always) necessary to backup a certificate with private key?

The code above will only backup the PUBLIC key portion of the certificate. This however by itself, is useless.

The private key is the part needed to decrypt the database - without this the database cannot be decrypted and therefore cannot be accessed.

sqlity Backup Certificate explanation

  To back up a certificate you can use
  the BACKUP CERTIFICATE statement. In its simplest form, it looks like
  this:

 BACKUP CERTIFICATE ACertificate TO FILE ='C:\temp\ACertificate.cert';

  But what about the Private Key? The above statement creates a backup
  of the public portion of the key only. That is however not the
  important part. The important part is the private key of the
  certificate. To create a backup of the private key too, we have to add
  the WITH PRIVATE KEY clause to the BACKUP CERTIFICATE statement:

 BACKUP CERTIFICATE ACertificate    TO FILE
 ='C:\temp\ACertificate.cert'   WITH PRIVATE KEY(
     FILE = 'C:\temp\ACertificate.prvk',
     ENCRYPTION BY PASSWORD = '**********'   );


How does postgres host based authentication work?

A local connection uses a Unix-domain socket; a host connection uses TCP.
The linked page says that

  you need to enable TCP/IP connections (DSpace uses JDBC)

… because JDBC does not have built-in support for Unix-domain sockets.

As long as a connection is allowed only from localhost (as configured in pg_hba.conf) and only to localhost (as configued by listen_addresses), it is as secure as a local connection.

The md5 is just an option that specifies how the password is to be encrypted during the connection setup; this is handled automatically by the JDBC driver. For a local connection, or for a connection over SSL, this does not really matter.