How do I make a nullable "parent" hierarchy level? I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this:

[PriceCode    v] --> nullable
  Price Code
  ...
[Material     v]
  Code
  AltCode
  Name
  ...
[Id           v]  --> not actually exposed as a hierarchy level
  DateInserted
  DateUpdated
  DateDeleted
  EffectiveFrom
  EffectiveTo

The problem is that the [PriceCode] attribute is nullable; the DSV has a FK between a [Materials] and a [PriceCodes] table, and [Materials].[PriceCodeId] is nullable.

Is there a way to still define a hierarchy where a nullable attribute is a parent? I've fiddled with UnkownMember and UnknownMemberName and the attribute key's NullProcessing setting, but couldn't get the dimension to process.

Bonus points if someone could confirm whether I'm approaching the slowly-changing dimension problem correctly, by making a hierarchy level based on business keys (i.e. the Code field; the natural key includes the EffectiveTo field, which is null for current image of a record), and treating the SCD metadata as a level of its own.

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :)

Nullable Parent Level

You probably don't want NULLs in your OLAP dimensions, and Kimball seems to agree.

  Nulls should also be avoided when we can’t provide a value for a
  dimension attribute in a valid dimension row. There are a several
  reasons why the value of a dimension attribute may not be available:

Missing Value – The attribute was missing from the source data.
Not Happened Yet – The attribute is not yet available due to source system timing issues.
Domain Violation – Either we have a data quality issue, or we don’t understand all the business rules surrounding the attribute. The data provided by the source system is invalid for the column type or outside the list of valid domain values.
Not Applicable – The attribute is not valid for the dimension row in question.

It kind of depends if you have an ETL process and a Data Warehouse or not how you should be handling them, but there are different types of 'not found'.

Think about the difference in a foreign key, one has an empty field, another has a field that's filled but the related record can't (or no longer) be found. I like to diffentiate between BLANK and DATA ERROR in my dimension.

In your example you could differentiate between 'no price code' and 'a price code I can no longer find'

If you have an ETL process with a Data Warehouse you can handle that easily in your ETL process, if you don't you would need some case statements in your DSV queries.

This question seems to reveal issues with the underlying Data Warehouse. There are arguments for and against both star and snowflake schema's but personally I tend towards a star schema, with some snowflake mixed in when necessary.
In any case the data cleansing and missing links needs to be solved in your Data Warehouse long before you reach the dsv.

Slowly Changing Dimension attributes

With regards to your Slowly Changing Dimension I don't see how the data type of hierarchies or keys in your dimension would change because the dimension somehow is SCD, that doesn't matter at all. You just need a validity rule somewhere in your ETL that gets picked up by your SSAS dimension definition (See here). But for any dimension key you create I suggest you use a surrogate key mostly because your surrogate key can be an int or bigint instead of a varchar and that could massively improve performance even for attribute keys.

  Using a numeric key column instead of a string key column or a
  composite key will improve the performance of attributes that contain
  many members.  This best practice is based on the same concept as
  using surrogate keys in relational tables for more efficient indexing.
  You can specify the numeric surrogate column as the key column and
  still use a string column as the name column so that the attribute
  members appear the same to end-users. As a guideline, if the attribute
  has more than one million members, you should consider using a numeric
  key.

Off course that numeric key would be a representation of 'the attribute' and not necessarily include the validity fields. The validity of the record is specified at the record in your dimension table, but as you state isn't necessary for your attribute keys.

For example this could be your dimension data

+---------------+-------+-----------+----------+
| DIMENSION_KEY |  NAME |  NAME_KEY |  CURRENT |
+---------------+-------+-----------+----------+
| 1             | tom   | 1         | y        |
| 2             | mat   | 2         | n        |
| 3             | mat   | 2         | y        |
+---------------+-------+-----------+----------+

Where you can pick dimension_key for the key of your key attribute and you could pick either name or name_key as the key of your name attribute.

Determining if it's worth the hassle for name depends on how many members your attribute will have (and your key attribute typically has most members).

In the end there isn't really any relation between the fact that you have a SCD and your decision what key is a good pick for your attribute. End user requirements make that decision for you. In the example dimension you would want all sales by mat reported under mat, and not have 2 mat's in your members when users report on that.


mysql separate email and cellphone from user table to avoid extra unused-space usage i have a question about putting some string (fixed-length and variable (which Might be always null)) into a table or separate them (Because of space-usage even if it's null) ...

for example, my user register with cellphone number or an email address

this is for cellphone number:

   cellphone_country_code (SMALLINT) (UNSIGNED)
   cellphone_num          (BIGINT)   (UNSIGNED)

and this is for email address:

   email_address         (CHAR(60))

Now which options are better ? (i want to know that i have to separate these data from user table or not)

option 1

user:

     id                      .....
     cellphone_country_code  (SMALLINT) (UNSIGNED)    (Default = 0)
     cellphone_num           (BIGINT)   (UNSIGNED)    (Default = 0)
     email_address           (CHAR(60)) (NULL)        (Default = NULL)

or (option 2)

user:
         id                      .....
         ....

user_cellphone:

         user_id                ......      (unique-index)
         cellphone_country_code  (SMALLINT) (UNSIGNED)
         cellphone_num           (BIGINT)   (UNSIGNED)

user_email:

         user_id                ......     (unique-index)
         email_address          (CHAR(60))

for option 1, if user registers with cellphone number and never want to add an email address, we have an empty-unused 60-BYTE space for email address (which we not going to use it for that user !)
so i think it's bad to have a column with extra bytes that you are not going to use !!! but it takes your ram space !

but in option 2, there is no extra unused-space because we add cellphone number or email address in separated tables so for example, if email address is not provided, there is no any empty-unused 60-BYTE space !!!!!

am i right ? what do you think ? which option is better ?
if we have a column (string with fixed-length or variable-length) and we don't use it, still it's get ram space (based on it's length i mean) ???

Use InnoDB, not MyISAM.

Use CHAR only for columns that are truly fixed length (country_code, uuid, postal_code, md5, etc).  email is not likely to be such.

I recommend against using a numeric field for a phone number.  What if the user has an extension?  Or needs a leading 0?  Or enters dashes, etc?

How many users are you expecting?  A million translates to well under 1GB.  Even a billion users, with tens of GB needed is probably not important compared to what else is going on in your multi-GB server.

Use NULL when the business logic needs.  For phone and email in your app, it means "not supplied".  That's fine.

If you dissect the table structure, you will find that more than half of the bytes are "overhead" are "free".  Your attempted optimization is relatively insignificant.

There are other reasons for splitting columns out from the table, but I don't think you have found a valid one.


How to set 'timing on' permanently in PostgreSQL? I work with PostgreSQL in the command line interface. I would like to activate a \timing command permanently for every database in the cluster so every time I connect to a database - this parameter is on.
I searched in postgresql.conf file, but I haven't found anything like what I want to switch on.

My PostgreSQL is 9.5.4 on x86_64 Ubuntu 16.04.2.

\timing is specific to the client psql, not to the database server. You need to put that into the configuration file for psql which is ~/.psqlrc

See the manual for details: https://www.postgresql.org/docs/current/static/app-psql.html#AEN100589

  psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands


How do I create a user-input column in SSRS? I am new to SQL Server Reporting Services and report designing; can anyone help me in this?

I have two questions:

How can I create a column for users to input data?  After that, I need to perform some operations on the data entered by the user, which I need to display in another column.
How can I round a decimal to the nearest integer?  For example, 6.03 to 6 and 6.52 to 7.

1) How to create a column to input user data and after that I need to
  perform some operations on data enter by the user which i need to
  display in another column

SSRS is not creating an application, just a report. It is not advisable to use SSRS to collect user input. A limited amount can be done using parameters but this is on a per report basis not per row as I think you're implying in the question. For example you could have a parameter to multiple values by a common factor and then display that result.

  2)how to make round a decimal to nearest integer ex 6.03 to 6 and 6.52
  to 7

Create an expression with the Round() function.

=Round(6.03)

this returns 6. Round also accepts a second value for number of decimal places. But instead of literal value, your fieldname would be in the parentheses:

=Round(myFieldName)


How to Index a JOIN result of two or more tables in order to improve the performance in SQL server? I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating a foreign key constraint i can't find any.

Now i've a requirement in which indexing should be implemented to improve performance. I've read about indexing the foreign key in order to improve the performance of a JOIN result.

Do i need to add the foreign key column to an additional non-clustered index or the foreign key has a default indexing?

How can i effectively implement indexing if my SQL table structure is as follows and i've a JOIN query with WHERE clause using t1_col3

               table1                       table2
               ------                       ------
               t1_col1(pk)                  t2_col1(pk)
               t1_col2                      t2_col2
               t1_col3                      t2_col3
               t1_col4                      t2_col4
               t2_col1(FK)

