Microsoft – SQL Server – Transact SQL – “Invalid Object”

Background

Microsoft SQL Server allows for pretty fast development. One is able to create
Programmable Objects Stored Procedures without first creating the referenced tables.

One is also able to add new columns and remove existing ones without much impedance.

Interesting Scenario

As various groups develop against the same database and the database changes with time, one
might run into small potholes.

In the rest of this post we will discuss possible side effects of renaming or removing columns.

 

 

Lab

Let us quickly setup a very small lab workshop.

 

Create Objects – Table & Programmable Objects

 

Create Object and programmable object.

 

use [tempdb]
go

/*
	drop table [dbo].[personIO]
*/
if object_id('dbo.personIO') is null
begin

	create table [dbo].[personIO]
	(

		  [firstname]   nvarchar(100) not null
		, [lastname]    nvarchar(100) not null

		, [middlename]  nvarchar(100) not null
			constraint constraintDBOPersonIDMiddleName 
				default ''

		, [dateOfBirth] datetime null

		, constraint PK_DBO_PERSONIO primary key 
		(
			  [lastname]
			, [firstname]
			, [middlename]
		)
	)


end
go

--drop view dbo.vw_PersonID
if object_id('dbo.vw_PersonIO') is null
begin

	exec('create view dbo.vw_PersonIO as select 1/0 as [null]')

end
go

alter view dbo.vw_PersonIO
as
	select 
		  lastname
		, firstname
		, middlename
                , dateofBirth
	from    dbo.personIO

go


if object_id('dbo.usp_PersonID_List') is null
begin

	exec('create procedure dbo.usp_PersonID_List as select 1/0 as [null]')

end
go

alter procedure dbo.usp_PersonID_List
as
begin
	select 
		  lastname
		, firstname
		, middlename
                , dateofBirth
	from    dbo.personIO
end
go

 

 

Add data

Let us add a few simple records

set nocount on
go

use tempdb
go

truncate table [dbo].[personIO]
go

insert into [dbo].[personIO]
([firstname], [lastname], middlename)
values
('Betsy', 'Johnson', 'T')
go


insert into [dbo].[personIO]
([firstname], [lastname])
values
('Mark', 'McGuire')
go

 

Query Data

Query data

use [tempdb]
go

select *
from   [dbo].[vw_personIO]
go

exec dbo.usp_PersonID_List
go

 

 Output:

dataFetched

 

Get Dependencies

List each object ddependencies

Nicely formatted sql code for listing each object’s dependency.

I will come back and properly credit the original source as it came from web through goggling.

SELECT  
        DB_NAME() AS dbname
        ,  o.type_desc AS referenced_object_type
        , d1.referenced_entity_name
        , d1.referenced_id, 
        STUFF( 
                (
                    SELECT ', ' + OBJECT_NAME(d2.referencing_id)
                    FROM sys.sql_expression_dependencies d2
                    WHERE d2.referenced_id = d1.referenced_id
                    ORDER BY OBJECT_NAME(d2.referencing_id)
                    FOR XML PATH('')
                ), 1, 1, ''
            ) AS dependent_objects_list
FROM sys.sql_expression_dependencies  d1 
        JOIN sys.objects o 
            ON  d1.referenced_id = o.[object_id]
GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
ORDER BY o.type_desc, d1.referenced_entity_name

 

Output:

listObjectDependencies

Disrupt

Let us disrupt things a bit.

Drop date of Birth column.

So Aunt Sallie finds out we are creating a table that is going to contain data for everyone in the family.

She is happy until she finds out it will contain each person’s date of birth, as well.

And, she says “she is having none of it“.

And, that she will call the authorities on us if we store everyone’s date of Birth.

So we have to drop the date of Birth column.

 

use [tempdb]
go

if object_id('dbo.personIO') is not null
begin

	if exists
	(
		select *
		from   sys.columns tblC
		where   tblC.object_id = object_id('dbo.personIO')
		and    tblC.name = 'dateOfBirth'
	)
	begin

		print 'dropping column dbo.personIO.dateOfBirth ...'

			alter table dbo.personIO
				drop column [dateofBirth]
		print 'dropping column dbo.personIO.dateOfBirth'

	end

end
go

 

Side Effects