A foreign key does not have an index by default. You have to create one.

In this case, I'd suggest either of these. It depends on relative selectivity of the 2 columns

CREATE INDEX IX_table2fk ON
              table1 (t2_col1, t1_col3)
              INCLUDE (any columns needed for the query)

OR

CREATE INDEX IX_table2fk ON
              table1 (t1_col3, t2_col1)
              INCLUDE (any columns needed for the query)

The INCLUDE will make the index covering to avoid key/bookmark lookups


How do I reduce time spent "preparing"? I have a query I need to make more efficient. Trying a couple different things, one of which is SHOW PROFILE . That shows that the most massive timesink by far is "preparing".

As far as I understand, that state indicates that the database is planning, preparing and generally thinking about how to get the results most efficiently which leads me to belive that it can be reduced by providing hints, indices or rewriting the query.

Exactly how those hints, indicies or rewrites should be made escapes me though.

How do I go about optimizing "preparing". All I have now is a single profile row and a search enginge that directs me to articles about prepared statements.

PS: I realise that the "correct" answer probably is "Rewrite everything into normalised tables, relations and make a better query", which is what I'm actually doing.

Preparing also include parsing and compilation of query into native inner code. Replace direct queries by stored routines that compiled once at creation time.


Moving SQL MDF and LDF file Is there a way to move an MDF and LDF file from C drive to D drive? I've tried to run

ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE myDb SET OFFLINE

ALTER DATABASE myDb MODIFY FILE ( Name = myDb, Filename = 'D:\SQL Data Files\myDb.MDF')

ALTER DATABASE myDb SET ONLINE

ALTER DATABASE myDb SET MULTI_USER

but I have to manually move files from C drive to D:\SQL Data Files which is ok but is there a way to move files by SQL query itself, I'm not trying to move any System Databases just the databases that I have on my server.

Is there a way to move files by SQL query itself

Yes.  Use BACKUP/RESTORE WITH MOVE instead of OFFLINE/ALTER/File Copy/ONLINE.
eg
use master
go
use foo
alter database foo set single_user with rollback immediate
backup database foo to disk='c:\temp\foo.bak' with init
--restore filelistonly from disk='c:\temp\foo.bak'
use master
restore database foo from disk='c:\temp\foo.bak'
  with move 'foo' to 'd:\mssql\data\foo.mdf',
       move 'foo_log' to 'd:\mssql\data\foo_log.ldf'

Note that when you RESTORE over a database SQL Server deletes the old database files.


Many to many link tables with history? While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to:

enable customers to subscribe to multiple services
each service can have multiple domain names per customer
a domain name must be unique per customer per timestamp

Example customers:

1 | Sun Microsystems (USA) Inc.
2 | Sun Microsystems (UK) Ltd.

Example domains:

1 | sun.com
2 | sun.co.nz
3 | sun.co.uk

If Sun USA subscribes with the .com & .nz domains in January, then decides in April to have the offshore UK office manage these services instead, along with the .uk domain. Therefore the customer ID changes at a timestamp for billing in the different currency.

A domain name must be live (have no deleted_at timestamp) for only 1 customer per time. A customer may delete a domain & re-add it later, or another customer may add it later. For billing, I need to know which customer had what domains active on what dates.

How do I manage the link between domains & subscriptions, and maintain a history?

CREATE TABLE subscription
(
  id serial NOT NULL,
  customer integer NOT NULL,
  service integer NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  suspended timestamp with time zone,
  ceased timestamp with time zone,
  CONSTRAINT subscription_pk PRIMARY KEY (id),
  CONSTRAINT subscription_customer_fk FOREIGN KEY (customer)
      REFERENCES customer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscription_service_fk FOREIGN KEY (service)
      REFERENCES service (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

CREATE TABLE dns_domain
(
  id serial NOT NULL,
  name character varying(256) NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT dns_domain_pk PRIMARY KEY (id),
  CONSTRAINT dns_domain_uk UNIQUE (name)
)

This simple link table works, but without maintaining any history:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

If I want to allow multiple instances of the same subscribed domain on different dates (only 1 having a NULL deleted_at timestamp), how do I do that? This doesn't work:

DROP TABLE subscribed_dns_domain;
CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  deleted_at timestamp with time zone,
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, added_at),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

Should I revert to the first subscribed_dns_domain table, and also have a subscribed_dns_domain_history table, with timestamps populated by triggers on the simple subscribed_dns_domain table? Not sure how to populate customer reference/ticket numbers ...

How about a partial unique index or EXCLUDE USING gist () on the above table? A quote from the manual:

  Note: The preferred way to add a unique constraint to a table is ALTER
  TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
  constraints could be considered an implementation detail that should
  not be accessed directly.

From Erwin's suggestion above about using a tstzrange exclusion constraint, this seems to have the desired effect:

Firstly, as the admin user postgres I had to execute this CREATE EXTENSION btree_gist on the target database (PostgreSQL 9.2.3 on OpenBSD).

Then as the database user:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  active_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone, '[)'::text),
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, active_period),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_single_active_ek EXCLUDE
  USING gist (dns_domain WITH =, active_period WITH &&)
)

New subscriptions to domains are easily added:

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 4);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 5);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (3, 6);

With it baulking on duplicate domain names with overlapping subscription time ranges (as desired):

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 6);

ERROR:  conflicting key value violates exclusion constraint "subscribed_dns_domain_single_active_ek"
DETAIL:  Key (dns_domain, active_period)=(6, ["2013-10-10 20:43:55.891139+01",)) conflicts with existing key (dns_domain, active_period)=(6, ["2013-10-10 20:11:48.54009+01",)).
********** Error **********

Subscriptions are easily ended:

UPDATE subscribed_dns_domain
SET
    active_period = tstzrange(lower(active_period), now())
WHERE
    subscription = 2 AND
    dns_domain = 4 AND
    upper(active_period) IS NULL

Active subscriptions are easily found:

SELECT
    subscription,
    dns_domain
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NULL

 subscription | dns_domain
--------------+------------
            2 |          5
            2 |          6
(2 rows)

As is historical data:

SELECT
    subscription,
    dns_domain,
    lower(active_period) AS added_at,
    upper(active_period) AS deleted_at
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NOT NULL

 subscription | dns_domain |           added_at            |          deleted_at
--------------+------------+-------------------------------+-------------------------------
            3 |          6 | 2013-10-10 20:10:14.269551+01 | 2013-10-10 20:11:33.467619+01
            2 |          4 | 2013-10-10 20:10:09.611487+01 | 2013-10-10 20:48:15.64681+01
(2 rows)


Can't connect to local Postgres databases After trying to install Postgres using Postgres.app and EnterpriseDB, I decided to re-install it using Homebrew (As I got some problems connecting to database).

PYTHON=/usr/local/bin/python brew install postgresql
initdb -A trust /usr/local/var/postgres
initdb /usr/local/var/postgres -E utf8

mkdir -p ~/Library/LaunchAgents
cp /usr/local/Cellar/postgresql/9.3.2/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

I am working to create a database using Postgres.
Whenever I try to create database using command line, I got this problem :

MACBOOK:~ macbook$ postgres -D /usr/local/var/postgres

  LOG:  could not bind IPv6 socket: Address already in use HINT:  Is
  another postmaster already running on port 5432? If not, wait a few
  seconds and retry. LOG:  could not bind IPv4 socket: Address already
  in use HINT:  Is another postmaster already running on port 5432? If
  not, wait a few seconds and retry. LOG:  could not bind IPv6 socket:
  Address already in use HINT:  Is another postmaster already running on
  port 5432? If not, wait a few seconds and retry. WARNING:  could not
  create listen socket for "localhost" FATAL:  could not create any
  TCP/IP sockets

Or I face this problem

  Is the server running locally and accepting   connections on Unix
  domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Any ideas to solve this problem?

If you want to run more postgres clusters on a single machine, just make sure that they have their own $PGDATA (the location where their data and config files are)
and make sure that in their postgres.conf the parameter for port differ.
port = 2345
listen_addresses='*'

the cluster can share the listen_addresses but not the port. So each must have it's own port number.


Is selecting from the same server but different database slower than the same database? I am thinking about 2 possibilities that I want to use in my project. So there are 2 projects, with different databases each. So Project P1, has Database D1, and Table T1. Project P2, has Database D2.

For Project P2, I need to select the data at Table T1 quite often. Is it better to create a replicate of T1 inside Database D2 (name it T2), and do SELECT * from T2 instead of doing SELECT * FROM [D1].[T1] ?

The 2 databases D1 and D2 are in the same server.