Once we drop the date of Birth column, our applications start to break.

For now we will not worry about the front end application code, and just concentrate on the database stuff.

Programmable Database Objects

When we query against our view we get “could not use view of function … because of binding errors“.

We get an error as well when we attempt to execute our Stored Procedure.  The error goes like “Invalid column name”.

dataFetchedErrors

 

 

Other Relational Databases

In other databases, we can get a list of in-limbo objects by querying system meta-tables.

ORACLE

In Oracle, we can query *_objects ( user_objects, dba_objects, all_objects ) and filter for objects that have status equal to INVALID.

Please read more here:

 

IBM – DB2

In DB2, we can query SYSCAT.INVALIDOBJECTS.

You can also read more here:

 

 

Microsoft SQL Server

Let us see what is available in SQL Server.

 

Metadata – DMV – Catalog Views

SQL Server has a wealth of useful data in its catalog views.

A couple of contender views are:

 

But, none of them indicate whether the object is valid or not.

Workarounds

Refresh object

SQL Server allows one to revisit code definitions using a couple of statements.

sp_refreshsqlmodule targets “stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger“.

And, sp_refreshview targets views.

Please follow the links below to read up a bit more.

 

sys.dm_sql_referenced_entities

We are able to determine all the objects and columns that a specific object reference by querying the sys.dm_sql_referenced_entities view.

 

Let us do so against one of our programmable object – dbo.vw_PersonID:

use tempdb
go

SELECT 
         referenced_schema_name AS schema_name
        ,referenced_entity_name AS table_name
        ,referenced_minor_name AS referenced_column

FROM sys.dm_sql_referenced_entities ('dbo.vw_PersonIO', 'OBJECT');

GO

 

Output:

vw_personIOR_references

 

Once we drop the dateofBirth column, we run afoul a bit.

 

Output Textual:


Msg 207, Level 16, State 1, Procedure vw_PersonIO, Line 10
Invalid column name 'dateofBirth'.

Msg 2020, Level 16, State 1, Line 9
The dependencies reported for entity "dbo.vw_PersonIO" might not include references to all 
columns.

This is either because the entity references an object that does not exist or because of an errorin one or more statements in the entity.

Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.


 

Output Image:

Msg207AndMsg2020

Commendation

Microsoft’s Umachandar Jayachandran has a very worthy post for listing Column level dependencies.

SQL Server Engine Tips
Guidelines, Best Practices, TSQL and SQL Programming Tips & Tricks
MSDN Blogs > SQL Server Engine Tips > Direct dependencies on a column…
Direct dependencies on a column
http://blogs.msdn.com/b/sqltips/archive/2005/07/05/435882.aspx

Summary

Again, I got to go.