To add a bit to TomTom's comment: The optimizer has full access to meta-data etc regardless of whether the data is in one database or several databases. And the optimizer or execution engine is on constrained to one database. As long at it is in the same instance! If you talk about cross-instance queries (using linked servers, for instance), then you will definitely hurt!


Calculating the record value I have two table one tblstationerystock (stationery_name,stationery_code, balance) and other tblstationerytranscation(stationery_code,trsntype,quantity).Trsntype column is transction type consists of two type of value (received or issued)
I want to calculate the value of balance in first table based on any transcation for stationery in second table.
Mathematically suppose there is  a stationery name 'PEN' in first table and in second table  there are three record for stationery 'PEN' -
STATIONERY. TRSNTYPE   QUANTITY
Pen.        Received.     200
Pen.        Issued.         30
Pen.        Issued.         40
In first table in balance column it should show 50(100-30-20)
I dont have much knowledge about sql .Is it possiblePlease guide me to achieve this .

Maybe if the MySQL version supports, you can use an AFTER INSERT TRIGGER
Here is the code that will help for INSERT statements
    delimiter $$
CREATE TRIGGER mytrigger
    AFTER INSERT
ON tblstationerytranscation
    FOR EACH ROW
    BEGIN
    declare quan integer;
    set quan = new.quantity;

    if new.trsntype = 'issued' then
        set quan = -1 * quan;
    END IF;

        update tblstationerystock
        SET balance = balance + quan
        where stationery_code =  new.stationery_code;

end $$


Which MySQL shows CPU usage? I am monitoring MySQL server using phpMyAdmin [Status => Monitor] function. And it shows a number of useful graphs.

Which variables (or which mechanism) is phpMyAdmin using to pull this data?

ps. I was wondering if it is calling SHOW STATUS query?   But when I look through a bunch of variables resulting from SHOW STATUS I cannot spot anything related neither to CPU, memory or not even active connnections...

Some guesses.  Some things come from SHOW GLOBAL STATUS (highlighted and with initial caps, below); some from elsewhere.

Questions:  Status Questions or Queries.  The latter seems to include statements inside Stored routines.

Connections:  Not Connections since that continues to grow.  Probably not Threads_running since that should be very small.

Processes:  Perhaps from the OS.  Are you using Windows?

Traffic:  Bytes_sent/received.  (Not very useful)

CPU Usage:  The OS.  That is high.  You have some slow queries and/or poor indexing.  See the slowlog (more below).

System Memory:  The OS.  See top or whatever Windows has.  Sounds like there is room to increase some settings.  Do you have 6MB of RAM?

Swap:  From the OS.  No swapping is Good.

Slow queries:  Slow_queries.  0 is good ...IF... you have a reasonable value for long_query_time, which defaults to 10 seconds.  Change it to 1.

Key reads:  Maybe Key_reads, which applies only to MyISAM.  Are you using that engine?  Maybe Handler_read_key, which applies to all engines, but is not useful by itself.

The graphs are pretty, but mostly useless.

I may be able to give you about 20 tips from the full set of VARIABLES and GLOBAL STATUS, plus tips from the slowlog, once you make use of it:  http://mysql.rjweb.org/doc.php/mysql_analysis


Why does ALTER COLUMN to NOT NULL cause massive log file growth? I have a table with 64m rows taking 4.3 GB on disk for its data.

Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.

I added a a NULLABLE column with data-type Datetimeoffset(0).

I then UPDATED this column for every row and made sure all new inserts place a value in this column.

Once there were no NULL entries I then ran this command to make my new field mandatory:

ALTER TABLE tblCheckResult
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

The result was a HUGE growth in the transaction log size - from 6GB to over 36GB until it ran out of space!

Does anyone have any idea what on earth SQL Server 2008 R2 is doing for this simple command to result in such huge growth?

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done in a single pass, though, the log has to account for all of the changes so that, if you hit cancel, it knows exactly what to undo.

An example. Simple table:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Now, let's look at the page details. First we need to find out what page and DB_ID we're dealing with. In my case I created a database called foo, and the DB_ID happened to be 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

The output indicated that I was interested in page 159 (the only row in DBCC IND output with PageType = 1).

Now, let's look some select page details as we step through the OP's scenario.

DBCC PAGE(5, 1, 159, 3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;
DBCC PAGE(5, 1, 159, 3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

Now, I don't have all the answers to this, as I am not a deep internals guy. But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. I do believe that SQL Server 2012 handles some of these scenarios a lot better, FWIW - but I have yet to do any exhaustive testing.


jdbc+mysql: "Deadlock found when trying to get lock; try restarting transaction" After recovering from a disk-full problem, I started getting these errors in my logs:

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1684)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at com.dappit.Dapper.adminer.pixelMaster.storage.RemotePixelStorage.flushBufferToDb(RemotePixelStorage.java:120)
    ...

The Java code (RemotePixelStorage.java:120):

(119) connection.setAutoCommit(false);
(120) int[] returnCodes = pstmt.executeBatch();
(121) connection.setAutoCommit(true);
(122) connection.commit();

Each statement is a call to the stored procedure add_pixel, which adds an element to a table and removes older elements if necessary.

What does this jdbc exception mean? How can I overcome this issue? Should I simply try to commit a few times with a catch block?

Looking at the Stored Procedure, I see something rather unnatural.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

The thing I see that is unnatural is the DELETE ... JOIN query that is mixing an InnoDB table and a MyISAM table. Such JOINs tend to be rather clumsy to mysqld.
Let's make the pixel_window table. InnoDB. In fact, let's not make it a temporary table. Of course, the hard part is to create a unique pixel_window table for the session. The pixel_window temp table will be the prefix 'pixel_window_' with the Connection ID appended.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  SET @pwtable = CONCAT('pixel_window_',CONNECTION_ID());

  #
  # Create the pixel_window Table
  #
  SET @sqlstmt = CONCAT('CREATE TABLE ',pwtable,' (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  #
  # Load the pixel_window Table
  #
  SET @sqlstmt= CONCAT('INSERT INTO ',@pwtable,' ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);

  #
  # Use the pixel_window Table to perform DELETE...JOIN
  #
  SET @sqlstmt = CONCAT('DELETE A.* FROM pixels A LEFT JOIN ',
  @pwtable,' B USING (id) WHERE A.type=GivenType AND B.id IS NULL');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;

  #
  # Drop the pixel_window Table
  #
  SET @sqlstmt = CONCAT('DROP TABLE IF EXISTS ',@pwtable);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

END $$

DELIMITER ;

Give it a Try !!!
CAVEAT : Since I created this monster, I needed to slay it as well.


How to setup a MongoDB replica set for the connector I want to keep MongoDB and Solr in sync. The mongodb-connector (python) is the tool I want to use to do that.
Now a replica set in mongodb is required (see ReadMe), but I don't want (and can) setup a set/instance.

So can I have a replica set setup with only the primary and without secondaries? Means: A replica set setup with only one instance? And how does this

Meanwhile I found a solution:

Add in the config-File following:

replSet=rs0 (name is variable) (necessary because starting mongod with replSet option didn't work)
oplogSize=[MemSize]

Start mongodb-instance:

sudo /etc/init.d/mongod start
mongo

Set replicaset:

rs.initiate({"_id" : "rs0","version" : 1,"members" : [{"_id" : 0,"host" : "localhost:27017"}]})
Check with rs.status()

In mongodb 3.2 "replSet" is changed to "replSetName"
https://docs.mongodb.com/v3.2/tutorial/upgrade-config-servers-to-replica-set/

You can start a single node replica set by starting the mongod with the replSet argument/config option, that is all that is required.  It takes a name argument, which will then function as the name of the replica set in question.  Once you have done that, you simply run rs.initiate() from the shell when connected to the node (this is only needed once).

This is described in detail here:

http://docs.mongodb.org/manual/tutorial/convert-standalone-to-replica-set/

To stay with just a single node, simply do not follow the "Expanding the set" instructions.


How can I pivot a view into a join so I get multiple query rows as columns in a single row? I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data into my view.

What I am trying to achieve is to join this view to my main vehicle table so that I can select the various properties as columns in a single row.

My view:

Vehicle_ID  Property     Value
1           Doors        2
1           Engine Size  2 liter
1           Tire Size    50cm
1           Color        Blue
2           Doors        4
2           Engine Size  3 liter

And my main vehicle table:

Vehicle_ID  Description  Date_Bought  Owner_ID
1           BMW M3 Sport 2000/12/23   1
2           Audi A5 3XL  2015/03/19   1

So what I would like to get out is something like

Vehicle ID  Description   Date_Bought   Doors   [Engine Size]  [Tire Size]  [Color]
1           BMW M3 Sport  2000/12/23    2       2 liter        50cm         Blue

My overall SQL knowledge is fairly limited, I'm pretty good with JOINS and stuff, but the logic of the PIVOT command and its syntax completely escape me.

Using the PIVOT clause, you can achieve the result like this:

SELECT
  Vehicle_ID,
  Doors,
  [Engine Size],
  [Tire Size],
  [Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
;

Now, to join the results to your main vehicle table, you can use the above as a derived table (same as you probably did with the solution you found yourself) – or you can join the main table directly to the results of the PIVOT:

SELECT
  m.Vehicle_ID,
  m.Description,
  m.DateBought,
  p.Doors,
  p.[Engine Size],
  p.[Tire Size],
  p.[Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
  INNER JOIN dbo.MainVehicleTable AS m ON p.Vehicle_ID = m.Vehicle_ID
;

The above assumes that your view has only the three columns mentioned in your question. If there are more (e.g. time of recording/storing the property or something else), then you will probably need to replace the plain dbo.YourView reference with a derived table where you are pulling only those three columns:

...
FROM
  (
    SELECT
      Vehicle_ID,
      Property,
      Value
    FROM
      dbo.YourView
  ) AS s
  PIVOT
  ...


Escaping a single quote in SQL Server 2005 I am using SQL server 2005 and using the northwind database. I need the supplier id of  Grandma Kelly's Homestead and I used this query:

select supplierid from suppliers
where companyname ='Grandma Kelly''s'' Homestead'

I checked the link Escaping a single quote in SQL Server, but I can't find an answer.

I used the two single quotes but it didn't work since the query is executing but it doesn't return a value.

How can I escape the single quote and retrieve the value? I would also like to know how to insert the values into the table having quotes

I think you just meant:

select supplierid from dbo.suppliers
where companyname ='Grandma Kelly''s Homestead';

(In other words, there was only one apostrophe to replace.)


Remote PostgreSQL DB with local buffer ... how could it look like I have a sensor which is generating a data vector let's say every second (adjustable to up to 100 vectors per second) at a remote location. The location got 4G modem to send the data. Due to power limitations I have chosen to use a Raspberry Pi 3+ to receive the data from the sensor, decode it and send it to my VPS server.
On my server running Ubuntu 18.04 I have installed TimescaleDB on top of PostgreSQL which is a good combination to handle time-series data and I would like to store the sensor data in there and publish then on my website. I'm keen to hear your thoughts of how the data flow could look like and I have some questions.

What would be the best way of inserting the sensor data from the RPi into the server DB? Is it save to insert them directly from the RPi using the libpq library in my C software or should I transport the data to the server in a different way and then insert the data into the DB at the server side?

At the RPi side, I would like to have a local buffer to prevent losing data if the connection to the server breaks. I could simply use a circular buffer in my C program or a named pipe, or I could install PostgreSQL/TimescaleDB locally, insert the data there and then use postgres_fdw or dblink to transfer the data from there to my server DB and delete the entries from my local DB. What would you suggest?

I think installing Postgres on the RPi is overkill.
Buffering the sensor data in a plain text file should be good enough. The process could look something like:

send the sensor data to a text file (append mode)
Have a cron job that runs every x minutes. This will rename the text file as a first step. This makes 1. create a new text file while you process the "old" one
Send the contents of the text file to your Postgres server using copy ... from stdin inside a transaction. If this fails (connection problem), retry until successful.
Remove the file that was just sent to the server. Alternatively keep it as an archive/backup - which would require creating a filename in 2. that is unique, e.g. by appending a timestamp.

Using copy .. from stdin is way more efficient than running multiple single-row INSERT statement. And still more efficient than running a single multi-row INSERT statement. Additionally the file then also acts as the buffer you want.


Understanding why Replication Distribution Agent stops Let me preface this in that I'm working as an accidental DBA at the moment.

We have continuous transactional replication running.  For some reason the Distribution Agent stops with a message of "success", but I can't understand at all why it is stopping and from the logs I don't get much information.

Two questions.

Other than looking at the monitor does anyone have some good ideas as to where I can get a bit more detail?  I can do verbose logging to a file but the replication has lots of data and the files grow really big really fast.
Is there a way to have the Distribution Agent restart itself whenever it stops other than when I manually stop it?  Maybe adding an additional step to the Agent job?

As far as starting it...add a schedule that runs the job every minute. If the job is running, no issues.  If not, it will start.

can't help on question 1 with no additional info.


Select Statement to generate result, for group by per second and link with Table I tried to search in existing queries but didn't find any related thread.
I would like to run a query in user table (for example), and like to get output result on per second basis. My query gives me zero (0) result if no second found.

Here is my query:

select
            call_start = convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108),
            rows = count(*)
  from [User_Table]
      where [session_datetime] >= ('09/29/2014' + DATEADD(mi, -3, CONVERT(varchar(50), GETDATE(),108)))
    and [session_datetime]<= ('09/29/2014' + DATEADD(mi, -1, CONVERT(varchar(50), GETDATE(),108)))
     group by convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108)

and current output/result of the query is like:

15:10:06    1
15:10:07    1
15:10:10    1

Here you can see that for 15:10:08 and 09 there is no entry shown, because no record exists in the database. How can I bring these entries as well with zero entry, like this:

15:10:06    1
15:10:07    1
15:10:08    0
15:10:09    0
15:10:10    1

How about creating a table holding all times for a day and using it to drive the query?

In this simple demo, I set up a table similar to yours, load it with data, then use the dbo.timeLookup table to drive it.  The example assumes you are only working with one day, but it could be extended if your timespan needed to span multiple days:

USE tempdb
GO

SET NOCOUNT ON

-- Create a large table
IF OBJECT_ID('dbo.user_table') IS NOT NULL DROP TABLE dbo.user_table
CREATE TABLE dbo.user_table (
    rowId INT IDENTITY PRIMARY KEY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    session_datetime DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

IF OBJECT_ID('dbo.timeLookup') IS NOT NULL DROP TABLE dbo.timeLookup
CREATE TABLE dbo.timeLookup (
    [time]      TIME ,
    [hour]      AS DATEPART( hour, [time] ),
    [minute]    AS DATEPART( minute, [time] ),
    [second]    AS DATEPART( second, [time] ),

    CONSTRAINT PK_dbo_timeLookup PRIMARY KEY ( [time] ),
    CONSTRAINT UK_dbo_timeLookup UNIQUE ( [hour], [minute], [second] )
    )
GO

-- Load up some dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.user_table ( session_datetime )
SELECT DATEADD( second, rn % 86400, '09/29/2014' )
FROM cte
WHERE rn % 33 != 0
GO

-- Suggested index
CREATE INDEX _idx ON dbo.user_table ( session_datetime )
GO

-- Populate the timeLookup table
;WITH cte AS (
SELECT TOP 86400 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.timeLookup ( [time] )
SELECT DATEADD( second, rn, '1 Jan 2014' )
FROM cte
GO

-- Remove certain seconds for the demo
DELETE dbo.user_table
WHERE  CAST( session_datetime AS TIME ) In ( '15:10:08', '15:10:09' )

-- Have a look at the sample records loaded
--SELECT MIN(session_datetime) xmin, MAX(session_datetime) xmax, COUNT(*) records
--FROM [User_Table]

-- Main query
SELECT tl.[time], COUNT(ut.session_datetime) AS records
FROM dbo.timeLookup tl
    LEFT JOIN dbo.user_table ut ON tl.[time] = CAST( ut.session_datetime AS TIME )
        AND ut.[session_datetime] Between '2014-09-29 00:00:00.000' And '2014-09-29 23:59:59.999'
WHERE tl.[time] Between '15:10:06' And '15:10:10'
GROUP BY tl.[time]

Result:


Can a trigger catch updates done by a select for update + lob.write()? It's maybe a stupid question, but I didn't find an explicit answer...

I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR UPDATE, then lob.write(...) and COMMIT. (I have tested it with c++ using occi)

Is there any way to catch it in an update (or any other) trigger?

I find it hard to believe that there is command that can't be monitored by any trigger...

If it's matter I'm using version 11g.

Thanks!

EDIT

Trigger for example:

CREATE OR REPLACE TRIGGER TR1
BEFORE DELETE OR INSERT OR UPDATE ON TEST_CLOB
FOR EACH ROW
BEGIN
IF DELETING THEN
  WRITE_LOG('LOG_DIR','deleting:'||:old.filename);
  goto endproc;
END IF;
IF INSERTING THEN
  WRITE_LOG('LOG_DIR','inserting:'||:new.filename);
  goto endproc;
END IF;
IF UPDATING THEN
  WRITE_LOG('LOG_DIR','updating:'||:old.filename||'-'||:new.filename);
  goto endproc;
END IF;
WRITE_LOG('LOG_DIR','other:'||:old.filename||'-'||:new.filename);
<<endproc>>
NULL;
END;

Now in my log I see the inserting and deleting, but I never see any update.

Copied comment :

You can't do that , at least up to 12c : "Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not fire triggers defined on the table that contains the columns or attributes." (docs.oracle.com/database/121/ADDCI/lobs.htm#ADDCI4383 )


how to get the CPU speed in SQL Server? when I run the following query from Glenn Berry

-- Hardware information from SQL Server 2016  (Query 18) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type],
softnuma_configuration_desc AS [Soft NUMA Configuration],
sql_memory_model_desc -- New in SQL Server 2016 SP1
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------

when I use powershell I get more details though:

I have been reluctant to learn powershell, but I plan to accept it soon...

meanwhile, the question is:

Is there any way in sql server to find out the speed of the processor (in this case 2.60 Ghz)?

You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this:

DECLARE @cpu_speed_mhz int,
        @cpu_speed_ghz decimal(18,2);

EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                            @key = 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
                            @value_name = '~MHz',
                            @value = @cpu_speed_mhz OUTPUT;

SELECT @cpu_speed_ghz = CAST(CAST(@cpu_speed_mhz AS DECIMAL) / 1000 AS DECIMAL(18,2));

I tested this on a few different computers and it seemed to get me what I needed. It may work for you as well.

Just remember that xp_regread is undocumented, and may blah blah blah like Microsoft ever deprecates anything anyway.

Hope this helps!


How to view contraints of a cloumn in postgresSql? I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME] . If there is a way to drop all constraint from a column that should work too.
I cannot use psql command.

PostgreSQL exposes the constraints as well as other schema details through information_schema, so to find all the constraints for the table, query table_constraints, for example:

SELECT constraint_name FROM information_schema.table_constraints
    WHERE table_name='my_table' AND constraint_type='UNIQUE';

To find which columns are involved into the constraints, check constraint_column_usage.


Inserting into multiple tables simultanteously (HEIDISQL) I have three tables.

flatpack_apn

components_apn

flatpackcomponents_apn

CREATE TABLE `component_apn` (
`componentno` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(30) NOT NULL,
PRIMARY KEY (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;

CREATE TABLE `flatpackcomponents_apn` (
`flatpackid` INT(11) NOT NULL,
`componentno` INT(11) NOT NULL,
`quantity` INT(10) NOT NULL,
PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `flatpackid` (`flatpackid`),
INDEX `componentno` (`componentno`),
CONSTRAINT `flatpackcomponents_apn_ibfk_1` FOREIGN KEY (`flatpackid`) REFERENCES `flatpack_apn` (`flatpackid`),
CONSTRAINT `flatpackcomponents_apn_ibfk_2` FOREIGN KEY (`componentno`) REFERENCES `component_apn` (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;

CREATE TABLE `flatpack_apn` (
    `flatpackid` INT(11) NOT NULL AUTO_INCREMENT,
    `name` TINYTEXT NOT NULL,
    `colour` TEXT NULL DEFAULT NULL,
    `flatpacktype` ENUM('Office','Kitchen','Bedroom','General') NOT NULL,
    `unitprice` DECIMAL(5,2) NULL DEFAULT NULL,
    PRIMARY KEY (`flatpackid`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2123
;

I am asked to:

Populate the tables with sample data using suitable data values and choosing values which are not offensive.
Flatpack: at least 5 flatpacks with at least 2 having more than 3 components
Component: at least 10 components

I am a little uncertain as to how to this.

I thought that:

INSERT INTO flatpack_apn
SELECT *FROM components_apn

INSERT INTO flatpack_apn

SELECT quantity FROM flatpackcomponents_apn;

Is it possible to run them simultanteously, or would I need to execute them as two seperate events? Is there any downside to simply running them as two seperate insert events? In truth, I am not entirely sure I am interpreting the question above correctly.

As a sidebar; and apologies if I am asking too much in one question, but, I figured that I would be required to create/insert new columns within the table before adding the rows/records. Would this be a correct assumption?

The overhead and complication of spinning off two threads to run the queries simultaneously are almost always more costly than doing them sequentially.

Copying an entire table over is normally a rare task.  As such, the performance is often not a big deal.  Are you doing this as part of the normal flow?  If so, let's look at it in more detail to see if there is a better way to achieve the ultimate goal.

Don't use TINYTEXT.  VARCHAR(255) (or some smaller number) is virtually equivalent, yet more efficient in some situations.

Redundancy:

PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `componentno` (`componentno`),    -- DROP, since the PK handles it.


Switching from SIMPLE to FULL recovery mode implications I have several databases that I'd like to move over to FULL recovery mode so we can have point-in-time restore capability.  We don't need it on all of our databases, just the ones that are transaction heavy and contain data that is constantly being updated.

I've done lots of research and I fully understand what happens when you switch over to FULL recovery, especially how it pertains to the log file.

What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

Right now, I plan on doing one database at a time, so I can monitor log file growth and determine the best frequency for log file backups to ensure we don't get into a runaway log file situation.  I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Are there any other considerations or advice that would be helpful.  Anything I should watch out for?  We run full index rebuilds, DBCC CHECKDB and statistics rebuild once a week too.  Are there any potential problems with those operations (maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Thanks for all your help!

There are lot of things I would like to say

  What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation.

  I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

You can take differential backup also perhaps this would be very helpful if you have big database. anything that links LSN chains would do.See below link it has good information about switching between recovery models

http://msdn.microsoft.com/en-gb/library/ms189272.aspx

  My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Stop there, this is not correct approach just because you did a full backup and log backup please don't be in idea that you can safely remove old log files and full backup. Do you have a plan to test old backup by restoring to actually see that in case of disaster this backup files would work. Remember only successful restore guarantees that your backup is totally consistent. Do you have a option to check backup integrity.If not please include it into your backup plan. At least keep 4 day old backup files( this is what I do on local disk) before if you delete backups This strategy is upto you keep backups for time where you are sure they wont be useless if disaster strikes. Sometimes business want only specific data change to which was made couple of days. I also have my database backup up on tape and that tape is stored for 6 months.

  (maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Changing recovery model to bulk logged and doing bulk logged operation would make you loose point in time(PIT) recovery so if you are concerned about PIT recovery dont do that. Instead rebuild index through intelligent script which only rebuild fragmented index like one Is Ola Hallengren index rebuild solution please note that if Index is rebuild with full scan for that index stats is already updated with rebuild process.

If you do heavy DML please break it into batches so as not to explode log files.


Single Column index or 3 column index in MS Access I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated.

There will be a single NO DUPLICATES index on either the 3 fields or the single concatenated field.

My question is this: from a performance point of view, is it faster to have a single text field with a single column index, or a 3 column index covering the 3 text fields? My guess is the single option, but I don't know how much difference it makes? There will be approx 20 possible table names, 100 possible field names, and a few thousand caseIDs.

I only need to search on a combination of the 3 text fields, never on any of the fields individually.

Thanks for any advice!
Jim

I'd say it will make only a negligible difference, but that's from the gut, as factors such as the number of data sets, CPU, RAM and HDD/SSD play a role.

However, I would technically tend to go for the three-field solution, as it gives you flexibility.

You can use them more flexibly in queries.

And consider the case that you might need to replace the value of one of the fields with another one later. Then the fun really starts with the one-field solution...


T-SQL - Enforce Atomic Operations x Locks I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2.

Table OPERATIONS:

VL_CREDIT decimal(10,2)
VL_DEBIT decimal(10,2)

Table BALANCE:

DT_OPERATION datetime
VL_CURRENT decimal(10,2)

Procedure INSERT_OPERATION

GET LAST BALANCE BY DATE
CHECK IF VALUE OF OPERATION > BALANCE
   IF > RETURN ERROR
   ELSE INSERT INTO OPERATION(...,....)

The issue I have is the following:

The procedure to insert the operation has to check the balance to see if there's money available before inserting the operation, so the balance never gets negative. If there's no balance, I return some code to tell the user the balance is not enough.

My concern is: if this procedure gets called multiple times in a row, how can I guarantee that it's atomic?

I have some ideas, but as I am not sure which would guarantee it:

BEGIN TRANSACTION in the OPERATION procedure
Some sort of lock on selecting the BALANCE table, but it must hold until the end of procedure execution

Can you suggest some approach to guarantee that? Thanks in advance.

There are two different isolation levels you can use within a session that will insure that the data read at the beginning of the transaction doesn't change until the transaction with one of the isolation levels mentionned below is committed

BOL article on Isolation Levels

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

REPEATABLE READ

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

The main difference between the two, is that the serializable isolation level prevents inserting new data anywhere within the range of rows.

USE MYDATABASE
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
sp_insert_operation
COMMIT TRANSACTION

There is also the option of setting snapshot isolation at the database level, but I don't believe that would give you the level of isolation you are looking for.

Here is the relevant documentation ALTER DATABASE look under snapshot isolation

Update on Serializable

http://blogs.msdn.com/
Serializable isolation level relies on pessimistic concurrency control.  It guarantees consistency by assuming that two transactions might try to update the same data and uses locks to ensure that they do not but at a cost of reduced concurrency - one transaction must wait for the other to complete


Would comparison of strings to a postgres 'text' type column for equality be a performance bottleneck? I have a Postgresql database with a column type of text. Coming from a SQL Server background, is this equivalent to the (n)varchar(max) type?

My specific example/reason for asking this question, is that I have a table with a column of type text in which I would like to store unique values. The table is updated according to regular CSV imports, meaning that for every row in the CSV the the text column is checked for an existing entry (a column-value in the CSV), and if none is found then that value is inserted into the table.

My understanding is that this could mean checking thousands (or maybe hundreds of thousands) of text values against other text values. I imagine this to be incredibly inneficient. Is this the case?

Yes, text is (roughly) equivalent to varchar(max)

Comparing text values is not less efficient than comparing varchar values in Postgres as under the hood they are absolutely identical. So the efficiency of the comparison is related to the length of the values. I don't expect that to be any slower than your current implementation using varchar(max) in SQL Server.

If you want to enforce uniqueness on a column, create a unique index on it. Then you can use insert ... on conflict do nothing to efficiently insert new values and at the same time validating that they are unique.

However: there is a technical limit on how long an index entry is allowed to be which is roughly 2700 byte. You didn't mention how long your values are, but the index might not work for you then.

Consider also this answer https://dba.stackexchange.com/a/69164


SQL Select records where any word / value contained within one string is also contained within another string I need to find a SQL select query that will allow me to find records in [KnowledgeArticle] Where any word contained in the string [KnowledgeArticle].[Keywords] is contained within [Incident].[Description].

I'm working with an IT Service Management application, and want to provide a list of Knowledge articles that match an Incident record's description, automatically. I don't have direct access to change the way this ITSM application queries the database, it's all managed via a series of GUI's.

However, I can connect the ITSM app to views that I create in the database, and filter results of the view from there.

So, my challenge is that I need to get, within a view, a list of all [KnowledgeArticle] records, duplicated once for each [Incident] record, where any word within [Incident].[Description] is contained within [KnowledgeArticle].[Keywords].

This query on it's own would be quite ridiculous, and would present a very large subset of records, however once I have the query built and set up as a view, I'll be able to configure the ITSM application to filter the results of this query / view to only the current [Incident] record, providing me with the data that I need (Knowledge articles where any keyword in the knowledge article is present on the Incident).

Hopefully this makes sense, I'm not sure what kind of operator I might be able to use in a comparison clause to bring up that list of knowledge article records.

(If you're not familiar with IT Service Management jargon, the words Incident and KnowledgeArticle are just table names, what they do is less important to the technical question being asked).

I don't know the requirements of your project but with the info you provided on this post, this qualify for a data quality project from Data Quality Services (DQS):

Data Quality Project (DQS)


All users are having NO Privileges in phpMyAdmin By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one database. But I have nearly 25 databases created with 'root' user. If I want to access all other databases I need to GRANT Privileges for the root user again but all other users are not permitted to execute GRANT statement. So is there any way to grant permissions for the root user again. The all other Databases contained in my DB Server are very much important. So Please help in resolving this issue.

I have discussed situations like this before in these posts:

May 10, 2013 : In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table
Sep 22, 2013 : How to create root user after running mysql_install_db?

In your case do this

STEP #1

Add these lines just under the [mysqld] group header in my.ini

[mysqld]
skip-grant-tables
skip-networking

STEP 2

Open Windows Command Line as Administrator, and reboot MySQL Service

C:\> net stop mysql
C:\> net start mysql

STEP 3

You can login to MySQL

C:\> mysql <Hit Enter>

STEP 4

From the mysql prompt, run this

SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = '127.0.0.1',
              Password = @PasswordHash,
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
exit

STEP 5

Remove skip-grant-tables and skip-networking from my.ini

STEP 6

Restart MySQL

C:\> net stop mysql
C:\> net start mysql

STEP 7

Try logging into MySQL

GIVE IT A TRY !!!


DBCC checkdb showing failure after a restore We restored a backup into a new database.  After running a few delete queries we received the following message:

  Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical
  consistency-based I/O error:...

I ran DBCC checkdb and saw numerous lines such as:

Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:783) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057599497994240 (type Unknown), but it was not detected in the scan.

and

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data), page (1:307605). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 0, text ID 1368915968 is referenced by page (1:307603), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 1, text ID 903086080 is referenced by page (1:307648), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 1368915968 owned by data record identified by RID = (1:43506:13)
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 903086080 owned by data record identified by RID = (1:102876:38)

The final section of output was:

CHECKDB found 1 allocation errors and 12 consistency errors in database 'Clients'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Clients).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My question is: Should I attempt to restore the database again or proceed with attempting to repair?  I only hesitate because it is a rather large DB and restoring takes quite a while.

UPDATE: a bit more info.
All of the table errors are related to a single table I could just drop and recreate.  However I don't understand what the IAM error really means or its impact.

Should I attempt to restore the database again or proceed with attempting to repair? I only hesitate because it is a rather large DB and restoring takes quite a while.

I'd probably do both, but restore it to a new name.

  All of the table errors are related to a single table I could just drop and recreate. However I don't understand what the IAM error really means or its impact.

If the data in that table really, truly doesn't matter, and you can really, truly drop and recreate the table, that's probably the fastest option.  Especially since "repair_allow_data_loss" will probably cause data loss, yes.

As for what the IAM error means:

Short version:  Your table innards are corrupted.

Longer version:  The Index Allocation Map tells SQL Server where to get the actual objects.  In this case, based on the references to RIDs I'm guessing this is a table without a clustered index (in other words, a heap), and that it's telling SQL where to get the data that was actively updated inside the heap.  (One of the things that happens when a heap has updates is that if the new information doesn't fit in the old space, a pointer is created that tells SQL where to get the new information.)

Here's an article on IAM pages, and here's a good video on heaps in SQL Server that came up when I was looking for IAM info for you.


MySQL: SELECT to local variable in stored procedure I have the following mysql stored procedure:
DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16),
                                     IN cardUID VARCHAR(14))
BEGIN
    SET @projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID =
          (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName)
             ORDER BY ID DESC LIMIT 1);
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;

    SET @id = (SELECT MIN(ID) FROM WORKDAY
                   WHERE WORKERID = @workerId
                     AND PROJECTID = @projectId
                     AND STOPTIME IS NULL
                     AND STARTTIME IS NOT NULL);
    IF (@id >= 0)
        THEN
            UPDATE WORKDAY SET STOPTIME = now() WHERE ID = @id;
    ELSE
        INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME)
            VALUES (@workerId, @projectId, now());
    END IF;
END; //
DELIMITER ;

When I call the procedure once, I get the result:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.09 sec)

From second run, is not working anymore:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

ERROR 1048 (23000): Column 'WORKERID' cannot be null

I did try to isolate and run the procedure with only this two lines of code:
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;

And it behaves just like above, but if I try to run SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = "045D91B22C5E80"; alone, works without problems.
The @projectId variable behaves normal everytime I call the stored procedure.
Can someone help me to find a solution?
Thank you!
My setup:
mihai:~$ mysql -V
mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

mysql> describe WORKER;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int         | NO   | PRI | NULL    | auto_increment |
| FIRSTNAME  | varchar(15) | NO   |     | NULL    |                |
| LASTNAME   | varchar(20) | NO   |     | NULL    |                |
| CARDNUMBER | varchar(14) | NO   |     | NULL    |                |
| POSITIONID | int         | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM WORKER;
+----+-------------+-----------+----------------+------------+
| ID | FIRSTNAME   | LASTNAME  | CARDNUMBER     | POSITIONID |
+----+-------------+-----------+----------------+------------+
|  1 | Ionut Mihai | Popescu   | 045D91B22C5E80 |          1 |
|  2 | Ilie        | Zbagan    | 040D7FB22C5E81 |          2 |
|  3 | Ilie        | Ungureanu | 043FA8B22C5E80 |          1 |
|  4 | Adrian      | Tehanciuc | 04D894B22C5E80 |          2 |
|  5 | Ioan        | Bitoanca  | 0405C6B22C5E81 |          2 |
+----+-------------+-----------+----------------+------------+
5 rows in set (0.00 sec)

DROP TABLE IF EXISTS `WORKER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKER` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `FIRSTNAME` varchar(15) NOT NULL,
  `LASTNAME` varchar(20) NOT NULL,
  `CARDNUMBER` varchar(14) NOT NULL,
  `POSITIONID` int NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `POSITIONID` (`POSITIONID`),
  CONSTRAINT `WORKER_ibfk_1` FOREIGN KEY (`POSITIONID`) REFERENCES `POSITION` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The problem was my declared local variable id.
After I changed her name, everything worked just fine.
My previous answer was:
I don't know what the problem was, but I did found a solution.
I wrote:
    SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO @workerId;
    SELECT @workerId;

instead of
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;


Does activating delayed durability affect executing statements? I'm in the middle of a huge REORGANIZE. In order to speed it up I set DELAYED_DURABILITY = FORCED.

Will this affect the running statement? I watched IO and CPU numbers on the server and could not make out a difference. I'm also not sure if there would be any difference at all so I can't tell.

DELAYED_DURABILITY does not change the number of IO and CPU tick necessary to perform transactions in your server.  It can give a throughput boost to your workload, so you will expect to see the duration being reduced for the same transaction. Generally you expect to see an increase of Transactions/sec. This feature let transactions before being flushed to the transaction log file.

In flight transactions will use the old setting and new transactions will use the new 'DELAYED_DURABILITY' setting.  I did confirm using #sqlhelp hashtag, Denny Cherry was kind enough to answer.

Be mindful that:

  You may still have log I/O contention if there is a high degree of
  concurrency, particularly if you fill up the log buffer faster than
  you flush it.

From Paul Randal's blog:

  Delayed durable transaction commits are asynchronous and report a
  commit as successful before the log records for the transaction are
  written to disk. Writing the transaction log entries to disk is
  required for a transaction to be durable. Delayed durable transactions
  become durable when the transaction log entries are flushed to disk.

From Books online:

  Delayed durable transaction commits are asynchronous and report a
  commit as successful before the log records for the transaction are
  written to disk. Writing the transaction log entries to disk is
  required for a transaction to be durable. Delayed durable transactions
  become durable when the transaction log entries are flushed to disk.

I suggest you also be totally aware of the risk of turning on this feature especially for all transactions as you did.  You have the capability of turning this feature on per transaction.

Few resource :

Control Transaction Durability
Paul Randal's post
Delayed durability performance by Aaron Bertrand


How to update sys.login_token without restart SQL Server Service I query sys.login_token to check if the current user is in a particular windows group.

SELECT principal_id, name, [type], usage
FROM sys.login_token
WHERE principal_id > 0

But after I added the user to a new group, the group didn't show up in the query result.

I tried to log out and disconnect but it appears only restart the SQL Server Service will refresh the result of sys.login_token.

Therefore, is there any way(stored procedure or something) that I can trigger SQL Server to update sys.login_token?

The client refreshes the login token, not SQL Server.

Either log out/in, or lock+unlock their PC
This forces an AD authentication(token rebuild)

There also other methods...

Serverfault also suggest crashing explorer.exe on the client
I've not tried the klist approach. YMMV

Until the token is rebuilt, the login token as read by SQL Server will be stale.


How to Start Express SQL Server instance in Single user mode? I want to start my SQL Server Express instance in single user mode to do some troubleshooting such as restoring the Master database.

I am unable to find the "Startup Parameter" options in SQL Server Configuration Manager.

I have found some blogs such as http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/, however I am unable to add the -m parameter to the 'Startup Parameters'.

See below for screenshots of my SQL Server Configuration Manager:

My working environment is:

MS SQL Server 2012 Express 11.1.3000.0
Windows Server 2012 R2
SQL server 2012 Express is installed in a Domain
OS is running under VMware

Is there any possible way to connect through the console?  Any idea or suggestion would be appreciated.

I am able to see startup parameters from this T-SQL: 'select * from sys.dm_server_registry;', however I am unable to see the settings in SQL Server Configuration Manager.

According to the documentation, SQL Server Configuration Manager requires Local Administrator permissions since various SQL Server Configuration items are stored in the local system registry.

From the "SQL Server Properties (Startup Parameters Tab)" in the SQL Server Configuration Manager Help:

  Use of this page is restricted to users who can change the related entries in the registry. This includes the following users.

  Members of the local administrators group.
  The domain account that is used by SQL Server, if the Database Engine is configured to run under a domain account.

You can confirm membership in the local Administrators via one of the following methods:

Start a command-prompt on the machine where SQL Server is installed, and run this:

whoami /groups | find /i "BUILTIN\Administrators"

Assuming the machine is a member of your Active Directory Domain, you can run the "Active Directory Users and Computers" Microsoft Management Console, either from the Domain Controller for your domain, or from your workstation if you install the tool by downloading the Microsoft Remote Server Administration Tools.  Once you have "Active Directory Users and Computers" running, you need to find your user account, then check the "Member of" tab to see if you are a "Domain Administrator" or "Enterprise Administrator".
On the server where SQL Server is installed, you can check the "User Accounts" from "Control Panel".


How can I speed up an sql query that round trips between two tables These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$msc=microtime(true);

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY && sid != ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh->query("
       SELECT * FROM music
       WHERE perms != 'c' && sid IN($imploded_songs)
       ORDER BY FIELD(sid,$imploded_songs)
       limit 50
");

$msc=microtime(true)-$msc;
echo ($msc*1000).' milliseconds';

...
Time taken: 9.5310001373291 milliseconds

This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.

It's more or less like this...

SELECT m.*
     , COUNT(p.sid) frequency
  FROM music m
  JOIN plays p
    ON p.sid = m.sid
 WHERE p.time > NOW() - INTERVAL 3 DAY
   AND p.sid != ''
   AND m.perms != 'c'
 GROUP
    BY m.sid
 ORDER
    BY frequency DESC
 LIMIT 50


PostgreSQL - can I use logical replication from a replica's WAL? I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because RDS won't give you SUPERUSER role so you could connect and turn off the slots.

So I set up an RDS replica and set rds.logical_replication to 1. But it seems that RDS replica doesn't react to that - SHOW ALL says wal_level = hot_standby while it should be logical.

This is what I want to achieve:

RDS master
    WAL --> RDS replica
                logical replication ON
                WAL + wal2json  ---->    pg_recvlogical on remote client

Is this possible?

Reference: wal_level

When I try to create a slot, I get:

  FATAL:  no pg_hba.conf entry for replication connection from host "...", user "postgres", SSL off

This looks like AWS is not putting the right entries to pg_hba.conf - i.e. does not allow the postgres user to create the replication slots.

Is my assumption right? Is there any way around that?

You need to assign rds_superuser and rds_replication role to the user.

Refer here: https://aws.amazon.com/blogs/aws/amazon-rds-for-postgresql-new-minor-versions-logical-replication-dms-and-more/


Entity with >150 attributes: One table or many tables with relationships I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY operations. I could create one table for all entity:

CREATE TABLE rows {
   terminal_id long reference terminals(id),
   time timestamp,
   -- description 170 attributes
   PRIMARY KEY(terminal_id, time)
}

or I could create many tables with relationships:

CREATE TABLE rows {
   row_id long PRIMARY KEY,
   terminal_id long  reference terminals(id),
   time timestamp -- need create index for group by
}

CREATE TABLE gps {
   row_id long references rows(row_id),
   -- description gps attributes
}

CREATE TABLE fuel {
   row_id long references rows(row_id),
   -- description fuel attributes
}
-- etc.

Please advise optimal structure for the database of this type.

First off, apply normalization to the table and make sure that it really is one table with 170 attributes and not several tables jammed together.

Then decide whether your table will be sparsely populated and if so, decide whether storage space is a concern that warrants creating sub-tables to hold subsets of nullable columns.

If you do decide to create sub-tables, remember to factor in the extra index space when calculating whether a sub-table saves storage or not.


Is the genetic query optimiser enabled by default? I read with interest about postgres' genetic query optimiser. Is this enabled by default? Is there a setting to enable it? Are there good rules of thumb of when to use it versus when now?

Doc link follows:

https://www.postgresql.org/docs/11/geqo.html

The configuration for the genetic optimizer is documented in the chapter "Query Planning" of the manual. Specifically the section Genetic Query Optimizer

The first configuration parameter that is listed there is:

  geqo (boolean) Enables or disables genetic query optimization. This is on by default. It is usually best not to turn it off in production; the geqo_threshold variable provides more granular control of GEQO.

(emphasis mine)

So whenever you run a query that has more tables in the FROM clause than specified by geqo_threshold, GEQO will be used.

If you want to see the effects of it, you could lower that value and compare those plans against plans done without it.


Breaking a big insert query into smaller ones I have the below INSERT query.  It tries to insert over 50,000 rows and it fails because of logging.  Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT statements so that it is not just one big statement?

INSERT INTO xxx (....) SELECT .... FROM xx

DECLARE @rc INT = 1;
WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.target(cols)
    SELECT TOP (5000) cols
    FROM dbo.source AS s
    WHERE NOT EXISTS
    (
      SELECT 1 FROM dbo.target AS t
      WHERE t.key = s.key
    )
    ORDER BY clustering_key;

  SET @rc = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Also see:

Break large delete operations into chunks


Innodb buffer pool and multiple joins assume we have 1 Billion records and want to store them in One or Three tables by post_type.

post_id   -> primary key
post_type -> ( page, group, user )
user_id   -> index
date      -> index

Note: we have enough RAM to store all data in buffer pool. ( about 40 GB )
In our situation, Reading from database is too much

the question is: WHILE the whole data is ON buffer pool ( RAM ), is there any difference in performance to have 3 separated tables and Join them, or have 1 big table and Select from it without any Join.

In some cases it's better for us to have separated tables for other kind of light queries.
But we fear of JOINS.

InnoDB Architecture

Please keep in mind what goes into the InnoDB Buffer Pool

16KB Data Pages for Tables that have been accessed
16KB Index Pages for Indexes that have been accessed
Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment)

MySQL's idea of a JOIN
Believe it or not, whether you have one big table and process a WHERE clause or you have joins on multiples, the query processing has the same mechanical behavior. (I wrote about this a year ago : Is there an execution difference between a JOIN condition and a WHERE condition?) You can also find this described in the MySQL Documentation.
You said you fears JOINs. MySQL treats every query like a JOIN. The book I quoted in my post calls a SELECT on a single table a degenerate join. Multiple tables just uses a little extra resources (file handles, temp tables).
Effects on the InnoDB Buffer Pool
A single table with all needed fields would be loaded into the buffer pool and may produce, at most, 1 temp table for gathering data satisfying a where clause. On the flipside of things, having a single table would mean denormalized data and introducing redundant data into the buffer pool as well as the physical table storage.
Doing joins may actually result in creating multiple tables. This would also load data and associated indexes into the buffer pool. Any subsequent accessing of data might actually take place in the temp tables. Accessing the Buffer Pool would no longer be necessary.
Tuning
While a large buffer pool helps in the initial processing of a query, tuning the per-connection settings would be more of a help. The parameters I have in mind are

join_buffer_size
sort_buffer_size (if you are using ORDER BY and/or GROUP BY)

Any query that causes need more space than what there setting are may trigger a paging of that buffer to disk. There is where a slowdown of a query would become manifest and felt.
For the InnoDB Buffer Pool, your main objective should be to have the data and index pages present as often as possible. Avoid have a single denormalized table or you may bloat the buffer pool.
For the query, you may have to run smaller queries if you fear joins. Conversely, you could tune your queries and setup better indexes.
MySQL 5.6
If you are using MySQL 5.6, I would further suggest flushing the map of the InnoDB buffer pool to disk by running this
SET GLOBAL innodb_buffer_pool_dump_now = 1;

every minute.
Add this to my.cnf
[mysqld]
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1

and restart mysql.
Doing this will have all data and index pages availableon each restart of mysql. This gives every query you have the luxury of having data and indexes in RAM and up-to-date.


Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2.

When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to Integration Services it is successful. But, the package fails to run with an error:

The binary code for the script is not found.
Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

However, if we perform the exact same procedure from a remotely connected Windows 10 PC using the SSMS that is installed on the PC, the package will run properly.

Both the server and the Windows workstation are running SSMS 17.9.1. Comparing versions in Help -> About shows the exact same version numbers on everything. The same domain user is performing the procedure and that user is a domain admin and SQL Sysadmin.

This makes no sense. Is there another component being used I am unaware of? Any ideas?

Here is what I found in regards to this issue.

First, I exported the broken package from the server after importing it. I also exported the working package from the server after importing it. I compared the two files side by side and quickly discovered that the script within our package where the errors occurred was missing it's binary data in the BinaryItem element. I was also able to look directly at the package data on the server using the query:

SELECT id
    ,NAME
    ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
    ,description
FROM [msdb].[dbo].[sysssispackages]

The missing binary code indicated to me that the script was not being properly compiled during import.

After wasting a lot of time trying to find out why, I realized that the script was fairly simple so I should take a look in the script to see what was in there that might cause the import to fail.

I discovered a reference to Microsoft.Office.Interop.Excel which is part of the Microsoft Office Primary Interop Assemblies installed with Microsoft Office. On the SQL server, Microsoft Office is not installed. As a result, the .DLL file related to this reference is missing on the SQL server.

Removing the faulty, and unneeded reference solved the issue. I was able to confirm with Process Monitor that the server was looking for this .DLL file during import and not able to find it.

Apparently, if this happens during import there is no indication of the failure. Long story short, the error message was accurate. The binary code was missing. That was a result of a failure to compile the source code during import.


Defragmentation - Rebuilding Indexes SQL Server 2005 I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for:

ALTER INDEX ALL ON mytablename
REBUILD WITH(ONLINE = ON)

When I pull the info from sys.dm_db_index_physical_stats, I see the percents are very high - 70, 80 and 90 percent (!). So that tells me I want a REBUILD, and not a REORGANIZE.

I have a few question before executing these REBUILDS.

Can it be ran while processes are using the database? (ONLINE = ON
tells me yes, but I want to confirm it won't crash anything.) Or is
it better to run when it's not in use?
I read REBUILD makes things run slower. Is that just while the
indexes are being rebuilt? (or forever after)
How long will it take to rebuild all indexes / or rather each one?
Are there any side effects or other info that I need to be aware of? This is production/live database.

EDIT:
And finally, what is the best way to go about rebuilding it? Looping through all the objects with a percentage of greater than 30? Or?

Thank you!

Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible.
During the rebuild your queries will run slower, mostly due to the I/O overhead of rebuilding the index. How noticeable this is depends on the specifics of your system. The performance penalty is only during the rebuild - not forever after.
Again, the duration of the rebuild depends on your specific setup. You should try running this on an equivalent development or staging server.
No other associated side-effects. Once the rebuild is complete you'll be back up and running as usual.


How can I use a 1-argument function in an "ANY"/"ALL" query? Assuming I have the following function:

 CREATE OR REPLACE FUNCTION myfun(IN TEXT)
     RETURNS boolean
     LANGUAGE SQL
     IMMUTABLE
     LEAKPROOF
     AS $CODE$
         SELECT COALESCE(
             $1 ILIKE ANY (ARRAY[
                 'test',
                 'bar'
             ]),
             false
         );
     $CODE$;

Which returns true if $1 matches 'test' or 'bar'.

How can I use this function in another ANY query? For example:

 SELECT myfun ANY(ARRAY['hello', 'world']);  -- should return false
 SELECT myfun ANY(ARRAY['hello', 'foo']);  -- should return true ('foo' matches)
 SELECT myfun ANY(ARRAY['test', 'world']);  -- should return true ('test matches')

But this syntax is incorrect. And I don't quite know how to properly write this.

edit: more detail

In essence what I want to do is check if any one of a set of columns contains any one of forbidden terms. In other words, I have a set of forbidden terms, and have to check multiple columns for containment of that word. This will later be used in a RLS policy. In the example below, the function contains_forbidden_term is the function I would like to be able to write.

A small but important note: The same functions must be applied to other tables, but with varying number of arguments (hence the array as argument-type).

CREATE TABLE foo (
    name TEXT,
    last_name TEXT
);
CREATE USER bob;
ALTER TABLE foo ENABLE ROW LEVEL SECURITY;

CREATE POLICY bob_foo ON foo TO bob USING (contains_forbidden_term(ARRAY[name, last_name]));

GRANT SELECT ON foo TO bob;

You can use the first function with ANY but you have to have a valid syntax as you found out (<expression> <**operator**> ANY ...). So the following would work (the "trick" piece is to use unnest() whild unfolds the array into rows):

SELECT true = ANY (select myfun(x) from unnest(ARRAY['test', 'world']) as x) ;

or the more compact:

SELECT true = ANY (select myfun(unnest(ARRAY['tt', 'foo']))) ;


How to design database for polymorphic relationships I have the following scenerio:

There are some categories
Each category has attributes
An attribute could be of free text or collection of options
There is a product which is assigned to category which needs to store values/options of attributes

How can a model be made without any repetition, and which correctly addresses the polymorphic nature of attributes?

I would design a meta-data/value based model, as:

Or, where formality is enforced,

Being AttributeCode and ValueCode unique, textual, human-readable identifiers, alongside the corresponding AttributeId and ValueId.

For instance, the hypothetical meta-attribute length, physically stored with AttributeId = 1, would (should!) be referenced in specific queries as AttributeCode = 'LENGTH'.

(of course, AttributeCode and ValueCode could compose the PKs... but I personally prefer PKs columns to be integer-only)