And, quite a bit far from being Trey Songz, I really got to go ( https://www.youtube.com/watch?v=QzPHWkksP7Y ), as I do have a day job.

But, it does seem that Microsoft’s SQL Server does not currently have database scoped tooling for identifying so called “Invalid Objects”.

I opened up a Connect Item – ID 1014712 – Add attribute to sys.sql_modules that flags no longer valid objects ( https://connect.microsoft.com/SQLServer/feedback/details/1014712/add-attribute-to-sys-sql-modules-that-flags-no-longer-valid-objects .

 

References

 

Object Catalog Views

 

 

Trouble-shooting & Identifying SQL Dependencies

 

Others Database – DB/2

 

3rd Part Tooling

Microsoft – SQL Server – Error Message – The conversion of a varchar data type to a datetime data type resulted in an out -of-range value.

Background

Needing to move data among systems using BCP, but it is failing.

 

Error Message


Msg 242, Level 16, State 3, Server DEV, Procedure usp_Pull_Wrong, Line 17
The conversion of a varchar data type to a datetime data type resulted in an out
-of-range value.


To correct

To correct, verify data  is really a date.

 

      case 
             when isDate([returnDate]) = 1 then [returnDate]

             else null

      end as [returnDate]

 

Nina Pham

 

 

Diwali

Happy Diwali to my Hindu family.

Here is Wikipedia’s write-up:

Diwali also known as Deepavali and the “festival of lights”, is an ancient Hindu festival celebrated in autumn every year. The festival spiritually signifies the victory of light over darkness, knowledge over ignorance, good over evil, and hope over despair.

Before Diwali night, people clean, renovate and decorate their homes and offices.

 

Nina Pham

I will like to request spirited and intercessory prayers for Nina Pham.  Both Nina and Amber Vinson both cared for Thomas Eric Duncan.

 

Ms. Pham’s struggles is family business as indicated by the following sentences:

  • At a traditional Monday night Mass at Our Lady of Fatima Church, her pastor, Jim Khoi, asked for prayers and support for Pham. The family is a long time presence at the church.
  • “Family friend Tom Ha said Nina Pham is a “hero” to the Vietnamese-American community and that her family is devoutly Catholic with a strong desire to serve humanity and a history of serving the poor and sick”.

 

Amber Vinson

Amber Vinson is Ebola free.

 

 

Dr. Kent Brantly

Dr Kent Brantly who successfully fought off Ebola donated his blood to Nina Pham. Dr. Brantly, volunteers with “Samaritan Purse”, contacted Ebola while serving in Liberia.

Dr. Brantly received blood donation from one of his young patients in Liberia.

Ashoka Mukpo, a freelance cameraman, was infected as he covered the Ebola outbreak in West Africa for NBC.

Mukpo said about Brantly’s “May his health flourish and his compassion be known to us all.”

 

Nancy Writebol ( Clinical Nurse )

 

Nancy Writebol contacted Ebola while volunteering with North Carolina based “Serving In Mission (SIM)“.   She miraculously survived. as well.

Here are some tracked conversations:

  • “(I thought) I don’t even know if I’m going to make it to the United States,” she said. “I don’t know if I will ever see my dear husband again.”
  • “It was a joy to be there,” she said. ” There was not a fear there. It was a wonderful place to be able to work and serve.”
  • Writebol said that she has been asked many times what she thinks saved her – the Zmapp, the medical care or her faith.

Theresa Romero

Teresa Romero, a Spanish nurse, contacted the disease while caring for a Spanish missionary.

Samaritan Purse

 

Samaritan’s Purse – Mission Statement
http://www.samaritanspurse.org/our-ministry/about-us/

Samaritan’s Purse is a nondenominational evangelical Christian organization providing spiritual and physical aid to hurting people around the world. Since 1970, Samaritan’s Purse has helped meet needs of people who are victims of war, poverty, natural disasters, disease, and famine with the purpose of sharing God’s love through His Son, Jesus Christ.

 

Please read more about Samaritan Purse here – http://www.samaritanspurse.org/.

 

 

World Response

Wall Street Journal has a good article that covers the participation of the various countries.

The article is titled “Cuban Doctors at the Forefront of Ebola Battle in Africa“.  And, is is available @ http://online.wsj.com/articles/cuba-stands-at-forefront-of-ebola-battle-in-africa-1412904212.

As the WSJ Journal is only available to subscribers, a copy is available @ https://docs.google.com/document/d/1nb9LPkSFMUVRYfAAv9vM_h7Rce_zUtYJxosXw9p1GYE/edit#

 

 

Ebola & Science

There are very well written scientific publications about Ebola.  ScienceMag.org has a wealth of very well written and engrossing material.

 

Spiritual Verses

 

Christian

  • Like a flitting sparrow, like a flying swallow, so a curse without cause shall not alight ( Proverbs 26:2 )
  • I, even I, am He who blots out your transgressions for My own sake; and I will not remember your sins. Put Me in remembrance; let us contend together; state your case, that you may be acquitted ( Isaiah 43:25-26 )
  • Then your light shall break forth like the morning, Your healing shall spring forth speedily, And your righteousness shall go before you; The glory of the LORD shall be your rear guard  ( Isaiah 58:8 ) ( NKJV )
  • The LORD said to me, “You have seen correctly, for I am watching to see that my word is fulfilled.” ( Jeremiah 1:12 )
  • But for you who revere my name, the sun of righteousness will rise with healing in its rays. And you will go out and frolic like well-fed calves. ( Malachi 4:2 )
  • As he went along, he saw a man blind from birth. His disciples asked him, “Rabbi, who sinned, this man or his parents, that he was born blind?” “Neither this man nor his parents sinned,” said Jesus, “but this happened so that the works of God might be displayed in him. (John 9: 1-3)
  • For this purpose the Son of God was manifested, that He might destroy the works of the devil. ( 1 John 3:8b )

Summary

Ebola is microscopic of Life,  And, though fraught with overwhelming issues.  Nonetheless,  it offers an engaging intersection of Faith, Science, Health and Public Service.

Nations who give give.  People who study and have dedicated their life to serving others will continue to do so. Intercessory prayers of righteous people continue to be effectual.

 

 

Addendum

Nina Pham

2014-10-24

Nina Pham declared Ebola free.

What will nurse do after beating Ebola? Hug her dog, of course
 http://www.cnn.com/2014/10/24/health/ebola-nurse/index.html?hpt=hp_c2

  • She invoked God and science in expressing gratitude for her ongoing recovery from a disease that has no established cure.
  • “I feel fortunate and blessed to be standing here today,” she said. “Throughout this ordeal, I have put my faith in God and my medical team.”
  • She thanked Dr. Kent Brantly, the American physician who also survived Ebola, for donating his plasma to her while she was sick.

 

References


Nina Phan


Dr. Kent Brantly

 


Nancy Writebol

 

Samaritan Purse

 

Serving in Ministry

 

Ebola in America

 

Ebola & Science

Microsoft – SQL Server – Transact – Compare Object Columns

 

 

Prelude

While trying to join two database objects (tables, views), I find myself getting errors with mismatched column types.

 

Error Messages

 

Image:

SquareMetersToDataTypeInt

 

Textual:

Msg 245, Level 16, State 1, Line 232
Conversion failed when converting the nvarchar value ‘square-meters’ to data type int.

 

Code

 

use master
go

if object_id('dbo.sp_CompareObjectColumns') is null
begin

    exec('create procedure dbo.sp_CompareObjectColumns as select 1/0 as [shell]' )

end
go

alter procedure dbo.sp_CompareObjectColumns 
(

      @objectName1         sysname
    , @objectName2         sysname
    , @compareType         sysname = 'columnType'
    , @listDifferencesOnly bit = 1

)
as
begin

    ;with cteObjectColumn1
    as
    (
        select   
                  TOP 100 PERCENT
                  schema_name(tblO.schema_id) as schemaName
                , tblO.name                   as objectName
                , tblC.column_id              as columnID
                , tblC.name                   as columnName
                , tblT.name                   as columnType  

        from   sys.objects tblO

                inner join sys.columns tblC

                    on tblO.object_id = tblC.object_id

                inner join sys.types tblT

                    on tblC.user_type_id = tblT.user_type_id

        where tblO.object_id = object_id(@objectName1)


        order by
                  schema_name(tblO.schema_id) 
                , tblO.name                   
                , tblC.column_id              

    )

    , cteObjectColumn2
    as
    (
        select 
                    TOP 100 PERCENT
                  schema_name(tblO.schema_id) as schemaName
                , tblO.name                   as objectName
                , tblC.column_id              as columnID
                , tblC.name                   as columnName
                , tblT.name                   as columnType  

        from   sys.objects tblO

                inner join sys.columns tblC

                    on tblO.object_id = tblC.object_id

                inner join sys.types tblT

                    on tblC.user_type_id = tblT.user_type_id

        where tblO.object_id = object_id(@objectName2)
        
         order by
                  schema_name(tblO.schema_id) 
                , tblO.name                   
                , tblC.column_id              

    )
    
        
    select 

              tblOC1.columnID
            , tblOC1.columnName
            , tblOC1.columnType

            , tblOC2.columnID
            , tblOC2.columnName
            , tblOC2.columnType

    from   cteObjectColumn1 tblOC1
   
                left outer join cteObjectColumn2 tblOC2

                    on

                        (

                                (@compareType = 'columnType')
                            and (tblOC1.columnID = tblOC2.columnID)

                        )

    where

            (

                   (@listDifferencesOnly = 0)

                or (

                            (@listDifferencesOnly = 1)
                        and (tblOC1.columnType != tblOC2.columnType)  

                   ) 


            )

end
go

exec sp_ms_marksystemobject 'dbo.sp_CompareObjectColumns'
go

 

40 Arabic Words

 

Prelude

Al-ḥamdu lillāh, I have love and respect for my Islam family and friends.

Blessings,”Wa Allahu yuHib al-kariim = And Allah loves those who are generous to others“.

Much honor to Prophet Mohammed, God bless his name, as this is not to blasphemy his name & work nor reach for apostasy in any way.

But, to share a thought providing video.

 

Video

40 Arabic Words ( by Ivey Conerly )
https://www.youtube.com/watch?v=TOJZRAjvW2U

The Evolution of Altruism by Oren Harman

 

The Evolution of Altruism

 

Notes

  • Where Kindness comes from
    • God creating
    • Role of reason
  • Inheritance vs construction
  • Scientific Problem
    • Darwinism
      • Every act is based on self interest
      • Natural Selection
  • Biology
    • Social Insects
      • Termites – No Reproduction
      • Honey bee
    • Amoeba
    • Gazelle
      • Stotting behavior – when confronted by lions drawing fire
    • Baboons
      • Cleaning each other
  • Psychology
    • Nepotism
      • Formalized by Bill Hamilton
      • Gene survival among relatives
      • Gene does nor care, as long as it propagates it self
      • And, so among brothers, sisters, and cousins it has the best chance
    • Reciprocation
      • Robert Trivers
    • Group Selection
      • William Mo
  • George Price
    • Self interest in disguise
    • Evangelical Christian
    • Human Spirit
      • Indeed, the spirit can transcend what nature can produce
    • Could not answer the question of Selflessness vs Self Interest
  • David Hume – Naturalist fallacy
    • Category Confusion
      • What is VERSUS What ought to be
    • Original Confusion
      • Because we understand the origin of something, does it mean we understand that something
  • Summary
    • Aristotle
      • The love we feel for others is part of the love we feel for ourselves
    • Ludwig Wittgenstein
      • Even when all the possible scientific questions have been answered, the problems of life remain completely untouched

 

References

 

Persons

George Price

Robert Trivers

Principles

Handicap Principle

 

Clay Shirky – Why I Just Asked My Students To Put Their Laptops Away

 

I really enjoyed this post by Clay Shirky.  Please see if you do, as well.

https://medium.com/@cshirky/why-i-just-asked-my-students-to-put-their-laptops-away-7f5f7c50f368

I teach theory and practice of social media at NYU, and am an advocate and activist for the free culture movement, so I’m a pretty unlikely candidate for internet censor, but I have just asked the students in my fall seminar to refrain from using laptops, tablets, and phones in class.

I came late and reluctantly to this decision — I have been teaching classes about the internet since 1998, and I’ve generally had a laissez-faire attitude towards technology use in the classroom. This was partly because the subject of my classes made technology use feel organic, and when device use went well, it was great. Then there was the competitive aspect — it’s my job to be more interesting than the possible distractions, so a ban felt like cheating. And finally, there’s not wanting to infantilize my students, who are adults, even if young ones — time management is their job, not mine.

Despite these rationales, the practical effects of my decision to allow technology use in class grew worse over time. The level of distraction in my classes seemed to grow, even though it was the same professor and largely the same set of topics, taught to a group of students selected using roughly the same criteria every year. The change seemed to correlate more with the rising ubiquity and utility of the devices themselves, rather than any change in me, the students, or the rest of the classroom encounter.

Over the years, I’ve noticed that when I do have a specific reason to ask everyone to set aside their devices (‘Lids down’, in the parlance of my department), it’s as if someone has let fresh air into the room. The conversation brightens, and more recently, there is a sense of relief from many of the students. Multi-tasking is cognitively exhausting — when we do it by choice, being asked to stop can come as a welcome change.

So this year, I moved from recommending setting aside laptops and phones to requiring it, adding this to the class rules: “Stay focused. (No devices in class, unless the assignment requires it.)” Here’s why I finally switched from ‘allowed unless by request’ to ‘banned unless required’.


We’ve known for some time that multi-tasking is bad for the quality of cognitive work, and is especially punishing of the kind of cognitive work we ask of college students.

This effect takes place over more than one time frame — even when multi-tasking doesn’t significantly degrade immediate performance, it can havenegative long-term effects on “declarative memory”, the kind of focused recall that lets people characterize and use what they learned from earlier studying. (Multi-tasking thus makes the famous “learned it the day before the test, forgot it the day after” effect even more pernicious.)

People often start multi-tasking because they believe it will help them get more done. Those gains never materialize; instead, efficiency is degraded. However, it provides emotional gratification as a side-effect. (Multi-tasking moves the pleasure of procrastination inside the period of work.) This side-effect is enough to keep people committed to multi-tasking despite worsening the very thing they set out to improve.

On top of this, multi-tasking doesn’t even exercise task-switching as a skill.A study from Stanford reports that heavy multi-taskers are worse at choosing which task to focus on. (“They are suckers for irrelevancy”, as Cliff Nass, one of the researchers put it.) Multi-taskers often think they are like gym rats, bulking up their ability to juggle tasks, when in fact they are like alcoholics, degrading their abilities through over-consumption.


This is all just the research on multi-tasking as a stable mental phenomenon. Laptops, tablets and phones — the devices on which the struggle between focus and distraction is played out daily — are making the problem progressively worse. Any designer of software as a service has an incentive to be as ingratiating as they can be, in order to compete with other such services. “Look what a good job I’m doing! Look how much value I’m delivering!”

This problem is especially acute with social media, because on top of the general incentive for any service to be verbose about its value, social information is immediately and emotionally engaging. Both the form and the content of a Facebook update are almost irresistibly distracting, especially compared with the hard slog of coursework. (“Your former lover tagged a photo you are in” vs. “The Crimean War was the first conflict significantly affected by use of the telegraph.” Spot the difference?)

Worse, the designers of operating systems have every incentive to be arms dealers to the social media firms. Beeps and pings and pop-ups and icons, contemporary interfaces provide an extraordinary array of attention-getting devices, emphasis on “getting.” Humans are incapable of ignoring surprising new information in our visual field, an effect that is strongest when the visual cue is slightly above and beside the area we’re focusing on. (Does that sound like the upper-right corner of a screen near you?)

The form and content of a Facebook update may be almost irresistible, but when combined with a visual alert in your immediate peripheral vision, it is—really, actually, biologically—impossible to resist. Our visual and emotional systems are faster and more powerful than our intellect; we are given to automatic responses when either system receives stimulus, much less both. Asking a student to stay focused while she has alerts on is like asking a chess player to concentrate while rapping their knuckles with a ruler at unpredictable intervals.


Jonathan Haidt’s metaphor of the elephant and the rider is useful here. In Haidt’s telling, the mind is like an elephant (the emotions) with a rider (the intellect) on top. The rider can see and plan ahead, but the elephant is far more powerful. Sometimes the rider and the elephant work together (the ideal in classroom settings), but if they conflict, the elephant usually wins.

After reading Haidt, I’ve stopped thinking of students as people who simply make choices about whether to pay attention, and started thinking of them as people trying to pay attention but having to compete with various influences, the largest of which is their own propensity towards involuntary and emotional reaction. (This is even harder for young people, the elephant so strong, the rider still a novice.)

Regarding teaching as a shared struggle changes the nature of the classroom. It’s not me demanding that they focus — its me and them working together to help defend their precious focus against outside distractions. I have a classroom full of riders and elephants, but I’m trying to teach the riders.

And while I do, who is whispering to the elephants? Facebook, Wechat, Twitter, Instagram, Weibo, Snapchat, Tumblr, Pinterest, the list goes on, abetted by the designers of the Mac, iOS, Windows, and Android. In the classroom, it’s me against a brilliant and well-funded army (including, sharper than a serpent’s tooth, many of my former students.) These designers and engineers have every incentive to capture as much of my students’ attention as they possibly can, without regard for any commitment those students may have made to me or to themselves about keeping on task.

It doesn’t have to be this way, of course. Even a passing familiarity with the literature on programming, a famously arduous cognitive task, will acquaint you with stories of people falling into code-flow so deep they lose track of time, forgetting to eat or sleep. Computers are not inherent sources of distraction — they can in fact be powerful engines of focus — but latter-day versions have been designed to be, because attention is the substance which makes the whole consumer internet go.

The fact that hardware and software is being professionally designed to distract was the first thing that made me willing to require rather than merely suggest that students not use devices in class. There are some counter-moves in the industry right now — software that takes over your screen to hide distractions, software that prevents you from logging into certain sites or using the internet at all, phones with Do Not Disturb options — but at the moment these are rear-guard actions. The industry has committed itself to an arms race for my students’ attention, and if it’s me against Facebook and Apple, I lose.


The final realization — the one that firmly tipped me over into the “No devices in class” camp — was this: screens generate distraction in a manner akin to second-hand smoke. A paper with the blunt title Laptop Multitasking Hinders Classroom Learning for Both Users and Nearby Peerssays it all:

We found that participants who multitasked on a laptop during a lecture scored lower on a test compared to those who did not multitask, and participants who were in direct view of a multitasking peer scored lower on a test compared to those who were not. The results demonstrate that multitasking on a laptop poses a significant distraction to both users and fellow students and can be detrimental to comprehension of lecture content.

I have known, for years, that the basic research on multi-tasking was adding up, and that for anyone trying to do hard thinking (our spécialité de la maison, here at college), device use in class tends to be a net negative. Even with that consensus, however, it was still possible to imagine that the best way to handle the question was to tell the students about the research, and let them make up their own minds.

The “Nearby Peers” effect, though, shreds that rationale. There is no laissez-faire attitude to take when the degradation of focus is social. Allowing laptop use in class is like allowing boombox use in class — it lets each person choose whether to degrade the experience of those around them.

Groups also have a rider-and-elephant problem, best described by Wilfred Bion in an oddly written but influential book, Experiences in Groups. In it, Bion, who practiced group therapy, observed how his patients would unconsciously coordinate their actions to defeat the purpose of therapy. In discussing the ramifications of this, Bion observed that effective groups often develop elaborate structures, designed to keep their sophisticated goals from being derailed by more primal group activities like gossiping about members and vilifying non-members.

The structure of a classroom, and especially a seminar room, exhibits the same tension. All present have an incentive for the class to be as engaging as possible; even though engagement often means waiting to speak while listening to other people wrestle with half-formed thoughts, that’s the process by which people get good at managing the clash of ideas. Against that long-term value, however, each member has an incentive to opt out, even if only momentarily. The smallest loss of focus can snowball, the impulse to check WeChat quickly and then put the phone away leading to just one message that needs a reply right now, and then, wait, whathappened last night??? (To the people who say “Students have always passed notes in class”, I reply that old-model notes didn’t contain video and couldn’t arrive from anywhere in the world at 10 megabits a second.)


I have the good fortune to teach in cities richly provisioned with opportunities for distraction. Were I a 19-year-old planning an ideal day in Shanghai, I would not put “Listen to an old guy talk for an hour” at the top of my list. (Vanity prevents me from guessing where it would go.) And yet I can teach the students things they are interested in knowing, and despite all the literature on joyful learning, from Marie Montessori on down, some parts of making your brain do new things are just hard.

Indeed, college contains daily exercises in delayed gratification. “Discuss early modern European print culture” will never beat “Sing karaoke with friends” in a straight fight, but in the long run, having a passable Rhianna impression will be a less useful than understanding how media revolutions unfold.

Anyone distracted in class doesn’t just lose out on the content of the discussion, they create a sense of permission that opting out is OK, and, worse, a haze of second-hand distraction for their peers. In an environment like this, students need support for the better angels of their nature (or at least the more intellectual angels), and they need defenses against the powerful short-term incentives to put off complex, frustrating tasks. That support and those defenses don’t just happen, and they are not limited to the individual’s choices. They are provided by social structure, and that structure is disproportionately provided by the professor, especially during the first weeks of class.

This is, for me, the biggest change — not a switch in rules, but a switch in how I see my role. Professors are at least as bad at estimating how interesting we are as the students are at estimating their ability to focus. Against oppositional models of teaching and learning, both negative—Concentrate, or lose out!—and positive—Let me attract your attention!—I’m coming to see student focus as a collaborative process. It’s me and them working to create a classroom where the students who want to focus have the best shot at it, in a world increasingly hostile to that goal.

Some of the students will still opt out, of course, which remains their prerogative and rightly so, but if I want to help the ones who do want to pay attention, I’ve decided it’s time to admit that I’ve brought whiteboard markers to a gun fight, and act accordingly.